Re: Normalization, Natural Keys, Surrogate Keys

In message <e51b160.0205210944.24596ca_at_posting.google.com>, Ed prochak <ed.prochak_at_alltel.com> writes

>> now have two columns instead of one, although the impact these days on
>> modern DBMSes should be negligeable. Furthermore, it requires additional
>> rules on the physical implementation which could hamper performance or
>> which might not be doable depending on the engine used. (PK on the
>> surrogate and unique index on the complex natural key rather than just a
>> single PK on a single complex natural.)
>
>It's not just extra work. When ALL (or nearly all) relationships in a
>system are identified by surrogate keys (read sequence numbers) you
>get brain dead programmers using the sequence number from the
>customer's address as the key to finding his current products
>purchased. (sorry, didn't mean to rant.)

I've seen situations where that would be the only sensible way to handle it. Customer names are free-text fields and as such are suspect. It's very easy to have accounts names like:

Marconi Radar
Marconi Radar Systems Ltd
Marconi Radar
MRSL
Marconi, Radar Systems
GEC-Marconi Radar
Macroni Radar

All of them (except the last) are equally valid entries. Purchase-order fields are also free-text and are suspect for the same reasons. I don't trust data typed in by the lowest paid employees in the company.

>There is no easy way to clean up some of this data, as I'm sure Daniel
>knows. Since the surrogates are the ONLY thing common for many tables,
>there is nothing to compare. Surrogates make a lot of sense in some
>cases (e.g. Sales Order numbers) where the natural key may be too
>complex or variable. And I'm not exactly a database designer. But it
>seems some systems have taken the goal of reducing redundant data to
>the extreme. And as I mentioned earlier, the system now looks like a
>network model database, with all the problems model implies.

There are people who automatically add a surrogate to every table they create.

>> 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.
>
>(Did I go ballistic??)
>surrogate keys are okay in the proper place.
> I just think they are overdone in many cases.

Only too true.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti