Techniques for database inheritance?

Question

What are the tips/techniques when you need to persist classes with inheritance to relational database that doesn't support inheritance?

Say I have this classic example:

Person -> Employee -> Manager
                   -> Team lead
                   -> Developer
       -> Customer -> PrivilegedCustomer
                   -> EnterpriseCustomer

What are the available techniques to design the database? Pros and cons of each?

p.s. I have searched and found several question regarding database inheritance but most were about changing to a database engine that supports it natively. But let's say I'm stuck with SQL Server 2005... what are my options?

Answer 1

Hello,

Check this out:

http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

HTH

Colby Africa

Answer 2

Three common strategies:

1. Create a table for each class in the hierarchy that contain the properties defined for each class and a foreign key back to the top-level superclass table. So you might have a "vehicle" table with other tables like "car" and "airplane" that have a "vehicle_id" table. The disadvantage here is that you may need to perform a lot of joins just to get one class type out.

2. Create a table for each class in the hierarchy that contains all properties. This one can get tricky since it's not easy to maintain a common ID across all the tables unless you're using something like a sequence. A query for a superclass type would require unions against all the tables in question.

3. Create one table for the entire class hierarchy. This eliminates joins and unions but requires that you have all properties in that table. None of the columns should be nullable since you should be able to null out columns that don't apply to a particular class (although you could relax these constraints if you want).

Answer 3

Chapter 8. Inheritance Mapping in following link also discussed this. http://nhforge.org/doc/nh/en/index.html#architecture-overview

It is the NHibernate document.