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_at_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 Fri May 17 2002 - 00:35:21 CEST