On Aug 31, 9:51 am, philiptaylo...@yahoo.com wrote:

Hi, Philip.

I think that's a bad idea. I know this may sound a bit shop-worn, but
all subtypes should be mutually exclusive and exhaustive. Further,
they should each describe a semantically unique "kind of" the
supertype, with each subtype described by attributes unique to it.

Taking these two guidelines together, this suggests (to me, anyway)
that if all subtypes are known, then all instances of the domain of
any category discriminator of these subtypes are also to be known.

Inspecting the value of the PK to decide which subtype to navigate to
also smacks of "smart" keys, which gets us to the whole natural key
vs. smart key vs. surrogate key holy war. Besides, if the PK is, say,
a 4-byte unsigned integer, are you saying you'd have (in order to
satisfy the "exhaustive" property) 4,294,967,296 subtypes?

Now, if you wanted to add a category discriminator to your AB
supertype and make it part of a three part composite key, that's
possible, assuming you're enforcing a business rule that mandates that
you may only have one instance of each subtype.

Perhaps you can elaborate a bit more on what you're after, and we can
humbly suggest better approaches?

Regards,

--Jeff

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,
..);

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
      CHECK(vehicle_type = 'SED'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
 REFERENCES Vehicles(vin, vehicle_type)
 ON UPDATE CASCADE
 ON DELETE CASCADE,
..);

I can continue to build a hierarchy like this.  For example, if I had
a Sedans table that broke down into two-door and four-door sedans, I
could a schema like this:

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
      CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
 REFERENCES Vehicles(vin, vehicle_type)
 ON UPDATE CASCADE
 ON DELETE CASCADE,
..);

CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
      CHECK(vehicle_type = '2DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
 REFERENCES Sedans(vin, vehicle_type)
 ON UPDATE CASCADE
 ON DELETE CASCADE,
..);

CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
      CHECK(vehicle_type = '4DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
 REFERENCES Sedans (vin, vehicle_type)
 ON UPDATE CASCADE
 ON DELETE CASCADE,
..);

The idea is to build a chain of identifiers and types in a UNIQUE()
constraint that go up the tree when you use a REFERENCES constraint.
Obviously, you can do variants of this trick to get different class
structures.

If an entity doesn't have to be exclusively one subtype, you play with
the root of the class hierarchy:

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL
      CHECK(vehicle_type IN ('SUV', 'SED')),
PRIMARY KEY (vin, vehicle_type),
..);

Now start hiding all this stuff in VIEWs immediately and add an
INSTEAD OF trigger to those VIEWs.