Re: The Case Against Compound/Natural Keys
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <don@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 28 Jan 2007 23:47:29 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
