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?

Savior

Related question: #190796 – Elinaelinor

Answer 1

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 column. 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 all of the columns for all class properties be in one table. You'll probably need to leave most columns nullable since some columns won't apply to records of a different type. For example, the vehicle table might contain a column called wingspan that corresponds to the Airplane type. If you make this column NOT NULL then any instance of a Car inserted into the table will require a value for wingspan even though a value of NULL might make more sense. If you leave the column nullable you might be able to work around this with check constraints but it could get ugly. (Single Table Inheritance)

Snobbish

The query complexity was where I am concerned. It may require a bit of tricks on the DAL part to get it right. – Savior

For query complexity #3 sounds best. Also, in some DBMS (like Postgres) you can enforce non-nullness for each specific subtype through a check constraint, even though the columns would all be nullable. – Mota

I don't know if this is a option but you not use 1. and use views for accessing the data this way you only get the joins in one spot? – Arron

Answer 2

Be careful with database inheritance in certain situations - we implemented it in our application for our auditing strategy and we ended up with a performance bottleneck/nightmare.

The problem was that the base table we used was insert only and rapidly changing so what we ended up with were deadlocks all over the place. We are currently planning to break these apart into their own tables because the headache of having the same columns in 15 different tables versus a performance nightmare is well worth it. This was also compounded by the fact that the entity framework doesn't necessarily handle inheritance efficiently (this is a known issue by Microsoft).

Anyway, just thought I'd share some knowledge since we've been through the wringer on this issue.

Chiaroscuro

So you were doing #1 and are moving (have moved) to #2? (of cliff.meyers solutions) – Spoilage

Answer 3

Chapter 8. Inheritance Mapping in following link also discussed this. http://nhibernate.info/doc/nh/en/index.html#inheritance

It is the NHibernate document.

Cormophyte