Re: Sub-Entities

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. This is an awful design and total kludge that tells the world you are an OO programmer who does not understand RM.

Many years ago, the INCITS H2 Database Standards Committee(nee ANSI X3H2 Database Standards Committee) had a meeting in Rapid City, South Dakota. We had Mount Rushmore and Bjarne Stroustrup as special attractions. Mr. Stroustrup did his slide show about Bell Labs inventing C++ and OO programming for us and we got to ask questions.

One of the questions was how we should put OO stuff into SQL. His answer was that Bells Labs, with all their talent, had tried four different approaches to this problem and come the conclusion that you should not do it. OO was great for programming but deadly for data.

I have watched people try to force OO models into SQL and it falls apart in about a year. Every typo becomes a new attribute or class, queries that would have been so easy in a relational model are now multi-table monster outer joins, redundancy grows at an exponential rates, constraints are virtually impossible to write so you can kiss data integrity goodbye, etc. Received on Sat Jul 02 2005 - 22:59:26 CEST