Re: Normalization, Natural Keys, Surrogate Keys

From: Bernard Peek <>
Date: Thu, 23 May 2002 21:02:26 +0100
Message-ID: <>

In message <8iaH8.2885$>, Richard Kuhler <> writes
>Bernard Peek wrote:
>> >
>> >In reflection I tend to agree. By using an artificial surrogate key -
>> >aside from making the database easier to read - we avoid the problem
>> >of potenially changing data.
>> I think that's incorrect. By using an artificial surrogate key you avoid
>> detecting changed data, which is subtly different. If there is a genuine
>> natural key then it is impossible for it to change. The key uniquely
>> identifies one and only one instance of an entity. If the key changes it
>> can only be because the instance it refers to has changed, and in that
>> situation you want to detect the change.
>By this standard it seems that the only natural key for a human being is
>their entire DNA sequence.

No, it's not a natural key because there's no mechanism that enforces the relationship between the genetic code and the individual. It's possible that two individuals could have the same code. It's also quite normal for different cells in an individual to have different genetic codes.

> Oh wait, clones are just around the corner,
>nevermind. Seriously though, I'm not following your meaning here. Can
>you provide an example of something and it's never changing natural key?

A natural key for a human is the precise location and time of their birth. The spatial position and time are unique because it is not possible for two objects to occupy the same space at the same time. Uniqueness enforced by the laws of physics.

I freely admit that my definition of a natural key may be excessively literal. That's possibly because I see a continuum between the keys which can't ever have duplicate values and keys which shouldn't have duplicates but sometime have due to mistakes. If I believe the probability of such a mistake is low enough I'll treat a key as if it were a natural key.

Bernard Peek

In search of cognoscenti
Received on Thu May 23 2002 - 22:02:26 CEST

Original text of this message