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?