Modeling categories and sub-categories using recursive network models

[elsen...@sbcglobal.net]

I'm building a system that will allow my client to track materials used
in framing prints and photos. One of the materials is mouldings
(frames). Many moulding manufacturers can produce mouldings that fall
into the Wood sub-category. Right now I have it modeled as
Moulding_Category instead of a sub-category. That is unique to a
certain Moulding provider which means I could never have something like
"Pine" as a drop down. I think what I really want is sort of a 'recipe'
model, a recursive network but I'm not sure. I'd like to have the
Moulding entity modeled in such a way that what makes a moulding unique
is the material used as well as dimensions, etc. Does this make sense?
My application will allow for a pull-down 'Moulding' which will
activate another pull-down 'Material', and subsequently 'Color'. Does
any of this make sense and does anyone have any experience modeling
something similar? Thank you.

[Bill Karwin]

I'd treat these as three separate attributes, not a heirarchy exactly.

I'd create additional tables that provides a map between a given
manufacturer, material, and color. That way you can track what you need
to display in the second and third drop-down once the user has chosen
the first one.

And the mapping table has a compound primary key over the three fields,
to which the three fields in the user's order reference using a foreign
key relationship.

Since the offerings inevitably change over time, you'll also need
another field in the mapping table to mark certain combinations as
"discontinued" and use that to exclude them from being shown in
drop-down menus, while still allowing them to stay in the database so
that past customer orders can maintain their referential integrity.

Regards,
Bill K.

[--CELKO--]

This does not sound like a hierarchy since a frame must have a
material, length and width, etc. These are attributes.

You might want to get a copy of TREES & HIERARCHIES IN SQL, tho, for
future use.

[elsen...@sbcglobal.net]

Hello and thanks to both. Indeed, I am NOT modeling a hierarchy. A
hierarchy represents an entity's relationship to itself, i,e; an
employee can manage other employees and also be managed by another
employee. The relationship becomes a recursive hierarchy with
employeeID becoming the PK and something lile employee_managerID
becoming the FK. What I am modeling is an entity's relationship to its
constituent components, i,e; a 'recipe'. In my example, a 'Moulding'
has attributes such as celko mentions, length, width, etc. but it also
consists of a 'Material' with 'Material' having its own attributes such
as wood, plastic, metal, etc. This creates a recursive network. In
thinking through this, I came upon my answer. In essence, I need to
create the 'Moulding' entity, a 'Material' entity (with entries for ID,
and material name or description such as wood, etc.), and a
'MaterialType' associative entity for the different types of 'Material'
such as wood-pine, plastic-cv2, glass-tempered, etc. If anyone sees a
flaw in this logic, I'm more than open to constructive criticism! My
feelings won't get hurt and I'd rather model this correctly in the
logical realm than implement it physically and later have to tear it
apart and rebuild!

[--CELKO--]

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.

[Jan M. Nelken]

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

Wouldn't the vehicle type be deriveable directly from VIN?

I believe that decoding VIN would give not only typr designator (SUV versus
SEDAN) but also body style (2,3,4 or 5 doors).

Kan M. Nelken

[--CELKO--]

>> Wouldn't the vehicle type be deriveable directly from VIN? <<

Yes, in this case. I prefer to say verifiable from the VIN. A key
needs validation and verification.

[elsen...@sbcglobal.net]

I'm not modeling a supertype/subtype relationship, though. I'm modeling
a 'consists of/comprises' relationship. A 'Moulding' consists of
'Metal' or 'Wood', 'Moulding' is not a subclass of 'Metal' or 'Wood'
nor is 'Metal' or 'Wood' a subclass of 'Moulding'. Also, with a
supertype/subtype model, I'd be adding a table every time I add a new
component of a finished work of art. So far, I ended up creating three
tables:

'MaterialStructure' which consists of ParentMaterialID (PK)(FK),
ChildMaterialID (PK)(FK), and StructureName - This would include
Mouldings, Mats, Frames, Prints, etc.
'Material' which consists of MaterialID, MaterialConsistsOfID, and
MaterialName - This would include Metal, Wood, Plastic, Glass, etc.
'MaterialConsistsOf' which consists of MaterialConsistsOfID, Component
- This would define what a specific material consists of. For example,
'Material' wood is further classified by 'MaterialConsistsOf' pine.
'Material' glass, could be further classified by 'MaterialConsistsOf'
tempered, leaded, etc.

Again, if you think of this as a recipe model, you'll see the logic.
I'm still refining the model but I think you can see where I'm coming
from.

Food:
Food_ID
Food_Desc

Food_Structure
Parent_Food_ID
Child_Food_ID
Child_Food_Qty_Used

This set of relationships can answer the following questions:
- What is used in the making of a food element?
- What quantity is used to make another food element?
- What food elements does another comprise?
- What quantity of a food element is used in another?

It's the same idea I'm using for my scenario with the addition of the
'MaterialConsistsOf' table.