Re: Surrogate Keys: an Implementation Issue

From: Bob Badour <>
Date: Fri, 28 Jul 2006 13:31:35 GMT
Message-ID: <Xkoyg.28072$>

Keith H Duggar wrote:

> Brian Selzer wrote:

>>Current: Jane Jones Married   Proposed: Jane Smith Divorced
>>Do Jane Jones and Jane Smith represent different people?
>>Or did Jane Jones get Divorced? Because the only key is
>>mutable, there isn't enough information to answer that.

What key? Brian never specified one. A person's name is not a candidate key because people's names do not uniquely identify them, which is the primary function of a candidate key. Hence, one can reject the proposed key without even considering the design criteria of stability or simplicity.

>>Another problem involves temporal constraints. Consider
>>the following states for the people table and a transition
>>constraint, Single people can't become Divorced:
>>Current: Jane Jones Married   Proposed: Jane Jones Married
>>Current: Jane Smith Single    Proposed: Jane Smith Divorced
>>Should the proposed state be rejected?  Or is it possible
>>that Jane Jones got Divorced becoming Jane Smith and Jane
>>Smith married Bob Jones?  Because the only key is mutable

What key? I see no reason to bother with idiotic straw men. Selzer needs to propose a valid candidate key. Thus, the candidate key must uniquely identify whatever it is he wants to discuss.


>>It should be obvious that surrogates solve these problems.

It should be obvious that all candiate keys are surrogates. Natural keys are merely familiar surrogates.

> I know this is asking a lot; but, would someone please
> address the examples/arguments Brian gave above? As a newb
> trying to learn RM principles the above examples seem
> interesting and I would appreciate learning how to properly
> handle them.
> Also, can someone confirm whether I have properly understood
> one of BBs comments
> Bob Badour wrote:

>>In the final case above, if something changes that does
>>not affect the truth of the assertion, it is an
>>unimportant change that we can ignore.  If something
>>changes that does affect the truth of the assertion, the
>>integrity function should detect that the assertion was
>>not derivable given the new information.

> Does this mean, for example, that if I selected a tuple
> (K0,A0...AN) say with key value K0, went on vacation during
> which time K0 was modified (name change for example) to K1,
> returned from vacation and modified some subset of (A0...AN)
> to (A0...AN') and then submitted the assertion (K0,A0...AN)
> -> (K0,A0...AN') that the integrity function would signal
> some error? Something like "Error no tuple (K0,A0...AN)"
> exists?

Your example is a little far-fetched in that it assumes a user will make an assertion based on information that is days and perhaps weeks out of date.

A well-written application, in this case, would implement 'check exists' logic, though. An interactive query tool would report that zero rows were updated, for instance.

  To recover from this error would I need to discover
> K1 and confirm that (K1,A0...AN) -> (K1,A0...AN') remains
> (after my vacation) an assertion that I wish to make? What
> is this "discovery" process called?

 From a logical perspective, K0 was deleted and K1 inserted. It would suffice to inform the user that K0 was deleted. At that point, the user knows the information he has is not valid and must seek to correct it. How he does that will depend on all of the tools he has available. Received on Fri Jul 28 2006 - 15:31:35 CEST

Original text of this message