Re: Database design, Keys and some other things


"Marshall Spight" <marshall.spight@xxxxxxxxx> wrote in message
news:1127832798.313559.156760@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> Definitely! But artificial isn't the same as "not part of the real
> world." The claim was made that the real world didn't contain
> predicates that included artificial keys. So I guess that
> means VIN, bank account number, and SSN are supernatural! Spooky!

Agreed.

But that raises another question: is the system we are building going to be
"part of the real world" once it is built?
My answer is a definite yes. But that means that 'PersonId' is no more and
no less artificial than 'PersonSSN'.
What may be a difference between these two data items is that PersonId will
be kept hidden from the users of the data, while SSN will not.

However, I've seen numerous systems, in the real world, where what was
originally envisioned as a hidden internal key ends up being shared with
special administrative users at first, and later with the general database
user community. So the principle of information hiding of surrogate keys
is, at best, fragile in practice.

The next question is whether a surrogate key and what we are pleased to call
a 'natural key' really identify the same thing or not. In many cases, there
is strong evidence that a surrogate key identifies a tuple, while a natural
key identifies a real world entity that the contents of the tuple describe.
If you'll go back to the origins of this thread, I think you'll see an
example of what I've just said.

If, however, you accept the idea that a surrogate key identifies a tuple,
and not a real world entity directly, then you have to come up with a whole
different response than yours or mine to dawn's claim that a foreign key
amounts to a logical pointer.

If the sole purpose of a foreign key is to act as a surrogate for the
address of the record that represents the tuple, then the argument that
it's really a pointer in disguise begins to carry more merit.



.