Re: Normalization, Natural Keys, Surrogate Keys

In message <c1ec9b8f.0205210930.211d68c9_at_posting.google.com>, Kai Ponte <cybermusicdude_at_aol.com> writes

>Galen Boyer <galendboyer_at_yahoo.com> wrote in message
>news:<usn4mxfa2.fsf_at_rcn.com>...
>> On Mon, 20 May 2002, bap_at_shrdlu.com wrote:
>>
>> > The surrogate key has its uses (others disagree on that) but you
>> > do have to recognise that there are risks. Any move away from the
>> > logical data structure has a risk. It's perfectly possible to
>> > take two copies of the same datum and attach two different
>> > surrogate key values.
>>
>> Well, its perfectly possible to attach the wrong ssn to a person's row.
>> How can you be assured that the correct ssn gets tagged to the correct
>> person? The arguments against surrogate keys haven't helped me change
>> my mind, although I wasn't the one asking. It should be quite obvious
>
>Although I believe we're digressing here...
>
>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.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti