Re: Newbie question
Date: Tue, 21 Jun 2005 21:47:24 +0100
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
>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.
-- Bernard Peek London, UK. DBA, Manager, Trainer & Author.Received on Tue Jun 21 2005 - 22:47:24 CEST