> > I should perhaps be posting this under another subject, but I feel that
> > beneath the surface, Michael's problem and my own are strongly related.
> There is also the problem that a name can change. People change names
> by deed-poll, and also women can adopt a married name or keep their old
> one. All in all an ID is about the only answer.
I'll take the other side of this issue. The fact that a primary key
is mutable does not make it any less primary. As long as we can can
count on it to be unique, how often identiying info changes has no
bearing on its selection as a p-key from a relational standpoint. SQL
gives us RI to help deal with this but in this is not always practical
if for example you you have a changing p-key that cascades to a
million records. The performance issue has zero meaning in a
conceptual sense however and I think you are trying to grapple things
in conceptual terms.
By assigning a surrogate key to a person, you are simply moving the
guess work from one place to another. If you can't logically
determine who 'John Smith' is, how can you possibly expect to relate
information to him? (which john smith? why, etc)...you are just hiding
a guess behind a number. Put into other words, *a record must have
unique identifiying criteria or the table containing it cannot be
expected to give correct results*. This is, more or less, a
mathematical truth. The non key attributes of the tuple are now
undefined because they can give two or more different answers to the
same question. Surrogates do not change this principle, they just
hide it but it still has to be dealt with.
Merlin