Database design for comments and replies

Question

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.

Answer 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).

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

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.

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

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