Re: Normalization, Natural Keys, Surrogate Keys

From: Ed prochak <>
Date: 21 May 2002 14:07:34 -0700
Message-ID: <>

Galen Boyer <> wrote in message news:<>...
> On Fri, 17 May 2002, (drop the numbers) wrote:
> > This said, the only thing I see wrong with surrogate keys is
> > that they can cause you extra work. You must, in some cases, think
> > about the "properness" of both the surrogate and the natural,
> > alternate key.
> True. But "thinking" about the surrogate is brain-dead. Its the
> natural key that must be thought of and is always the hardest.
> > You now have two columns instead of one, although the impact these
> > days on modern DBMSes should be negligeable.
> Performance should be increased on joins with tables each having one
> surrogate key over tables all having natural keys and most of these
> having multiple values.

I'm not sure what you really meant here but: 1.If they are Keys (natural or surrogate), they are not duplicated. 2.Personally I'd be real disappointed if there was a significant difference in a query using a surrogate key versus a natural key. Both should result in an index scan followed by fetch by row id and should be fast either way.

(now there is a factor of the size of the keys, so a table with MANY rows will be slower for the natural key because fewer of them fit in a block, so more blocks must be read to find it. but if this amounted to something even near a factor of two, I'd be supprised.)

> > Furthermore, it requires additional rules on the physical
> > implementation which could hamper performance
> There will be one extra index to maintain on every table.

I agree this isn't a lot of overhead.


Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       ---
Received on Tue May 21 2002 - 23:07:34 CEST

Original text of this message