For our CRM database, we have a table called 'action', which defines
'time spent by an employee doing something'. The intent is to extend
this table to encompass specific actions, such as time spent training
groups of clients, correspondance on behalf of a client... The trouble
is that simple inheritance probably won't work because of a need for
multiple inheritence. I am toying with the idea of composition, with
another table 'action_composite':
CREATE TABLE action_composite(
action_id INTEGER,
table_ref VARCHAR(20),
CONSTRAINT a_compos_pkey PRIMARY KEY(action_id, table_ref)
);
ALTER TABLE action_composite ADD CONSTRAINT action_id_fk
FOREIGN KEY(action_id) REFERENCES action(action_id) ON UPDATE CASCADE;
I'm not sure about table_ref, but it would either be a reference to
the table's id or a VARCHAR containing the table's name. In this way a
an action in the real world that consists of 'work on a client's
problem' and 'correspondance' can be represented. I am uneasy about
this solution - it feels slightly wrong. Can anyone help?
Thanks,
Tom