Re: What predicates the following relation represents

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 31 Mar 2004 16:17:59 -0800
Message-ID: <a264e7ea.0403311617.358d5a3a@posting.google.com>

> SALES
> =====
> PART SOLD
> ---- ---
> nuts 10
> nuts 15
>
> Is SALES a legal relation? Is database really a repository of facts?

I am still playing with this idea, so let me ramble a bit.

I'd take the position that it is not a proper relation. Assuming that the columns have their obvious meaning, there is no key -- it should have been the part column.

But more than that, the rows do not represent a *complete* fact, namely that we sold 25 nuts. Now think about a very common design flaw in order detail tables:

CREATE TABLE OrderDetails
(order_nbr INTEGER NOT NULL

           REFERENCES Orders(order_nbr)
           ON DELETE CASCADE
           ON UPDATE CASCADE,

 line_nbr INTEGER NOT NULL,
 PRIMARY KEY(order_nbr, line_nbr),
 upc CHAR(10) NOT NULL

         REFERENCES Inventory(upc)
         ON UPDATE CASCADE,

 qty INTEGER NOT NULL);

When I enter two lines on the paper form from which this table gets it data, I can easily forget that I already had the same thing:

 (123, 1, '0000011111', 14)
 (123, 2, '0000022222', 12)
 ...
 (123, 20, '0000011111', 10)

The problem comes from splitting a single fact over lines 1 and 20 on the original paper order form and then mimicking that paper form in the table. The **real** key for the item is the UPC code; the line number on a paper form is a "locator" (better word anyone?). The UPC code is easily verified by reading the bar code if I have to! There is nothing special about the line number that compels it to hold a particular item.

Another example: The VIN number on an automobile is a real key (required, permanently burned into a metal block, verifiable in itself, etc.); the auto tag is a locator (subject to change, not verifiable in itself, assigned by chance, etc.); the parking space number for the automobile is a still weaker locator.

To summarize the idea I am trying to define:

Keys = locators or identifiers
Locators = weaker or stronger
Facts in a database = missing, incomplete, split or complete

Does this seem useful? Received on Wed Mar 31 2004 - 18:17:59 CST