Designing Super Types of Tables

I want to talk a little about database design patterns. When working with a relational database, there are a couple of patterns that exist to help you normalize your data. I think one of the most useful patterns in this is the supertype-subtype relationship.

You don’t see a supertype-subtype relationship defined as such when you’re looking at the physical database. You’ll only see it explicitly in the logical data model. So what is the pattern and how do you know that you have one in your database?

This relationship exists where you have one entity that could have different attributes based on a discriminator type. One example is a person. Depending on the role of that person in relationship to the business, you will need to store different pieces of information for them. You need different information about a client than you do an employee. But you’re dealing with a person so there is shared information.

If you tried to put everything into one table, it would look something like this:

Because a person may not be a client or may not be an employee, you have to leave all of the fields NULL. If any of the columns have to have data depending on the role, the database would be unable to help enforce those requirements.

The next way you could do this would be create two separate tables such as this:

Now we can start saying which fields are mandatory for each different type of person. But now we get into the next issue – repeating data. The fields for First Name, Last Name and Middle Name are common between them, because they are all about the person. This is where the supertype-subtype comes into play:

Now we have a structure that consolidates all of the person information together but allows for each person to have role specific information grouped together. This may be an oversimplified version of a solution but you can see how this can help your table structure.

It’s a fairly simple but elegant solution to a common problem. You may already have this in place but not realize that there’s a specific name to it. What I like is that it allows you to create a vertical partition on a table so you can group related columns together and manage those sets of columns together. There are a lot of different situations where you can use this pattern beyond just people and roles – think addresses, sports, vehicles, etc. It is why this is one of my favorite types of table design.

Any favorite table designs of yours that you’d like to discuss?

 

Advertisement