Re: Newbie question

In message <MPG.1d2773f4580185819896b3_at_news.ntnu.no>, Jon Heggland <heggland_at_idi.ntnu.no> writes
>In article <o0ALWJBGtFvCFw8y_at_shrdlu.com>, bap_at_shrdlu.com says...
>> >Perhaps, perhaps not. There may not even *be* an "object" to "point to";
>> >the incorrect key value may be unused in the real world. Anyway, that
>> >still does not answer the question why you would have to change the
>> >surrogate key.
>>
>> To maintain 1:1 mapping between surrogate keys and the natural key,
>> which identifies the object that the record holds data on.
>
>No, there is still a 1:1 mapping even if you don't change the surrogate
>key. I think we are talking past each other here. Are you talking about
>a mapping in an informal sense, as in existing in the users' minds
>and/or outside of the database?

Perhaps we are talking past each other. As far as I'm concerned if there are two different surrogate key values at different times there can't be a 1:1 mapping, it has to be 1:n instead. Granted there is only one at any given time.

There's a missing entity which is time and another one, a link entity, that includes the identity of the object to which the key is supposed to point and also the time period during which each value points to that object.

[...]
>> Essentially I see three extra risks when surrogate keys are used.
>>
>> There is an insertion anomaly when a new record is created that assigns
>> a new surrogate key value for an object which already has a record in
>> the database.
>
>Yes, this is known as the singularity problem, I think. You have the
>same problem with incorrect natural keys, though.
>
>> There is a deletion anomaly when a record is selected for deletion using
>> a surrogate key that does not point to the correct object.
>>
>> There is an update anomaly when a record is updated using a surrogate
>> key that does not point to the correct object.
>
>Isn't this just the problem that it is always possible to feed incorrect
>information to the database?

There is always that problem but these are additional risks that don't exist if you use a natural key. If you use a surrogate key there are additional ways of corrupting your data.

Surrogate keys are a special case where the general case is denormalisation to improve performance. They introduce precisely the same additional risks as denormalisation, and for exactly the same reasons.

>
>Anyway, I think we agree that both natural and surrogate keys are
>useful. My main argument is that it is never necessary to change
>surrogate keys.

I'm certainly not saying that surrogate keys aren't useful. But they do introduce structural weaknesses into the database design, and those weaknesses have to be compensated for. In most cases that I've seen the compensation isn't possible in software, it has to be applied using business processes outside the database and sometimes outside the control of the database designer.

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