Table "Inheritance" in SQL Server

Question

Hi,

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

You'll still have the problem that if you want the sub-type fields and you have only the master contact, you'll have to know what table to go looking at - or else join to all of them. But otherwise this is a workable solution to a common problem.

Another possibility (fairly similar in structure, but different in how you think of it) is to simply put all your contacts into one table. Then for the more specific fields (birthday say for people and department for position@company) create separate tables that are associated with that contact.

    Contact Table
    --------------
    Name
    Phone Number

    Address Table
    -------------
    Street / state, etc
    ContactId

    ContactBirthday Table
    --------------
    Birthday
    ContactId

    Departments Table
    -----------------
    Department
    ContactId

It requires a different way of thinking of things though - instead of thinking of people vs. companies, you think of the various functional requirements for the task at hand - if you want to send out birthday cards, get all the contacts that have birthdays associated with them, etc..

Answer 2

I know this won't help much now, but initially it may have been better to have an Entity table rather than 6 different contact types. Then each Entity could have as many addresses as necessary and there would be no need for type in the join.

Answer 3

I'm going to go out on a limb here and suggest you should rethink your normalization strategy (as you seem to be lucky enough to be able to rethink your schema quite fundamentally). If you typically store an address for each contact, then your contact table should have the address fields in it. Alternatively if the address is stored per company then the address should be stored in the company table and your contacts linked to that company.

If your contacts only have one address, or one (or even 3, just not 'many') instance of the other fields, think about rationalizing them into a single table. In my experience having a few null fields is a far better alternative than needing left joins to data you aren't sure exists.

Fortunately for anyone who vehemently disagrees with me you did ask for opinions! :) IMHO you should only normalize when you really need to. Where you are rethinking schemas, denormalization should be considered at every opportunity.

Answer 4

When you have a 7th type, you'll have to create another table.

Answer 5

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.

Answer 6

Google on "gen-spec relational modeling". You'll find a lot of articles discussing exactly this pattern. Some of them focus on table design, while others focus on an object oriented approach.

Table inheritance pops up in a few of them.

Answer 7

I'm going to try this approach. Yes, you have to create new tables when you have a new type, but since this table will probably have different columns, you'll end up doing this anyway if you don't use this scheme.

If the tables that inherit the master don't differentiate much from one another, I'd recommend you try another approach.