Re: The Case Against Compound/Natural Keys

My database is actually more of an all-purpose hybrid.  We have OLTP
data where data is entered by sales and updated by customer service,
etc.  We then bulk-load call records and processed billing information
(we are a telecom) that the customer service app uses when customers
call about their bills or question a call.  So we don't have fact vs
dimension tables as you might find in an ideal DW instance.

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.

These tables are already partitioned with local indexes.  We are
running the "rolling window" scenario, keeping the most recent 4
months.

Jack: are you suggesting that I put a foreign key constraint/index on
the leading X number of fields already in my primary key
constraint/index?  Because that is what it would be, and is yet
another exhibit of my frustration with this design (or lack thereof).

Don.

On 1/28/07, jaromir nemec <jaromir@xxxxxxxxxxxx> wrote:

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: