Organizing a comments table: How to justify the choice

database-designrdbmsscalabilityschema

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.

My considerations

I have three solutions:

  1. One comments table 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
);

  2. One comment table for all. Each table retains a foreign key in the comment_id column. The table has a "complex" primary key (id, created_at), and the id values 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.

  3. Separate tables, e.g. comment_money, 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.

Question

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?

Best Answer

This is not really an answer, but more of a set of reflections. However it is to long for a comment so I'll add it as an answer anyhow. These are the base tables if I get it right:

CREATE TABLE USER
( USER_ID ... NOT NULL PRIMARY KEY
, ... );

CREATE TABLE MONEY
( MONEY_ID ... NOT NULL PRIMARY KEY
, ... );

CREATE TABLE PROGRESS
( PROGRESS_ID ... NOT NULL PRIMARY KEY
, ... );

Since I don't know your business I just invented some identifier as a primary key. It may very well be a composite key, but for simplicity I do it like this.

Alternative 1:

CREATE TABLE comment 
( user_id ... not null references USER (user_id)
, text ...
, created_at ...
, subject_type ... -- 'money' or 'progress'
, subject_id ... -- identifier from 'money' or 'progress'
);

In all essential this is a union type for comments on either MONEY or PROGRESS. It is a construction that does not really fit well in a relational world. One major drawback is that it will not be possible to implement referential integrity (foreign key). I'm not sure what the primary key would be here.

Alternative 2:

I'm not sure I understand what you are saying here. Is MONEY dependent of the COMMENT? I.e.

CREATE TABLE comment (
    id…, -- not unique
    created_at ... not null
    user_id… not null references USER (user_id)
    text ... not null
    CONSTRAINT comment_pk PRIMARY KEY (id, created_at)
);

ALTER TABLE MONEY ADD CONSTRAINT ...
    FOREIGN KEY (MONEY_ID, created_at) -- add created_at in MONEY
    REFERENCES COMMENT (id, created_at);

and similar for PROGRESS. If this is what you mean then the COMMENT will have to exist before MONEY or PROGRESS and this seems counter intuitive to me.

Alternative 3

I would probably change the name to:

CREATE TABLE MONEY_COMMENT
( MONEY_ID ... REFERENCES MONEY (MONEY_ID)
, USER_ID ... REFERENCES USER (USER_ID)
, CREATED_AT ...
, ...
,    PRIMARY KEY (MONEY_ID, USER_ID, CREATED_AT) );

and a similar table PROGRESS_COMMENT. I share your thoughts that this is the most elegant solution. If you are interested in all COMMENTS by a USER you can create a view like:

CREATE VIEW COMMENTS AS 
    SELECT 'money' as COMMENT_TYPE
         , MONEY_ID AS COMMENT_ID
         , USER_ID
         , CREATED_AT 
         , ...
    FROM MONEY_COMMENT
    UNION ALL
    SELECT 'progress' as COMMENT_TYPE
         , PROGRESS_ID AS COMMENT_ID
         , USER_ID
         , CREATED_AT 
         , ...
    FROM PROGRESS_COMMENT
Related Question