RE: The Case Against Compound/Natural Keys
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <don@xxxxxxxxx>, "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- Date: Mon, 29 Jan 2007 10:34:46 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@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
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
