Re: How to show different attributes for different categories

>> I don't think they are completely different. Isn't it more like they are all products? So a book is a descendant of a product.
So why don't we use a table for products with all the common attributes
and additional tables for all the additional attributes a book has.
Isn't this more the solution that follows from an according ER? <<

This gets messy, too.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 for performance and searching. 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.

.

Relevant Pages

  • Re: Unique PK across two tables?
    ... identifier (VIN), and add two mutually exclusive sub-classes, Sport ... NOT NULL PRIMARY KEY, ... REFERENCES Vehicles ... ON UPDATE CASCADE ...
    (microsoft.public.sqlserver.programming)
  • Re: Updatable views
    ... identifier (VIN), and add two mutually exclusive sub-classes, Sport ... NOT NULL PRIMARY KEY, ... REFERENCES Vehicles ... ON UPDATE CASCADE ...
    (comp.databases.theory)
  • Re: modeling either/or relationship...
    ... > identifier (VIN), and add two mutually exclusive sub-classes, Sport ... > NOT NULL PRIMARY KEY, ... > REFERENCES Vehicles ... > ON UPDATE CASCADE ...
    (comp.databases.theory)
  • Re: design question - type heirarchy with supertype queries
    ... identifier (VIN), and add two mutually exclusive sub-classes, Sport ... NOT NULL PRIMARY KEY, ... REFERENCES Vehicles ... ON UPDATE CASCADE ...
    (comp.databases.ms-sqlserver)
  • Re: primary key as subtype discriminator
    ... identifier (VIN), and add two mutually exclusive sub-classes, Sport ... NOT NULL PRIMARY KEY, ... REFERENCES Vehicles ... ON UPDATE CASCADE ...
    (comp.databases.theory)