Re: Separate foreign keys with shared ID space

"Christopher Browne" <cbbrowne_at_acm.org> wrote in message news:2n27o8Fs39uuU1_at_uni-berlin.de...
> After a long battle with technology, "Marshall Spight" <mspight_at_dnai.com>,
an earthling, wrote:
> > "Christian Antognini" <christian.antognini_at_trivadis.com> wrote in
message news:410affcf$1_at_post.usenet.com...
> >>
> >> A PK should have no business meaning.
> >
> > Says who? Can you justify this statement?
>
> A good reason for this is that business meanings can change, but
> primary keys can't.

On the contrary, primary keys, as with any key, can change as long as they don't run afoul of another primary key value.

>
> A typical example of this is the use of the government "social
> insurance/security" as a PK. It's not _supposed_ to change, but it
> can.
>

> Supposing somebody does a "steal my identity" thing using my SIN/SSN
> number, and things go so gravely badly that the government actually
> decides that it is a better thing to give me a new number, that
> _breaks_ the use of SSN/SIN as a primary key.

Why?

CREATE TABLE old_ssns
(
old_ssn CHAR(9),
current_ssn CHAR(9),
PRIMARY KEY (old_ssn)
);

UPDATE people
  SET SSN = <new SSN>,
 WHERE SSN = <old SSN>

INSERT INTO old_ssns
  (old_ssn, current_ssn)
VALUES (<old SSN>, <new SSN>);

COMMIT;
>
> If we fabricate a number of our own as an "employee ID," that's well
> and good, until such time as there is a corporate merger that has
> conflicting ID spaces so that peoples' IDs have to change.

I agree that this is a problem but, it is a problem of logic and reflecting how the world works and changes. I think that convoluting the notion of keys (which are by definition a subset of properties of some entity) reflecting semantics of certain properties to somthing different is not always the proper approach.

Codd actually went into this in depth with his RM/2 proposal, which I really need to reread. I think his proposal and ideas haven't been given the attention they deserve.

• Dan > --
  > (format nil "~S@~S" "cbbrowne" "acm.org")
  > http://cbbrowne.com/info/sgml.html
  > "That's convenience, not cracker-proofing. Security is an emergent
  > property, not a feature." -- void <float_at_interport.net>