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_at_newsread2.news.pas.earthlink.net>
>
> 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.
>
>
> 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.
Date: Tue, 21 Jun 2005 05:30:37 GMT
Message-ID: <1CNte.7368$jX6.6641_at_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 - 07:30:37 CEST