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

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 1 Mar 2005 06:39:14 -0800
Message-ID: <1109687954.839778.310740_at_l41g2000cwc.googlegroups.com>

Even for a made-up schema this was pretty bad :). At least give a nod to industry standards we all know, and use industry standard column types and sizes.

CREATE TABLE Cars
(vin CHAR(17) NOT NULL PRIMARY KEY,
 car_name VARCHAR(25) NOT NULL);

CREATE TABLE Owners
(license_nbr CHAR(9) NOT NULL PRIMARY KEY,  owner_name VARCHAR (35) NOT NULL);

Never use IDENTITY for a key, especially in a relationship table! You have a natural key that you must use:

CREATE TABLE CarOwnership
(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);

 UNIQUE (vin, car_status), -- one status per car  PRIMARY KEY (vin, license_nbr) -- one driver per car );

>> Every owner has at least one primary car, and may have one or many
others. <<

Being a primary vehicle is a property of the *relationship* between the driver and the car. It is not an attribute of a car per se, and certainly not an attribute of the driver. Received on Tue Mar 01 2005 - 15:39:14 CET