Reply-To: "Pablo Sanchez" <pablo@dev.null>
From: "Pablo Sanchez" <pablo@dev.null>
Newsgroups: comp.databases.oracle.misc,comp.databases.theory,comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming
References: <c1ec9b8f.0205151525.6dce24d2@posting.google.com> <eiWjfwR$BHA.2384@tkmsftngp02> <ac1apu$locri$1@ID-135366.news.dfncis.de>
Subject: Re: Normalization, Natural Keys, Surrogate Keys
Date: Thu, 16 May 2002 16:35:21 -0600
Lines: 62
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <3ce43257_19@news.teranews.com>
X-Abuse-Report: Send abuse reports to abuse@teranews.com
Organization: http://www.TeraNews.com - FREE NNTP Access
Path: news.easynews.com!easynews!newsfeed-east.nntpserver.com!nntpserver.com!reseller.nntpserver.com
Xref: easynews comp.databases.oracle.misc:81723 comp.databases.theory:20810 comp.databases.ms-sqlserver:72050 microsoft.public.sqlserver.programming:238342
X-Received-Date: Thu, 16 May 2002 15:33:20 MST (news.easynews.com)


"Tobin Harris" <comedyharris@hotmail.com> wrote in message
news:ac1apu$locri$1@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@hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts