Modeling Inheritance in Relational Models

If you've ever designed Relational Model, you probably discovered that there are different ways to model the object-oriented concept of inheritance in relational models.  This becomes particularly important when using an Object-Relational Mapping (ORM) library, such as Hibernate.

There are several ways to accomplish this modeling, but I'll only talk about two methods, which I call:

1. Differentiation on a "discriminator" field
2. Shared Primary Key

Differentiation on a "discriminator" field
With this solution, there is a single table for the parent, children, grandchildren, and so on.  In this table, one field is a "discriminator," from which the type of the object can be derived.  The benefit to this solution is that it is a very simple way to model inheritance.  Unfortunately, it's also not a very robust solution.  First, if the children/grandchildren/etc. have many (disjoint) fields, it can lead to a very sparsely populated table (i.e., lots of "NULL" values).  Furthermore, making changes to the structure of the objects or hierarchy can be very tricky.

id	type	parent_field	child_1_field	child_2_field
1	parent	1	NULL	NULL
2	child_1	2	1	NULL
3	child_2	3	NULL	1

Shared Primary Key
Another way of modeling inheritance in a relational model is by constraining the child's primary key (PK) as a foreign key (FK) to the parent's PK.  This solution makes changing the structure or inheritance hierarchy simpler than the "discriminator" solution.  It also eliminates the unnecessary "NULL" values for inapplicable fields.

Parent:

id	parent_field
1	1
2	2
3	3

Child_1:

parent_id	child_1_field
2	1

Child_2:

parent_id	child_2_field
3	1