Solving supertype-subtype relationship without sacrificing data consistency in a relational database

Question

Trying to get better at designing databases, I noticed I'm always stuck trying to solve variations of the exact same problem.

Here is an example using common requirements:

1. An online store sells different categories of product.
2. The system must be able to retrieve the list of all product categories, say food and furniture.
3. A customer may order any product and retrieve his order history.
4. System must store specific properties depending on the product category ; say the expiration_date and calories for any food product and manufacture_date for any furniture product.

If it wasn't for requirement 4, the model could be quite straightforward:

Problem is trying to solve requirement 4. I thought of something like this:

In this approach, the relationships product-furniture and product-food are supertype-subtype (or superclass-subclass) associations; the primary key of the subtype is also a foreign key to the supertype primary key.

However, this approach can not guarantee the category referenced via a foreign key to the product will be consistent with its actual subtype. For instance, nothing stops me from setting food category to a product tuple having a subtype row in the Furniture table.

I read various articles about inheritance in modelling relational databases, especially this one and this one which were very helpful but didn't solve my problem for the reason mentioned above. But whatever model I come with, I'm never satisfied with the data consistency.

How can I solve requirement 4 without sacrificing data consistency ? Am I going all wrong here ? If so, what would be the best way to solve this problem based on these requirements ?

Answer 1

One common way is to add a classifier that is "inherited" like:

CREATE TABLE products
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... NOT NULL 
,     UNIQUE (product_id, products_type)
,     CHECK (product_type IN ('food', 'furniture'))
);

product_type would typically be a code of some kind. Might be a foreign key to a "lookup" table instead of a check constraint. For the sub-types:

CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... DEFAULT 'food' NOT NULL
,    FOREIGN KEY (product_id, product_type)
     REFERENCES products (product_id, product_type)
,    CHECK (product_type = 'food')
);

and a similar one for furniture. The constraints guarantee that product_type is consistent between super- an sub- tables.

There are products (I've heard) that allow sub-selects in CHECK constraints, but the majority do not. For such product something like:

CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
,    FOREIGN KEY (product_id)
     REFERENCES products (product_id)
,    CHECK (
         (SELECT product_type 
          FROM products p 
          WHERE p.product_id = product_id) = 'food'
     )
);

could be used.

An alternative to the latter is to use before triggers for insert/update, and signal an exception if the wrong product_type is used. Personally I don't fancy using procedural code for integrity constraints, but I guess it is a matter of taste.

EDIT: If you for one reason or another want to abandon check constraints (personally I don't find them problematic) you can easily replace them with:

CREATE TABLE food_types
( food_type text not null );
INSERT INTO food_types (food_type) VALUES ('food);

Now you can add a foreign key constraint instead of the check constraint like:

CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... DEFAULT 'food' NOT NULL
,    FOREIGN KEY (product_id, product_type)
     REFERENCES products (product_id, product_type)
,    FOREIGN KEY (product_type)
     REFERENCES food_types (food_type) 
);

Answer 2

Maybe I'm simplifying the requirement, but to satisfy the requirement and your concern is to simply set the required columns in the subtype to NOT NULL. When a new PRODUCT row of food is created, a new FOOD row will also be created, and if no info is provided required for FOOD, then the database will reject the transaction.

Answer 3

in general with subtypes you will have a column in the supertype that specifies which subtype this is ... so the general/common product table will know if this is furniture/food/etc ... in your case the identifier is the category

to me your 2nd pic above is exactly how it should be

Answer 4

I don't think a database should be responsible for stopping someone from misclassifying the sub-type of a record. That seems much more like a business logic layer problem. So, I don't see too big of a problem with your second scenario. I've seen it work pretty well in even very large enterprise solutions.

At large table size (like 2 million or more), the JOIN query that will be necessary to combine sub-class and super-class is going to have trouble scaling with ad-hoc queries if you need to join more than a few thousand results between the two tables. For that reason a Table-per-hierarchy model might be better - the object inheritance is flattened into a single physical table.

I do wonder about what would be the best way to handle the case of re-classifying a product in your example. Any sub-type record that was previously created would need to be deleted since only one sub-type should exist for each super-type record - assuming only a single layer of inheritance. Should that be a database trigger or something handled in the Business Logic Layer?