用于评论和回复的数据库设计 [英] Database design for comments and replies

问题描述

我想创建两个表，一个表带有评论，另一个表带有回复，它们之间是一对多的关系.但是，如果他们也可以答复答复该怎么办呢?这是我一对多的功能，但是我不知道如果也有要回复的内容，应该怎么看.

I want to create two tables one with comments and another with replies, with a one to many relationship between them. But what if they can also reply to replies, how should it be changed then? This is what I have for the one to many, but I don't know how it should look if there can also be replies for a reply.

    Comments:
•   Id
•   Title
•   Text

    Replies:
•   Id
•   Title
•   Text
•   Comment id

谢谢.

推荐答案

您可以只使用一个包含ParentID字段的表.如果记录没有值，则为注释，否则为答复(对注释或答复).

You could just use one table, which includes a ParentID field. If the record has no value, it is a comment, otherwise it is a reply (to either a comment or a reply).

您可以查询记录的ParentID记录(检查它是 ParentID)，以查看此回复是针对评论还是回复.

You could query the record's ParentID record (inspect it's ParentID) to see if this reply is to a comment or a reply.

编辑:以上是相当实用的解决方案.但是，要使用规范化版本，请仍然保留一个Comments表(没有ParentID)，并创建一个带有CommentID和ResponseID的ReplyTo表，这两个表都是Comments表中记录的ID.

Edit: The above is a fairly practical solution. However, to go with a normalised version, still keep the one Comments table (with no ParentID), and create a ReplyTo table which has a CommentID, and a ResponseID, both of which are the IDs of the records in the Comments table.

使用此想法，以下sql将显示注释以及对具有注释的每个答复的每个注释的答复":

Using this idea, the following sql will show the comments and the 'reply' to each comment for each reply that has a comment:

select c.comment, r.comment as reply
from comment as c, comment as r, replyto as rt
where c.ID = rt.CommentID
and r.ID = rt.ReplyID

正如Dimitrii所指出的，它不会显示没有回复的评论-为此，您需要一个外部联接查询(没有测试语法):

As Dimitrii points out, it won't display comments with no replies - for this you need an outer join query (didn't test syntax):

SELECT c.comment, r.comment as reply,
from Comment c 
  left outer join Comment r on c.id = r.id  
  left outer join replyto rt on rt.responseid = r.id

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