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
.

Relevant Pages

  • Re: modeling either/or relationship...
    ... >> identifier (VIN), and add two mutually exclusive sub-classes, Sport ... > allowing a reference to different columns than those of a primary key? ...
    (comp.databases.theory)
  • Re: why use 4nf?
    ... > industry standard codes, such as UPC, VIN, GTIN, ISBN, etc. ... In any event if you use an "artificial" key as your primary key, ... You would have the personelId. ... vehicleId int NOT NULL REFERENCES Vehicle), ...
    (microsoft.public.sqlserver.programming)
  • Re: AutoNumber Question
    ... particular field only applies if that field is a Primary Key? ... SSN or VIN anaolgies, couldnt one still ensure the uniqueness of them, even ... database, as it would be if it was part of relationships. ... important one) of a vehicle. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Naming conventions for special database objects
    ... If you want it to be required, why not use a DEFAULT and a NOT NULL constraint? ... identifier (VIN), and add two mutually exclusive sub-classes, Sport ... utility vehicles and sedans ... NOT NULL PRIMARY KEY, ...
    (comp.databases)
  • Re: Sub-Entities
    ... identifier (VIN), and add two mutually exclusive sub-classes, Sport ... NOT NULL PRIMARY KEY, ... REFERENCES Vehicles ... ON UPDATE CASCADE ...
    (comp.databases.theory)