Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




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.

Print | posted on Thursday, February 21, 2008 11:05 AM | Filed Under [ SQL Server 2005 Database Design Links ]



# re: Implementing Table Inheritance with SQL Server

2/21/2008 12:11 PM | Jeff Maass

# 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?
2/27/2008 5:04 PM | Jeffro

# re: Implementing Table Inheritance with SQL Server

Hi Jeffro --

None really that I have encountered so far. The pros far outweigh the cons.
2/28/2008 11:18 AM | Jeff

# 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.
12/17/2008 10:14 AM | Ed

# 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.
5/21/2009 9:12 AM | Melissa

# re: Implementing Table Inheritance with SQL Server

Very good article !

2/8/2010 6:13 AM | Jignesh Patel
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET