RE: The Case Against Compound/Natural Keys

I guess I'm a little late to this party.

FYI, Steve Adams has a nice write up on his website of synthetic vs. natural keys:
http://www.ixora.com.au/tips/design/synthetic_keys.htm

-Mark

--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005


-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Don Seiler
Sent: Monday, January 29, 2007 10:23 AM
To: jaromir nemec
Cc: oracle-l
Subject: 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_at_db-nemec.com> 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_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


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