Re: Newbie question

In message <1CNte.7368$jX6.6641_at_newsread2.news.pas.earthlink.net>, Jonathan Leffler <jleffler_at_earthlink.net> writes
>Bernard Peek wrote:
>> Jon Heggland <heggland_at_idi.ntnu.no> writes
>>> I'm not sure why you think surrogate keys are not acceptable---they are
>>> a staple of relational database design---but I'll assume you think they
>>> are useless in the presence of natural keys (which is the term for keys
>>> that come from the "real world").
>> I haven't found anyone who says that surrogate keys are
>>unacceptable. They do have disadvantages though so I would usually
>>suggest trying to use natural keys wherever possible.
>>
>>> Off the top of my head, the main
>>> advantages of surrogate keys vs. natural keys are that they are stable
>>> (I.e. they don't change),
>> That is one of their failure modes. If the natural key changes then
>>any surrogate keys should change too. If they don't then the system is
>>badly broken.
>
>Can you elaborate on this? Why does a surrogate have to change when
>the natural key for which it is a surrogate changes? Do you have some
>historical (temporal) system in mind?

Nope. The natural key identifies the object that the record refers to. If the natural key changes it's because the record now refers to a different object. It should get the new object's surrogate key too.

>Suppose either that someone made a mistake entering an employee's name
>in the database or that an employee gets married and changes their
>name; neither circumstance means, in my view (understanding) that their
>employee number needs to change. The records that were associated with
>their past performance, for example, are still applicable to them under
>the corrected or new name.

Names are not natural keys. If you want a natural key for a person try the exact latitude, longitude, altitude and precise time of their birth.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.