Re: Newbie question

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Tue, 21 Jun 2005 05:30:37 GMT
Message-ID: <1CNte.7368$jX6.6641@newsread2.news.pas.earthlink.net>

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?

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.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/

Received on Tue Jun 21 2005 - 00:30:37 CDT