Designing SQL database to represent OO class hierarchy

Question

I'm in the process of converting a class hierarchy to be stored in an SQL database.

Original pseudo code:

abstract class Note
{
   int id;
   string message;
};

class TimeNote : public Note
{
   time_t time;
};

class TimeRangeNote : public Note
{
   time_t begin;
   time_t end;
};

class EventNote : public Note
{
   int event_id;
};

// More classes deriving from Note excluded.

Currently I'm having a couple of ideas how to store this in a database.

A. Store all notes in a single wide table

The table would contain all information needed by all classes deriving from Note.

CREATE TABLE t_note(
   id INTEGER PRIMARY KEY,
   message TEXT,
   time DATETIME,
   begin DATETIME,
   end DATETIME,
   event_id INTEGER
);

Future classes deriving from Note need to add new columns to this table.

B. Map each class to a table

CREATE TABLE t_note(
   id INTEGER PRIMARY KEY,
   message TEXT
);

CREATE TABLE t_timenote(
   note_id INTEGER PRIMARY KEY REFERENCES t_note(id),
   time DATETIME
);

CREATE TABLE t_timerangenote(
   note_id INTEGER PRIMARY KEY REFERENCES t_note(id),
   begin DATETIME,
   end DATETIME
);

CREATE TABLE t_eventnote(
   note_id INTEGER PRIMARY KEY REFERENCES t_note(id),
   event_id INTEGER
);

Future classes deriving from Note need to create a new table.

C. Use database normalization and VARIANT/SQL_VARIANT

CREATE TABLE t_note(
   id INTEGER PRIMARY KEY,
   message TEXT
);

CREATE TABLE t_notedata(
   note_id INTEGER REFERENCES t_note(id),
   variable_id TEXT, -- or "variable_id INTEGER REFERENCES t_variable(id)".
                     -- where t_variable has information of each variable.
   value VARIANT
);

Future classes deriving from Note need to add new variable_id.

D. Map each concrete class to a table (newly added based on current answers)

CREATE TABLE t_timenote(
   id INTEGER PRIMARY KEY,
   message TEXT,
   time DATETIME
);

CREATE TABLE t_timerangenote(
   id INTEGER PRIMARY KEY,
   message TEXT,
   begin DATETIME,
   end DATETIME
);

CREATE TABLE t_eventnote(
   id INTEGER PRIMARY KEY,
   message TEXT,
   event_id INTEGER
);

Future classes deriving from Note need to create a new table.

---

What would be the most logical representation in SQL be?
Are there any better options?

Answer 1

In general I prefer obtion "B" (i.e. one table for base class and one table for each "concrete" subclass).

Of course this has a couple of drawbacks: first of all you have to join at least 2 tables whenever you have to read a full instance of a subclass. Also, the "base" table will be constantly accessed by anyone who has to operate on any kind of note.

But this is usually acceptable unless you have extreme cases (billions of rows, very quick response times required and so on).

There is a third possible option: map each subclass to a distinct table. This helps partitioning your objects but costs more in development effort, in general.

See this for a complete discussion.

(Regarding your "C" solution, using VARIANT: I can't comment on the merits/demerits, because it looks like a proprietary solution - what is it ? Transact-SQL? and I am not familiar with it).

Answer 2

Your 'B' option as described is pretty much an implementation of the 'Object Subclass Heirarchy' (Kung, 1990 http://portal.acm.org/citation.cfm?id=79213)

As such, it's a well established and understood method. It works quite well. It's also extensible through multiple levels of inheritance, should you need it.

Of course you lose some of the benefits of encapsulation and information hiding, if you don't restrict who can access the data theough the DBMS interface.

You can however access it from multiple systems, and even languages, simultaneously (e.g Java, C++, C#) (This was the subject of my Masters dissertation :)