Supertype-subtype database design

Question

I have a question about superype-subtype desing in a relational database. If I had a supertype with two subtype tables, I would have the PK of the supertype related to the PK of the two subtype tables as a FK. Let's say I had some thing like this:

Type

TypeID PK

SuperType

ID PK TypeID FK

SubtypeA

ID PK,FK

SubtypeB

ID PK,FK

On the database side, how would I ensure that Supertype ID's of a given type were only put into the appropriate subtype table? For example, I would not want a Supertype ID with Type A to be put into the SubtypeB table. Is there a way to easily prevent this from happening on the database side? I know this could be handled in code, but what if the code had mistakes? Or what if someone manually entered the wrong ID into one of the Subtype tables? I guess I'm looking for some way to make this impossible on the database side.

Any ideas? Maybe the PK on the Supertype table should be the ID and TypeID combination with a unique constraint on the ID column to prevent a record with both types being in the SuperType table... and then the Subtype tables would have the combo ID and TypeID PK with a constraint on the TypeID to only be of the type it should be for the appropriate subtype table??

Answer 1

On the database side, how would I ensure that Supertype ID's of a given type were only put into the appropriate subtype table?

On a DBMS that supports deferred constraints, you could do something like this:

With the following constraint on SuperType:

CHECK (
    (
        (SubtypeAId IS NOT NULL AND SubtypeAId = SuperTypeId)
        AND SubtypeBId IS NULL
    )
    OR
    (
        SubtypeAId IS NULL
        AND (SubtypeBId IS NOT NULL AND SubtypeBId = SuperTypeId)
    )
)

These peculiar circular FKs¹ combined with the CHECK ensure both exclusivity and presence of the child (the CHECK ensures exactly one of: SuprerType.SubtypeAId, SuprerType.SubtypeBId is non-NULL and matches the SuperTypeId). Defer the child FKs (or the CHECK if your DBMS supports it) to break the chicken-and-egg problem when inserting new data.

¹ SubtypeA.SubtypeAId references SuperType.SuperTypeId and SuperType.SubtypeAId references SubtypeA.SubtypeAId, ditto for the other subtype.

If your DBMS doesn't support deferred constraints, you could allow (in the CHECK) for both fields to be NULL and forgo the enforcement of the child's presence (you still keep the exclusivity).

---

Alternatively, just the exclusivity (but not presence) can also be enforced like this:

NOTE: You might need to add a redundant UNIQUE on SuperType {SuperTypeId, TypeId} if the DBMS doesn't support "out-of-key" FKs.

With the following constraint on SubtypeA:

CHECK(TypeId = 1)

And the following constraint on SubtypeB:

CHECK(TypeId = 2)

I used 1 and 2 to denote specific subtypes - you could use anything you like, as long as you are consistent.

Also, you could consider saving storage space by using calculated column for subtype's TypeId (such as Oracle 11 virtual columns).

---

BTW, enforcing presence and exclusivity through the application logic is not considered a bad overall strategy. Most of the time, you should strive to put as much integrity enforcement in the database as you can, but in this particular case doing it at the application level is often considered justified to avoid the complications above.

---

And finally, "all classes in separate tables" is not the only strategy for implementing inheritance. If you implement inheritance using "everything in one table" or "concrete classes in separate tables", enforcing both the presence and the exclusivity of subtypes becomes much easier.

Take a look at this post for more info.