If you are familiar with any object oriented programming language, the term inheritance may not be new to you. In OO programming such as in Java or C++ you basically put all the common attributes and behavior in a parent class and then you provide specific attribute and behavior implementation in each subclass. This feature is also supported by all major DBMS; it is called subtyping.

Some examples of subtyping are as follows:

  • A School can be broken down into: regular school, charter school, administrative school, etc…
  • A person can be broken down into: man or woman.
  • A customer can be broken down into: an internal customer or an external customer.
  • A product can be broken down into several products, books, music, movies.
  • A book can be of different types: Hard Cover, PDF format, kindle format etc…

Let’s take a look at it in action:

Supertypes and Subtypes

Let’s depict the diagram above:

Book Entity

  1. Is the Parent entity, aka Supertype
  2. The Supertype is at the one and only one side of the relationship.
  3. Has all the information that is common among all of its subtypes.
  4. It’s PK is going to be the PK of the Subtype as well so that we can get to the details and/or generic information back and forth.

Paperback Book and Digital Book Entities

  1. Are the Child entities, aka Subtypes.
  2. The Subtypes are at the zero or one side of the relationship.
  3. They contain information specific to them only.
  4. Their PK is the Parent entity primary key so that you can easily get to the common information.

Alert: Some people put a char column in the parent entity to be able to determine the subtype of that record without having to look at the subtype. Be very careful with this approach because if a parent entity can be of both types the solution would not be consistent; if you do that make sure that the subtypes are mutually exclusive.