网站评论系统
给定页面上元素的注释系统，我想避免重复表格，例如：
tblBlogComments
tblNewsComments
tblArticleComments
等等
设计这个的好方法是什么？一张评论表？如何将它们链接到博客中的单个整体？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
假设所有评论都相同（即所有评论都具有相同的最大长度和其他存储字段，例如“评论作者”），我会采用一种方法，即使用一个
Comment表，该表具有 < code>CommentTypeId 列来标识评论的类型。顺便说一句，我还会从实体名称的开头删除
tbl，因为它并不是真正必要的，至少在我看来 =)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
Commenttable that has aCommentTypeIdcolumn to identify what type of comment it is.As an aside, I'd also drop the
tblfrom the beginning of the entity names as it's not really necessary, at least IMO =)这种方法的两个问题是
a) 外键约束。没有简单的方法来指定
comment_type_id 是否为“Article”，然后检查该 Parent_id 是否存在于 Article 表中
如果comment_type_id是“Blog”，则检查Blog表中是否存在该parent_id
---等等..
我见过的大多数地方都会忽略外键约束，并在添加注释时在应用程序层（java，.net ..等等）中进行检查。这引入了进一步的复杂性，例如并发用户、事务和锁定。
b) 第二个问题是某些评论包含特定数据（仅与博客、文章或新闻相关）。即使有，请确保将其作为单独的列包含在内，并且不要包含“additional_column_1”等通用列。
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".
您的问题与这个数据库设计问题几乎相同，其中包括用于实现此类评论系统的SQL示例代码。您应该能够非常轻松地调整 SQL。
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.
当然，您可以使用单个表来实现这些功能，但是如果您可能想要为特定表添加您不希望为其他表添加的特定功能/字段，则必须非常仔细地考虑。
即你可以有
但是如果你想添加另一个字段仅用于新闻评论，即新闻图片，那么在让事情变得更容易和最终得到语义上不正确的表结构之间有一条微妙的界限
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
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
这取决于。单独的表是有原因的，例如不必包含类别或过滤，以及逻辑上分隔不应分组的条目（博客评论与新闻评论）。
如果您想要一个简单的评论表，您可以随时添加一个“类别”列，用于标记评论的位置/内容（博客、新闻、文章等）。如果您计划好您的唯一 ID，则可以只拥有“父文档”之类的列，并将该列指向评论发布到的文章/博客/等的 ID。
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.
您可以创建一个包含所有评论和某种类型的元素 ID 的评论表，该表链接回特定元素（博客文章、新闻项目、文章等）。
将对象类型添加到注释表中也可能是一个不错的选择，其中值将是注释链接到的元素的类型。通过这种方式，您可以创建一个视图来过滤站点不同部分中查询的表，因此您不需要每次都进行完整的表/索引扫描。
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.