One table versus multiple tables

Question

I have the following tables: - messages - files - Events - documents

Each message, file, event, document may have comments.

What is the best database schema for this, and why ?

First decision

comment table (comment_id, author_id, comment)
4 tables for creating relationships (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

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

What is the best solution for this or which of the two should I use?

+3

Tamás pap 15 '12 14:09

3

. Sql. , ,

+1

Tomtom 15 '12 14:16

For completeness, I must mention another possibility - inheritance (aka. Generalization or hierarchy of categories):

+1

Branko dimitrijevic May 15, '12 at 19:52

Mark Wilkins · Accepted Answer · 2012-05-15T16:27:34+0000

/ . , . , ( FTS , ).

, , ( ) .

(, , , ). RI . , , .

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

, .

. OP "", " " (, ). , , ... ... , , , .