问题描述
我想创建两个表,一个表带有评论,另一个表带有回复,它们之间是一对多的关系.但是,如果他们也可以答复答复该怎么办呢?这是我一对多的功能,但是我不知道如果也有要回复的内容,应该怎么看.
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
这篇关于用于评论和回复的数据库设计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!