Given a comments system for elements on my pages, I want to avoid repeating tables, like:
tblBlogComments
tblNewsComments
tblArticleComments
etc etc
What's a good way to design this? One comments table? How do I link those to single entires in the blog?
Sure you could use a single table for these, but you have to consider very carefully if you might ever want to add specific functionality / fields for a particular table which you don't want for the others.
i.e. you could have
TABLE Comments (commentid, userid, blogid, newsid, articleid, commenttitle, comment)
But if you then want to add another field for News comments only i.e. NewsPicture then there's a fine line between making things easier for you and ending up with a table structure which is semantically incorrect
You can create a comments table with all comments and some type of element ID, which links back to the specific element (blog post, news item, article, etc.).
It might also be good to add an object type to the comments table, where values will be the type of element the comment was linked to. This way you can create a view to filter the table for queries in the different sections of the site, so you don`t need to do a full table/index scan each time.
It depends. There are reasons for separate tables such as not having to include categories or filtering, and logically separating entries that should not be grouped (blog comments vs news comments).
If you wanted to have one simple comments table though, you can always add a 'Category' column for labeling the comment with where/what it is a comment for (Blog, News, Article, etc). If you plan your unique IDs out, you could just have a column like 'Parent Document' and have that point to the ID of the article/blog/etc the comment is posted to.
Assuming that all comments are the same (i.e. all have the same maximum length and other stored fields such as "comment author"), I'd go for the approach of having one Comment table that has a CommentTypeId column to identify what type of comment it is.
As an aside, I'd also drop the tbl from the beginning of the entity names as it's not really necessary, at least IMO =)
The two problems with this approach would be
a) The foreign key constraint. There is no simple way to specify
if the comment_type_id is "Article" then check if this parent_id exists in Article table if the comment_type_id is "Blog" then check if this parent_id exists in Blog table --- and so on..
Most places I have seen ignore the foreign key constraint and do the check in the application layer (java, .net.. whatever) when a comment is added. This introduces further complexities like concurrent users and transactions and locking.
b) The second issue is when some of the comments have specific data (related only to a blog, article, or news). Even if there is one, make sure you include it as a separate column and not having generic columns like "additional_column_1".
Your question is almost identical to this database design problem, which includes SQL sample code for implementing such a comment system. You should be able to adapt the SQL pretty easily.
