Re: Newbie question

In article <sfO68KRBpawCFw2f_at_shrdlu.com>, bap_at_shrdlu.com says...
> 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.

Yes, if we want to keep track of this in the database, then I agree with you. If not, the relationship as represented in the database ("current" data only) is 1:1, and should be constrained as such.

> >> 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.

There is an update anomaly when a record is updated using a natural key that does not point to the correct object.

There is an update anomaly when a record is updated using a natural key that does not point to the correct object.

I see no logical difference (though there may be hidden assumptions in the word "correct"). There might be a *practical* difference if a natural key is easier to remember or verify in the real world, though.

> 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.

This I very much disagree with. Normalisation has nothing to do with whether a key is surrogate or natural.

> >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.

We're not getting anywhere. Unless you can show with examples what weaknesses you are talking about, and why surrogate keys must be changed when natural keys change, I see no reason to continue.

-- 
Jon