Re: The Case Against Compound/Natural Keys

  • From: Nuno Souto <dbvision@xxxxxxxxxxxx>
  • Date: Tue, 30 Jan 2007 22:16:57 +1100

Don Seiler wrote,on my timestamp of 30/01/2007 2:23 AM:

My database is actually more of an all-purpose hybrid.

Just like the majority of dbs out there.  Like it or not,
OLTP and DW are two extremes.  The middle of the road and
most common db type is by far the DSS.  Which has bits of
both plus its own distinctive set of a zillion tables.
Then again, I've recently heard someone ask "DSS, what's
that?"  (must be the "powerpoint OCP" factor again...)

To be precise, I don't hate natural keys for the sake of hating
natural keys.  It's the composite keys that I hate, and especially
when there are no queries that such a large index would address.

and when you have those natural composite keys present on
a large number of tables as FKs.  And indexed, of course,
because otherwise joins would be painfully slow.

This is of course the detail that is omitted from the
"natural key" camp arguments: it's not just the index
on the composite natural PK, it's also the one that will be
needed wherever that composite, very long natural key will
be used as a FK!

As opposed to a single column index on the synthetic key
wherever it is used as a FK, plus a unique index on the
composite key in the prime parent table.

I know which overhead I'd rather take.

Yes, I'm painfully aware of the index hot spot problem
with growing synthetic key values: been aware of that for
many years, even before database engines had an "r" before
the "dbms"!  It's not a new problem and it has been overcome
in other dbms engines: there is no reason it can't in Oracle
other than inactivity on the part of their R&D.

There is nothing in indexing b-tree theory that says the top
level index node has to be stored in one single block, for
example. In fact, there is nothing in b-tree indexing theory
that requires the concept of "blocks": that's a physical storage
requirement, not a logical one necessary for the operation
of b-tree indexes.

But even with that problem present, I'll gladly take the
trade-off and use synthetic keys anywhere and everywhere
rather than having to incur the horrible maintenance and
space overhead that is natural composite keys used as PKs
*AND* as FKs.

Nuno Souto

Other related posts: