Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Implementing Table Inheritance with SQL Server

I have a new article up at SQLTeam:

Implementing Table Inheritance with Sql Server

It discusses the situation where you have multiple entities that are distinct, yet they have many attributes or relations in common.   There is an easy way to simplify your database design and your code if you use the concept of a "base table" for that common data, which is very similar to the concept of Inheritance in Object-Oriented programming.

I recently had to do this for a client that tracks Contributions to their foundation; there is a base set of data that all Contributions have, such as who donated, how they paid, the date a tax letter was sent, etc. But for certain contribution types we need additional information, such as if they bought an Auction Item, sponsored an Event, and so on. Thus, we set up a "base table" of Contributions and sub-tables for the different types, and set up a constraint to ensure that the right subtable is used depending on the type. 

It sounds complicated and like it may require fancy triggers or at the very least CHECK constraints, but it is very easy to model in T-SQL – especially in SQL Server 2005+.  The end result is that you have a less redundant database design and much less code to write to maintain that data. 

Check it out if it sounds interesting, or helpful, or if none of this makes any sense and you're wondering what the heck I'm talking about.

Legacy Comments

Jeff Maass
re: Implementing Table Inheritance with SQL Server

re: Implementing Table Inheritance with SQL Server
We're thinking of implementing this design on a new project. Have you ran into any gotchyas with this approach? Have you found that the approach requries any work-arounds you wouldn't be required if you broke all the tables out separately?

re: Implementing Table Inheritance with SQL Server
Hi Jeffro --

None really that I have encountered so far. The pros far outweigh the cons.

re: Implementing Table Inheritance with SQL Server
You say the "The pros far outweigh the cons." So what are the cons?

I also wanted to point out a problem that occurs in the way people create examples. In your modeling you wanted to show that you had an idea for inheritance, you give the idea that a student is a person and a parent is a person and a teacher is a person instead of the idea that a person may have the role of a student, a parent or a teacher. Your example locks you into major code rewrite when you look at the real world. For instance, in a college the teacher may very well be a student. There may even be a parent that is a student taking classes with their children.

Overall it is a good idea, but role modeling may serve you better in this case. It changes the structure of what you are doing only slightly and allows for growth in the future when your system requirements change.

re: Implementing Table Inheritance with SQL Server
Hi! I am currently working on my third year project. My project team and I are having the problem that you are addressing here. I am rather excited and hope that this will solve our problem. Will let you know though.

Jignesh Patel
re: Implementing Table Inheritance with SQL Server
Very good article !