许多表的数据库设计问题 [英] Comments on many tables database design issue

问题描述

我有表格：

Articles{...}
Recipes{...}
Notifications{...}
Photos{...}

实现用户评论功能（如Facebook）。
我应该使用1：n关系创建表格： ArticleComments，RecipesComments 等吗？
或者创建一个注释表（但我不知道如何设计这个）？

And I need to implement 'user comments' feature (like facebook). Should I make tables: ArticleComments, RecipesComments etc. with 1:n relationship? Or create one Comments table for all (but I have no idea how to design this)?

推荐答案

您可以创建另一个表 CommentableEntity （虽然称为更好的东西）。表中的每一行（ Articles ， Recipes 等）都会引用此行中的唯一行表。实体表可以具有类型字段以指示实体的类型（以辅助反向加入）。

You could create another table CommentableEntity (although call it something better). Each of the rows in your tables (Articles, Recipes etc.) would have a reference to a unique row in this table. The entity table might have a type field to indicate the type of entity (to aid reverse joining).

您可以有一个注释表，引用 CommentableEntity

You can then have a Comment table that references CommentableEntity, in a generic fashion.

例如，您最终会得到以下表格：

So for example you'll end up with the following tables:

Articles
-----------------
Article_id
CommentableEntity_id (fk, unique)
Content
....

Recipes
-----------------
Recipe_id
CommentableEntity_id (fk, unique)
Content
....

CommentableEntity
-----------------
CommentableEntity_id (pk)
EntityType (e.g. 'Recipe', 'Article')

Comment
-------
Comment_id (pk)
CommentableEntity_id (fk)
User_id (fk)
DateAdded
Comment 
...etc...

您可以在每次添加文章/食谱时添加CommentableEntity记录。您的所有注释处理代码都必须知道是CommentableEntity_id - 它不关心什么类型的事情。

You can add the CommentableEntity record every time you add an Article/Recipe etc. All your comment-handling code has to know is the CommentableEntity_id - it doesn't care what type of thing it is.

这篇关于许多表的数据库设计问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！