model an abstract base class and subclasses in a database

Question

I have 4 subclasses: Video, Image, Note, and Form. Each one contains different types of data. For example, the Image class contains a path to the image file on disk and image properties, and the Form class contains the form field values. The common element between each item, however, is the GPS coordinates and heading, so I have the following abstract base class:

public abstract class Content
{
    public float? Latitude { get; set; }
    public float? Longitude { get; set; }
    public float? Heading { get; set; }
}

However, where I get hazy is on how to model this in a database. Currently I have a table called Events (for the sake of example, let's say an event is a birthday party) and 4 tables (Videos, Images, Notes, and Forms). Each table has a foreign key linking back to Events' primary key.

Using LINQ-to-SQL, I get 5 classes for each table. This is fine if I only want one type of data, for example Event.Images, but what I want to do is count the total number of 'contents' an Event has and get the GPS coordinates. I can wing the count easy enough by just using Event.Images.Count() + Event.Videos.Count() + ..., but I can't do the same for the GPS coordinates. Is there some way I can model the database so that I can use a base class for every item and still be able to get the individual strongly-typed item when I need to see its data?

Answer 1

There are three different patterns for this documented in Martin Fowler's Patterns of Enterprise Application Architecture, each with different trade-offs:

• Single Table Inheritance
• Class Table Inheritance
• Concrete Table Inheritance

Answer 2

That is one way to do it.

Generally speaking, there are two other ways:

• table per sub-class: instead of trying to share the baseclass in the database, just create separate tables for each one.

• table per hierarchy: put a superset of all the fields into a table, as well as a "discriminator" column, and store them in one place.

To figure out which is right, think about the usage patterns:

• if you generally treat them independently, and query independently, separate tables are a good way to go-- either what you have or table per subclass.
• if these are treated as heterogeneous collections, so you want to look at them as such, a single table can be easier

Also see what your tool supports most naturally. They all work.