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:

we can efficiently join across columns (assuming a NUMBER(16) in Oracle or an INT in Sybase), we're looking at four bytes, and
our implementation is independent of the business requirements without losing the business requirments
our index density is very high so index range scans/'covering an index' is extremely efficient

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