Re: Normalization, Natural Keys, Surrogate Keys

In message <MPG.1753053482c33da6989710_at_news.easynews.com>, Paul Tiseo <123tiseo.paul_at_123mayo.edu> writes
>In article <3CE583F3.7F5D20D5_at_exesolutions.com>,
>dmorgan_at_exesolutions.com says...
>> If I didn't get paid so darned much money dumping surrogate keys and cleaning
>> up the messes I'd actually be upset. And not once has there been a resulting
>> performance problem that warranted recreating a surrogate key and instituting
>> a fake primary key using a unique index with a not null constraint.
>
> The SSN is a surrogate key itself, and a BAD one at that. There
>are duplicates out there. It is "just as good" as a surrogate. In fact,
>it might be worse, because at least I can QA my surrogate, but I can't
>QA the government's surrogate for Person.

The value of someone else's surrogate key depends on how much they can be trusted. If you trust them completely then you can treat their keys as if they were natural keys.

>
> Secondly, as someone else explained, the error you illustrate of
>duplication is NOT due to the use of a surrogate key, but of bad usage
>of integrity operations (checks, triggers or stored procedures) that
>would have prevented it.

The error has multiple causes. Adopting a surrogate key is a risk factor although it doesn't necessarily cause the error. If a natural key had been used then the error would not have occurred. Of course this is easier to say than to do, there are no easily available natural keys to identify people. That's why the SSN had to be invented,

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti