Re: The Case Against Compound/Natural Keys

Don,

> These tables are bulk-loaded and .

I assume your database is a kind of DW system.

> They've all heard me calling for
> surrogate keys, but they say they need uniqueness among this set of
> fields. Then when they discover duplicates, they just add another
> field.

I thing you address two different problems here: a) how to enforce the uniqueness of a fact table b) how to define the primary key (natural / surrogate) on the dimension table

Uniqueness of a fact table can be enforced using index, alternatively you may define a cleaning step in the loading process (eliminating the dups before the load) and not to rely on an index. A similar pre-processing step can enforce the consistency of the FK relation to the parent table.

For a dimensional table (your "parent table") there are two options in my opinion
a) use natural key as a primary key of the dimension and a foreign key of the fact table - it is your implementation b) use surrogate key for PK of the dimension and FK of the fact table and additionally denormalize the dimension natural key into the fact table. There is a nice example on Jonathan Lewis blog demonstrating the consequences of using "pure" surrogates. When to use surrogate keys? It depends on the "nature" of the natural keys. A little example: I wouldn't for sure set up a DW with natural key (only) for Oracle product names. Querying webDB, htmlDB, RAC,. over years of history would be a nightmare.
A real value added surrogate key processing must implement some logic deciding when to assign a new key (for a new dimension instance) or to reuse existing one (for a new version of changed dimension instance).

HTH Jaromir D.B. Nemec
----- Original Message -----
From: "Don Seiler" <don_at_seiler.us>
To: "oracle-l" <oracle-l_at_freelists.org>
Sent: Saturday, January 27, 2007 5:48 AM Subject: The Case Against Compound/Natural Keys

--
http://www.freelists.org/webpage/oracle-l