Re: Surrogate Keys: an Implementation Issue

"-CELKO-" <jcelko212_at_earthlink.net> wrote in message news:1153576575.260428.27560_at_b28g2000cwb.googlegroups.com...
>
> First of all, a surrogate key is not exposed to the user. That is
> straight out of Dr. Codd. The system maintains it, like an index and
> it does not replace the relational key.
>

I agree with the above, as far as it goes.

But it's simplistic. The word "system" implies some sort of information system, and that in turn suggests a system that is under control of the database designer and the application programmers, as opposed to the "end users".

The problem with this formulation is that it excludes "systems" that include people. The Human Resources department of a corporation is a "system", whether we like to think of it as a system or not.

And HR can, and does, come up with an artificial key to identify empployees, rather than rely on SSN as a "natural key" identifying persons, and therefore employees.

The defects with SSN have been beaten to death in this newsgroup several times before, and probably will be again. But all those defects can be summarized thus: The SSN is beyond the control of HR.

Now, if HR invents an attribute, called "EMPLOYEE_ID", assigns it, and maintains it, there's no way the application or the database should shiled it from "the user". At least not if "the user" is part of HR.

Now what's the difference between a purely artificial key like EMPLOYEE_ID that's generated, assigned and maintained by HR, and a "surrogate key" that's generated, assigned, and maintained by some automated process? The difference is largely in the eye of the beholder.

The point I'm trying to make is that the boundary between "inside the system" and "outside the system" is dependent on the position of the observer. Received on Sat Jul 22 2006 - 20:30:38 CEST