SQL Schema to allow users add comments to various tables

Question

So, I'm building a website and I'm going to have standard CMS tables like Article, Blog, Poll, etc. I want to let users post comments to any of these items. So my question is, do I need to create separate comments tables for each (e.g. ArticleComment, BlogComment, PollComment), or can I just make a generic Comment table that could be used with any table? What has worked for people?

Method 1: Many Comment Tables

• Article {ArticleID [PK], Title, FriendlyUrl}
• ArticleComment {ArticleCommendID [PK], ArticleID [FK], Comment}
• Blog {BlogID, Title, PubDate, Category}
• BlogComment {BlogCommendID [PK], BlogID [FK], Comment}
• Poll {PollID, Title, IsClosed}
• PollComment {PollCommentID [PK], PollID [FK], Comment}

Method 2: Single Comment Table

• Article {ArticleID [PK], Title, FriendlyUrl}
• Blog {BlogID, Title, PubDate, Category}
• Poll {PollID, Title, IsClosed}
• Comment {CommentID [PK], ReferenceID [FK], Comment}

Answer 1

I'd go with the generic comment table. It will make a lot of things much simpler. I'd also tag comments with the ID of the user who created them, or other source-identifying information (IP address, etc.). Even if you don't display this it can be very handy when you have to clean up spam, etc.

Answer 2

I'd suggest just one comment table, adding an ItemID field telling which type of item is the comment for:

Article {ArticleID [PK], Title, FriendlyUrl}
Blog {BlogID, Title, PubDate, Category}
Poll {PollID, Title, IsClosed}
Comment {CommentID [PK], ReferenceID [FK], ItemID, Comment}
Item {ItemID, Type}

The last table would contain records such as (1, 'article'), (2, 'blog'), etc.

That way you'll be able to identify which content type each comment was made for.

Answer 3

I am working on a system where we used the following model for comments:

  Data Table(s)      Many-to-many Assoc             Comment Table
  CommentableId  ->  CommentableId/CommentId   ->   Comment_Id

Not my design, but I like the fexibility. It allows us to use one comment in many different places. Since this is not trivial to implement in the UI, users don't get to see this feature (just a text box to type in a comment), but it is used when we do batch imports and legacy data processing in the database.

Answer 4

There seem to be two major ways of mapping OO-inheritance to relational databases:

1. Take all the attributes from the parent class and all the child classes and put them in the table, together with a 'which class is this?' field. Each object is serialized as one row in one table.

2. Create one table for the parent class and one table for each child class. The table for the parent class table contains the 'which class is this?' field. The child class table contains a foreign key pointing to the parent class table. Each object is serialized as one row in the parent class table and one row in the child class table.

Method one doesn't really scale well: it quickly winds up with lots of nullable fields, almost always null, and scary CHECK constraints. But it is fairly simple for small class hierarchies.

Method two scales much better, but is more work. It also results in many more tables in your schema.

I suggest taking a look at method two for your Articles/Polls/Blogs tables — to me, they sound like child tables of a Content or something. You will then have a very clear and easy place to attach comments: to the Content.

Answer 5

Why do you want to keep all of your comments in the same table? Will you be treating all comments as a group? If you don't anticipate working with all of the comments on all items as a single group then there isn't really a reason to bunch them all together. Just because two entities in a database share the same attributes doesn't mean that they should be put in the same physical table.