Business domain description
Some of the entity types of interest with respect to the relevant business domain are money, user and progress.
The users may need to post comments about money and progress.
In the future, maybe there will be more entity types related to comment, not only money and progress.
I have three solutions:
commentstable for all, keeps the "commented entity id" and the "type of entity" of relevance.
CREATE TABLE comment ( user_id …, -- the identifier of who leaves comment text …, -- text of the comment created_at …, -- tells when comment has been left subject_type …, -- 'money' or 'progress' subject_id … -- identifier from 'money' or 'progress', depends on the 'subject_type' value );
commenttable for all. Each table retains a foreign key in the
comment_idcolumn. The table has a "complex" primary key
(id, created_at), and the
idvalues are not unique.
CREATE TABLE comment ( id…, -- not unique created_at…, -- indicates when the comment was left user_id…, -- identifier of who leaves comment text…, -- the text of the comment CONSTRAINT comment_pk PRIMARY KEY (id, created_at) -- "complex" primary key );
When I need to get all comments associated with a given entity instance, I obtain them by means of an operation like
SELECT * FROM comment WHERE id = comment_id.
Separate tables, e.g.
comment_job, ... All the tables will look the same:
CREATE TABLE comment_<entityName> ( id…, entity_id…, -- id of the commented entity user_id…, -- who leaves comment text…, -- text of the comment create_at… -- when the comment was left );
I prefer the third variant despite the fact that I have to create more tables, because the logic will be really simple and this approach is easy to manage and support.
I will pass the model of database (see "business domain description") to a teacher and I'm not sure how to justify my choice. I think that the teacher can say that my approach is bad because all my tables have the same columns and structure.
What would be good arguments?
Maybe there are more solutions?