Re: Theory Question--Two tables relating in more than one way?

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 1 Mar 2005 08:25:34 -0800
Message-ID: <1109694334.552345.60690@f14g2000cwb.googlegroups.com>

>>a car could have more than one owner.<<

But can a car have one and only one primary driver? My primary might be your secondary vehicle.

CREATE TABLE MotorPool
(vin CHAR(17) NOT NULL

     REFERENCES Cars (vin)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
 license_nbr INTEGER NOT NULL
     REFERENCES Owners(license_nbr)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
 car_status INTEGER DEFAULT 0 -- zero is primary car
       CHECK (car_status >= 0);

PRIMARY KEY (vin, license_nbr, car_status) one status per car-driver pair,
CHECK ( (SELECT COUNT(DISTINCT vin) FROM MotorPool)

(SELECT COUNT(car_status) FROM MotorPool WHERE car_status = 0)) );

That last constraint is Standard SQL-92, but not widely implemented yet.

>> Also, is it a design flaw to have two tables related in more than
one way? Does it violate TNF? <<

No. But you need to be careful about cycles. I am having a hard time coming up with a reasonable example, tho. You might want to Google a series of articles by Tom Johnston in DM Review on other data redundancies; normalization is not enough. Received on Tue Mar 01 2005 - 10:25:34 CST