Re: modeling either/or relationship...

-CELKO- schrieb:
> The classic scenario calls for a root class with all the common
> attributes and then specialized sub-classes under it. As an example,
> let's take the class of Vehicles and find an industry standard
> identifier (VIN), and add two mutually exclusive sub-classes, Sport
> utility vehicles and sedans ('SUV', 'SED').
>
> CREATE TABLE Vehicles
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) NOT NULL
> CHECK(vehicle_type IN ('SUV', 'SED')),
> UNIQUE (vin, vehicle_type),
> ..);
>
> Notice the overlapping candidate keys. I then use a compound candidate
> key (vin, vehicle_type) and a constraint in each sub-class table to
> assure that the vehicle_type is locked and agrees with the Vehicles
> table. Add some DRI actions and you are done:
>
> CREATE TABLE SUV
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
> CHECK(vehicle_type = 'SUV'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
Yes, that is what I did. I'm just a bit frustrated at my design tool that it doesn't model it like this and I have to use stuff that's not visible on the printed out EERD.
Also, since my root table doesn't contain any common attributes, just the name (primary key) and the single permitted subtype for that particular type I haven't defined the unique constraint. Ditto for the subtype table since the check constraint ensures uniqueness by constraining to a single value and primary key ensures uniqueness of the primary key anyway.
Am I missing anything there?

Lots of Greetings and Thanks!
Volker Received on Mon Jan 30 2006 - 13:32:18 CET