One table vs multiple tables

Question

I have the following tables: - posts - files - events - documents

Every post, file, event, document can have comments.

What is the better database scheme for this, and why?

First solution

• comments table (comment_id, author_id, comment)
• 4 tables to create relations (posts_comments(post_id, comment_id), files_comments(file_id, comment_id), events_comments(event_id, comment_id), documents_comments(document_id, comment_id))

Second solution

• comments table (comment_id, author_id, comment)
• items_comments (comment_id, parent_type (enum['post', 'file', 'event', 'document']), parent_id)

What is a better solution for this, or which one of two should I use?

Answer 1

For completeness, I should mention another possibility - inheritance (aka. generalization or (sub)category hierarchy):

Answer 2

There may be real reasons for wanting/needing a single comments table. For example, it would make it simpler to view all comments from a given user. Also, searches through all comments would be simpler (put one FTS index on the one table and you are done).

On the other hand, if there is not a compelling reason to keep the comments in a single table, there is a possible third (and rather obvious) solution.

Create a separate comments table for each item (post, event, file, document). The RI relationships would be very simple to define and describe in that situation. Also, if you are typing ad-hoc queries very often, it could make it simpler. For example

 select * from documents d left join doc_comments c 
                           on d.id = c.docid 
                           where d.id=42;

None of this may be pertinent or important to your situation, but it could be worth considering.

One additional random thought: Both solutions in the OP have the "feel" that they are defining a many-to-many relationship (e.g., a comment can belong to multiple items). Assuming that is not the desired situation, it can be prevented with the appropriate unique index, ... but still ... it has that initial appearance, which seems as if it could lead to possible confusion.

Answer 3

I would prefer the first solution. The Sql-Statements are simpler there. And in my opinion it's more conform with database normalization