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?

I prefer the first solution, be flexible so you have to change the name file to files by editing 1 name (if you want to), not all in the items_comments table you have in the second solution. Als try to understand database normalization http://en.wikipedia.org/wiki/Database_normalization — Ron van der Heijden, May 15 '12 at 14:19

score 2 · Accepted Answer · answered May 15 '12 at 16:27

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.

score 1 · Answer 2 · answered May 15 '12 at 14:16

1

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

answered May 15 '12 at 14:16

Tomtom

9,087
7
52
95

score 1 · Answer 3 · answered May 15 '12 at 19:52

1

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

enter image description here

answered May 15 '12 at 19:52

Branko Dimitrijevic

50,809
10
93
167

One table vs multiple tables

3 Answers3

Linked