Table "Inheritance" in SQL Server

Question

I am currently in the process of looking at a restructure our contact management database and I wanted to hear peoples opinions on solving the problem of a number of contact types having shared attributes.

Basically we have 6 contact types which include Person, Company and Position @ Company.

In the current structure all of these have an address however in the address table you must store their type in order to join to the contact.

This consistent requirement to join on contact type gets frustrating after a while.

Today I stumbled across a post discussing "Table Inheritance" (http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server).

Basically you have a parent table and a number of sub tables (in this case each contact type). From there you enforce integrity so that a sub table must have a master equivalent where it's type is defined.

The way I see it, by this method I would no longer need to store the type in tables like address, as the id is unique across all types.

I just wanted to know if anybody had any feelings on this method, whether it is a good way to go, or perhaps alternatives?

I'm using SQL Server 05 & 08 should that make any difference.

Thanks

Ed

Answer 1

I designed a database just like the link you provided suggests. The case was to store the data for many different technical reports. The number of report types is undefined and will probably grow to about 40 different types.

I created one master report table, that has an autoincrement primary key. That table contains all common information like customer, testsite, equipmentid, date etc.

Then I have one table for each report type that contains the spesific information relating to that report type. That table have the same primary key as the master and references the master as well.

My idea for splitting this into different tables with a 1:1 relation (which normally would be a no-no) was to avoid getting one single table with a huge number of columns, that gets very difficult to maintain as your constantly adding columns.

My design with table inheritance gave me segmented data and expandability without beeing difficult to maintain. The only thing I had to do was to write special a special save method to handle writing to two tables automatically. So far I'm very happy with the design and haven't really found any drawbacks, except for a little more complicated save method.