Implementing Subclassing in the Database

Question

I'm developing an application that will have a parent class which will have many basic fields and methods, and several subclasses with additional fields methods.

Curious how best to implement in the database. Does it make sense to have one table for the parent class, a separate table for the subclass and link them via id fields, or is it better to duplicate the parent class fields in the subclass tables for ease of access and simpler querying?

Answer 1

Three usual ways

1. Table per hierarchy -- one table for everything, lots of NULL columns.

2. Table per concrete class; each sub-class gets a table with all common fields repeated.

3. Table per type (class). This is as in supertype/subtype approach in relational design.

Answer 2

The answer to your question is yes, those are both valid approaches. It depends on things like how many joins you want done when you query something, and whether you want polymorphic queries to be supported. Here's an article on the subject by Scott Ambler. Section 2.6 in the article has a nice table that lists advantages and disadvantages of different mapping approaches.

Answer 3

you're basically talking about the object relational impedence mismtach. http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch

The simplest answer is that you model you data differently when it is in a relational database than when it is in object form.