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_at_shrdlu.com