Designing SQL database to represent OO class hierarchy

Viewed 7809

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?

7 Answers
Related