Re: Surrogate Keys: an Implementation Issue

"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message news:Xkoyg.28072$pu3.371631_at_ursa-nb00s0.nbnet.nb.ca...
> 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.
>

I used this example to make a valid point. I could have written a 20-page long dissertation describing one of the many instances where I've encountered these problems in practice, but I assumed that using a simple example would illustrate them more effectively. Instead of answering Keith's question, you resort to inane criticism about poor key choice, and no substantive attempt to address the issues presented.

By the way, you need to look up the definition of a candidate key. Within these domains of discourse, people's names do uniquely identify them.

>
>>>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.
>
> [snip]
>
>
>>>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 Sat Jul 29 2006 - 03:48:59 CEST