Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Normalization, Natural Keys, Surrogate Keys

Re: Normalization, Natural Keys, Surrogate Keys

From: Pablo Sanchez <pablo_at_dev.null>
Date: Thu, 16 May 2002 16:35:21 -0600
Message-ID: <3ce43257_19@news.teranews.com>

"Tobin Harris" <comedyharris_at_hotmail.com> wrote in message news:ac1apu$locri$1_at_ID-135366.news.dfncis.de... >
> I think the point is to think about your surrogate keys carfully,
not just
> assign them as habit (which many do). I believe that if you can find
an
> existing natural key, then use it!

I think the issue with implementing natural keys as the physical primary key is that unfortunately, the business changes. What a business analyst swears will never happen, does. Additionally, if we don't use surrogate keys, then dependent rows have a pretty nasty looking foreign key:

parent



col_1 VARCHAR(40)
col_2 VARCHAR(60)

child



my_own_column
col_1 <--\
col_2 <---+-- coming from the parent - the foreign key.

Whereas when we use surrogate keys we end up with something like the following (notice my naming convention -- which I snarfed from someone else ... can't recall who ... :)

parent



id
col_1
col_2

child



id
my_own_column
parent_id (FK)

The benefits of the surrogate key are:

What's key (pun intended! <go>) is that surrogate keys and natural keys can peacefully co-exist. Kumbaya, kumbaya ...

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts
Received on Thu May 16 2002 - 17:35:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US