Re: Normalization, Natural Keys, Surrogate Keys

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.

> Furthermore, it requires additional rules on the physical
> implementation which could hamper performance

There will be one extra index to maintain on every table.

> or which might not be doable depending on the engine used.

Hm... Haven't thought of this one. Hm... What engines would have issues?

> (PK on the surrogate and unique index on the complex natural key
> rather than just a single PK on a single complex natural.)
>
> Plus, I don't understand how someone can go ballistic over
> creating surrogate keys and then turn around and use an SSN or an
> EmployeeID as a PK, for example. These are basically someone else's
> surrogate key, yet are commonly used as PKs.

Bingo!

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.