design question - type heirarchy with supertype queries

[nflacco]

Is there a convient way to do a supertype/subtype heirarchy in mysql
and do queries on the supertype to return sets of subtypes?

For example, suppose I have a table with several types of military
hardware:

Table:
Id----Type--------Price
1.....Mig-15.....$20
1.....Mig-17.....$32
1.....Su-27......$80
1.....T-72........$20

What I'd like to be able to do is say:
SELECT FROM Table Where Type=Mig
instead of
SELECT FROM Table Where Type=Mig-15 or Type=Mig-17

Of course, we have to assume we have a type heirarchy:

Airplane
--Mig
----Mig-15
----Mig-17
--Su
----Su-27
Tank
--T
----T-72

What's the best way to represent this type heirarchy in the database
so as to require the minimum amount of queries to get a meaningful
result like all subtypes of Mig?

Thanks in advance,
Nick

[--CELKO--]

Why are you asking MySQL questions in a SQL Server Newsgroup?

Get a copy of TREES & HIERARCHIES IN SQL for several different ways to
model such structures.

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_t ype 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_t ype = '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_t ype = '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_t ype 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_t ype = '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_t ype = '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_t ype 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.

[Hugo Kornelis]

On Wed, 23 Apr 2008 14:48:28 -0700 (PDT), nflacco wrote:

>Is there a convient way to do a supertype/subtype heirarchy in mysql
and do queries on the supertype to return sets of subtypes?

For example, suppose I have a table with several types of military
hardware:

Table:
Id----Type--------Price
1.....Mig-15.....$20
1.....Mig-17.....$32
1.....Su-27......$80
1.....T-72........$20

What I'd like to be able to do is say:
SELECT FROM Table Where Type=Mig
instead of
SELECT FROM Table Where Type=Mig-15 or Type=Mig-17

Hi Nick,

In this specific case, you can use WHERE Type LIKE 'Mig%'. But I guess
that's sidestepping your actual question :)

>Of course, we have to assume we have a type heirarchy:

Airplane
--Mig
----Mig-15
----Mig-17
--Su
----Su-27
Tank
--T
----T-72

What's the best way to represent this type heirarchy in the database
so as to require the minimum amount of queries to get a meaningful
result like all subtypes of Mig?

There is no single best way. There are several methods; which one you
choose depends on the type of operations you often do. If you google the
terms below, you'll find a wealth of information:
* Adjacency list model
* Nested sets model
* Materialized path model

In SQL Server 2008, there will also be a new data type, HierarchyID,
which is basically an encoded and optimised materialized path. From what
I've seen so far, builtin support for actually handling the hierarchy
apppears to be rather sparse though.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[nflacco]

"Why are you asking MySQL questions in a SQL Server Newsgroup? "

I actually am using MySQL and posted in the wrong group by accident.
However, I'm less worried about the specific features mysql or
postgres or mssql has that would deal with my problem as much as
general way of dealing with this as I'm thinking of switching to
postgres once mysql is close-sourced by sun.

As for the solutions, the cascade looks pretty interesting and I'll
try that if I have time in the next few days. Also, I looked up some
of the terms given in Hugo's response, found lots of good stuff like:

http://troels.arvin.dk/db/rdbms/links/#hierarchical

thanks!