Re: Surrogate Keys: an Implementation Issue

In comp.databases.theory Paul Mansour wrote:


The second is perhaps more profound. Consider a rollback database, or
a database that must provide a complete audit trail of every change.
For example, the database must provide the answer to "who changed
this SS number from X to Y, and when did they change it? As far as I
can tell, if there is no way to answer this without an immutable
identifier. ( I suppose you could design the DB to handle specific
cases, but I'm interested in DBMS with native rollback and audit
trail support.)

If what you think of as the key is mutable it's not a natural key.
Natural keys aren't mutable, at all, ever.

The problem here is that there isn't a usable natural key for
identifying people. So all that's left are surrogates of varying
quality. The SSN is a surrogate that works most of the time. If you
assign someone a payroll number and tell tham that thay won't get paid
unless they can quote it then you have a close to immutable key. But
it's still a surrogate.

I think the original post was part correct. Use a natural key when you
have one. I'd just add to that, don't use a surrogate unless there is a
compelling reason for it. Efficiency is unlikely to be a compelling
argument in most cases.



--
--
Bernard Peek
bap@xxxxxxxxxx
.

Relevant Pages