Inheritance & Database Design

Question

When you model inheritance through class table inheritance relationships in a database model, do you...

1) Include an attribute (boolean for two subtipes, string for more subtipes) which identifies the particular subtipe of each record?

2) Include this identification as a foreign key to a table containing a description of all the possible subtypes?

3) None of the above and rely on "trial and error" lookup in subtables on the few times it is needed?

Answer 1

I would prefer in most cases the table per class hierarchy model, in which one column is a discriminator column (you 1. solution). If you really need the second table because of many subtypes that are needed, well perhaps you should break the tables into few tables, because I would suspect that much data will be reduntant, and null.

There is also a table per subclass model, in which you have a master table with shared data, and child tables for subclasses (data completly normalized) although working with this kind of data is harder, except when maybe using ORM tool like NHibernate.

Could you provide the concrete example of situation you are solving, because in these cases the number of subclasses is usually known up-front (like BillingDetails -> CreditCard, BankAccount etc...)