Re: Newbie question

In message <MPG.1d232e6a11c10d119896ac_at_news.ntnu.no>, Jon Heggland <heggland_at_idi.ntnu.no> writes

>In article <gTH5vQUczHuCFwXh_at_shrdlu.com>, bap_at_shrdlu.com says...
>> >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.
>
>Not necessarily; it depends on the problem domain, the purpose of the
>database, and the nature of the change. If you change an erroneous
>natural key, does the "record" now refer to a "different object"? If you
>change the license plates of a car, does it become a "different object"?
>For some purposes yes, for others, no. How do you define "different
>object"?

I did consider whether to mention the exception for incorrect key values, but decided not to. If the key value is incorrect then it points to the wrong object.

License plates are not natural keys that identify cars if it's possible to change plates on the same car.

>
>> It should get the new object's surrogate key too.
>
>Why? What breaks badly if they don't?

The point of having a key is that there is a 1:1 mapping between key values and the objects they identify. If one key value can be mapped to two or more different objects why use it?

>
>(I find the phrase "the new object's surrogate key" a little
>objectionable, too---it doesn't *have* a surrogate key unless and until
>we decide to generate one.)
>
>> Names are not natural keys.
>
>That depends on the problem domain and the purpose of the database.

That's true. You could have a list of all of the possible unique names, but I can't think of any practical use for that.

>
>> If you want a natural key for a person try
>> the exact latitude, longitude, altitude and precise time of their birth.
>
>That is an excellent example of the need for surrogate keys. :)

Of course. There are times when there is no real alternative to using a surrogate key. They are very useful at times, but I usually avoid using them if I have a natural key available. Maintaining the 1:1 mapping between surrogate keys and the objects they identify can involve a lot of work and that usually increases the risk of error.

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