The best relationship between one table and some other tables, each dependent on n datasets of the first table?
I have one table that stores comments for a diverse set of content types. They are stored in other tables (news, articles, users). Wondering what is the best way to connect these tables? In previous projects, I have used a second table for each type of content. They contained the ID of the specific content mapped to the IDs of the comment table. So for each comment I had a comment entry itself and a "connector" entry. An alternative would be to use a separate comment table for any content. In the end, both paths contain some lack of redundancy.
So which one should I use or is there a ONE solution?
source to share
There are several obvious ways to design a table:
1) You can have a table of main comments and intermediate tables to connect each comment to your articles, articles, news and users:
Comments
--------
ID
News NewsComments
---- ------------
ID NewsID
CommentID
Articles ArticleComments
-------- ---------------
ID ArticleID
CommentID
Users UserComments
----- ------------
ID UserID
CommentID
This has the advantage of being relatively easy to request comments for each function. However, this style suffers from referential integrity: it can associate a single comment with multiple comments, news, and users. Also, it's not very scalable: if you add comments to rss feeds, featured links, user status, etc., then you have staging tables for all of these 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 functionality, but you cannot use the Comments.RefID field on multiple tables at the same time, so you lose referential integrity.
3) The final option requires many redundant "comment" tables for each function.
News NewsComments ---- ------------ ID NewsID Articles ArticleComments -------- --------------- ID ArticleID Users UserComments ----- ------------ ID UserID
The advantage of this style is that it maintains referential integrity and lends itself easily to anyone looking at your diagram. The disadvantage is too many tables with the same schema.
source to share
I would probably have a separate comment table for each content table so that I can use foreign key relationships to automatically manage updates / deletes when the original content changes / deletes. So if I had news and article tables, then I would have news_comments and article_comments tables. Each of them will have a content_id field and a user_id field. The content_id field will link to the content table and the user_id field will link to the users table. I have established a foreign key relationship between the corresponding content and comments table and the users and each comment table and these changes are propagated on update / delete. You can also set up indexes on id fields so that you do indexes between them.
If you have a table with a single comment, you will need to manage the relationship manually in code rather than accessing it from the DB, since you can only link the FK to one other table.
source to share
Another way could be as follows:
Comments
-------
ID
NewsID
ArticleID
UserID
For each line, only one will be nonzero. This will simplify queries than with staging tables.
From a data integrity perspective, it would make more sense to have one comment table for each table of contents.
source to share