Re: Normalization, Natural Keys, Surrogate Keys

Galen Boyer <galendboyer_at_yahoo.com> wrote in message news:<un0uy7xqi.fsf_at_rcn.com>...
> On Fri, 17 May 2002, 123tiseo.paul_at_123mayo.edu (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.

Galen,
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.

Ed

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com