Re: what are keys and surrogates?

From: rpost <rpost_at_pcwin518.campus.tue.nl>
Date: Wed, 13 Feb 2008 22:25:56 +0100
Message-ID: <1961$47b36064$839b4533$319_at_news1.tudelft.nl>


Bob Badour writes:

>JOG established the need for natural keys. You have not established the
>need for OID. A solution without a problem is no solution at all.

It was established elsewhere; this is what Marshall's "abstract syntax trees" example is about. But there is no need to use such an abstract example; e.g. management hierarchies or geneaology graphs of people will do just as well.

Let's consider a minimal genealogy database: every person has a father, a mother, and a name, but no other attributes. Using oids we can express this directly as a Person relation with attributes PersonID, FatherID, MotherID, and Name. You and JOG are expected to complain that name is the only natural attribute, but is by no means a key: many different persons have the same names. But if all we want is for every person to be uniquely identifiable in the database, we can live with much weaker requirements! For instance, if the genealogy is *complete* (e.g. assume, for the sake of argument, that the Bible is correct, and define the father and mother of Adam, Eve, and God to be God), then we already have unique identifiability if no two *siblings* have the same names.

What is more, this mechanism for the identification of persons is very common in practice; e.g.

  http://en.wikipedia.org/wiki/Genealogy_of_Jesus

(where only father and name are used, not mother).

So references / oids can not only be useful in identification, for expressing certain situations they are *required*.

[...]

>The point, you seem to keep missing, is something must connect what you
>are using in the database to what you are using elsewhere for the
>database to have any meaning elsewhere. Just adding a numeric identifier
>does nothing unless you somehow associate that identifier with something
>somehow recorded outside the database. In the example given, by
>recording it on the pieces of paper.

To add a natural key is to cheat: you make the model fit by extending reality. And, as I said before, it isn't always possible. E.g. we can't ask biblical figures to start carrying name tags around. Even when you attempt to do it while the persons in question are still alive, all kinds of practical issues may arise (ask Herod).

-- 
Reinier
Received on Wed Feb 13 2008 - 22:25:56 CET

Original text of this message