Re: Surrogate Keys: an Implementation Issue
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 22 Jul 2006 22:02:47 GMT
Message-ID: <bgxwg.13694$pu3.316523_at_ursa-nb00s0.nbnet.nb.ca>
>
> 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.
Date: Sat, 22 Jul 2006 22:02:47 GMT
Message-ID: <bgxwg.13694$pu3.316523_at_ursa-nb00s0.nbnet.nb.ca>
Bernard Peek wrote:
> 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.
What is a natural key if not a familiar surrogate key? Received on Sun Jul 23 2006 - 00:02:47 CEST