Best relationships between one table and some other tables, each of them depending on n datasets of the first table?

Question

I have one table that saves comments for a varied set of content types. These are saved in other tables (news, articles, users). I wonder what's the best way to connect these tables? In previous projects I used a second table for each kind of content. They held the id of the certain content mapped to ids of the comments table. So for each comment I had the comment entry itself and a 'connector' entry. An alternative would be to use a separate comments table for any kind of content. At the end both ways contain some redundancy flaw.

So which one should I use or is there the ONE solution?

Answer 1

I would probably have a separate comments table for each content table so that I could take advantage of the foreign key relationships to automatically manage updates/deletes when the original content is changed/deleted. So if I had news and articles tables, then I would have news_comments and article_comments tables. Each of these would have a content_id field and a user_id field. The content_id field would refer back to the content table and user_id field would refer back to the users table. I'd set up foreign key relationships between the appropriate content and comment table and the users and each comment table and have the changes propagate on update/delete. You might also want to set up indices on the id fields so that you are doing index joins between them.

If you have a single comments table, you would have to manage the relationships manually in code rather than having the DB handle it for you -- since you can only have the FK relate to one other table.

Answer 2

Your question seems to be similar to

How do I relate one table to many different tables?

Answer 3

There are a few obvious ways to design your table:

1) You can have a master Comments table, and intermediate tables to connect each comment to your Articles, News, and Users tables:

Comments
--------
ID

News      NewsComments
----      ------------
ID        NewsID
          CommentID

Articles  ArticleComments
--------  ---------------
ID        ArticleID
          CommentID

Users     UserComments
-----     ------------
ID        UserID
          CommentID

Advantage of this is the relative ease of querying comments for each feature. However, this style suffers from referential integrity: its possible to connect a single comment to multiple comments, news articles, and users. Additionally, its not very scalable: if you add comments for RSS feeds, favorite links, user status, etc, then you have intermediate tables for all of those types.

2) Another approach is a slightly denormalized version:

Comments
--------
ID
TableName
PkID (Connects the primary key in other tables)

News
----
ID  

Articles
--------
ID

Users
-----
ID

This works and it scales easily whenever you add new features, but you can't key your Comments.RefID field to multiple tables simultaneously, so you lose referential integrity.

3) The final option requires lots of redundant "comments" tables for each feature.

News      NewsComments
----      ------------
ID        NewsID

Articles  ArticleComments
--------  ---------------
ID        ArticleID

Users     UserComments
-----     ------------
ID        UserID

Advantage of this style is the preservation of referential integrity, and that its readily understanable to anyone looking at your schema. Disadvantage is the excessive number of tables with an identical schema.

Answer 4

Yet another way might be this:

Comments
-------
ID
NewsID
ArticleID
UserID

For each row only one would be non-zero. This would make queries simpler than with the intermediate tables.

From the standpoint of data integrity however it would be wiser to have one Comments table for each Contents table.