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

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 2

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.