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@xxxxxxxxx> 
To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, January 27, 2007 5:48 AM
Subject: The Case Against Compound/Natural Keys



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

Other related posts: