Re: Normalization, Natural Keys, Surrogate Keys

Paul Tiseo wrote:

> 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.
>
> 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.
>
> (Any opinions expressed are strictly mine only and not my employer's)
> --------------------------------------------------------------------
> Paul Tiseo, Intermediate Systems Programmer
> Reply To: 123tiseo.paul_at_123mayo.edu (drop the numbers)

Just to dwell on a point ... the origin of a value, for example a Social Security Number is not important. What is important is the way that the value is used within your system.

I keep hearing that Social Security Numbers are not unique but have yet to come upon a situation where that was true. But assuming it is and you are creating a system for the XYZ company (lets say a health insurance provider or employer). If you are going to set up your policies using Social Security Number as the method of identifying a unique individual ... you had better know right up front that you have two people with the same number. If you do not, if some system, somehow, assigns different surrogate keys, you are going to have a huge mess with legal implications somewhere down the line.

Personally; I'd rather have the system catch that one up front and with a blaring of trumpets and beating of drums.

Daniel Morgan Received on Mon May 20 2002 - 21:47:21 CEST