用于评论和回复的数据库设计

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

Thanks in advance.

提前致谢。

1 个解决方案

#1

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字段。如果记录没有值，则为注释，否则为回复（对注释或回复）。

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

您可以查询记录的ParentID记录（检查它的ParentID）以查看此回复是对评论还是回复。

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.

编辑：以上是一个相当实用的解决方案。但是，要使用规范化版本，仍保留一个Comments表（没有ParentID），并创建一个ReplyTo表，其中包含CommentID和ResponseID，两者都是Comments表中记录的ID。

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

使用这个想法，以下sql将显示每条评论的评论和“回复”，每个评论都有一条评论：

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

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

正如Dimitrii指出的那样，它不会显示没有回复的注释 - 为此你需要一个外连接查询（没有测试语法）：

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

#1