Re: Normalization, Natural Keys, Surrogate Keys
Date: 21 May 2002 14:07:34 -0700
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.
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 --- http://www.magicinterface.com email: ed.prochak_at_magicinterface.comReceived on Tue May 21 2002 - 23:07:34 CEST