Tag, comment, rating, etc. database design

Question

I want to implement modules such as comment, rating, tag, etc. to my entities. What I thought was:

comments_table -> comment_id, comment_text

entity1 -> entitity1_id, entity1_text

entity2 -> entitity2_id, entity2_text

entity1_comments -> entity1_id, comment_id

entity2_comments -> entity2_id, comment_id

....

Is this approach correct?

Answer 1

It is simpler than that. You are going to want something like this:

Entity: EntityID EntityText

Comments: CommentID AssocEntityID CommentText

Where AssocEntityID has a foreign key relation to the Entity table EntityID column.

For this solution, to get all comments for Entity with ID 1, you would do this:

SELECT CommentID, CommentText FROM Comments WHERE AssocEntityID = 1

Answer 2

No, I would suggest having just one entity_comments table that is an intersect table between comments and entity. You would have to have the entity1 and entity2 ids in the one comments table as separate attributes.

so it would look like:

entitiy1 -> entity_comments <- comments_table
entitiy2 -> entity_comments <- comments_table

a simple select might be:

select text
  from entity1
     , entity_comments
     , comments_table
  where entity1.id = entity_comments.entity1_id
    and entity_comments.comment_id = comments_table.id