Database schema design for posts, comments and replies

Question

In my previous project I had posts and comments as two tables:

post

• id
• text
• timestamp
• userid

comment

• id
• message
• timestamp
• userid
• postid

Now I've got to design replies to comments. The replies is just one level, so users can only reply to comments, not to replies. The tree structure is only 1 level deep. My first idea was to use the same comment table for both comments and replies. I added a new column though:

comment

• id
• message
• timestamp
• userid
• postid
• parentcommentid

Replies have parentcommentid set to the parent comment they belong. Parent comments don't have it (null)

Retrieving comments for a given post is simple:

but this time I need another query to find out the comment replies. This has to be done for each comment:

This doesn't seem to be a good solution, is there a way to have a single query which returns the complete list of comments/replies in the correct order? (dictated by the timestamp and the nesting)

Answer 1

You may use join and achieve result in single query like I provided below:

  SELECT *, cc.message as replied_message 
    FROM `post` 
    JOIN comment as c 
      ON c.postid = post.id 
    JOIN comment as cc 
      ON cc.id = c.parentcommentid 
ORDER BY c.timestamp DESC, cc.timestamp DESC;

Please note that, it works correctly only if 1 comment have 1 reply only.multiple replies on single comment will not support by this query

Answer 2

If you're using a database that supports JSON or object aggregation, you can get a nicer result from the query where each top-level comment is a row (and is not duplicated), and the replies are nested in an array/JSON within each row.

This gives you flexibility with what you do with it and also makes it easier to ensure the ordering and nesting is correct.

An example using Postgres:

SELECT
  p.id AS post_id,
  c.id AS comment_id,
  c.message,
  JSON_AGG(
    JSON_BUILD_OBJECT('comment', r.comment, 'timestamp', r.timestamp)
    ORDER BY r.timestamp
  ) AS child_comments
FROM
  post AS p
  INNER JOIN comment AS c
    ON c.post_id = p.id
  LEFT JOIN comment AS r
    ON r.parent_id = c.id
WHERE
  post.id = <some id>
  AND c.parent_id IS NULL
GROUP BY
  post.id,
  c.id,
  c.message
ORDER BY
  c.timestamp DESC
;

Note that, as above, this example will only retrieve the top-level and their first-level replies. It won't get replies to replies. You can use recursive commands or additional subqueries to do that.