Re: Normalization, Natural Keys, Surrogate Keys
Date: 21 May 2002 14:07:34 -0700
Message-ID: <e51b160.0205211307.2617e84a_at_posting.google.com>
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.
>
> > 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.comReceived on Tue May 21 2002 - 23:07:34 CEST