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.

Downhaul

You design is normalized, you can should go ahead with it. You should have a userId which will be the creater's id in both the tables. Secondly Reply for a Reply is a logical Scenario, implementation of it is like You have a status on which people reply – Haricot

UserId? Where is the 'User' referenced? – Didactic

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

Didactic

I would agree with mcalex. Splitting it over 2 tables seems needlessly complex without any benefit. If there is a case to split it over multiple tables for normalisation reasons then have a table of threads, and then a table of comments (both the original comment and replies). However done normally it is going to require recursive calls to the database to get replies, then replies to replies, etc. Could be done using the nested set model. – Volkman

thanks alex, I think both solutions will work good, but in your experience which one do you think will provide better overall performance? – Downhaul

a big 'it depends'. Databases i work with aren't usually being worked to the limit of their abilities, hence I'm not so worried about having a column with null values. I'd suggest the normalised approach would provide better performance in a hammered database, but the 1 table approach would be easier to work with otherwise :) – Didactic

The above query doesn't work for comments without replies. – Mendelism

it works for me, and i added this in the end of query for sorting comments - ORDER BY c.created_at DESC, rt.created_at DESC; – Comedic

Answer 2

I have done the same for comment and comment replies. i have created same table and added parent_id column and managed with object association.

Below is the comment Entity:

public class Comment {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

@Column(name = "message") 
private String message;

@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "post_id")
private Post post;

@ManyToOne(cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST,
        CascadeType.REFRESH})
@JoinColumn(name ="user_id")
private User user;

@OneToMany(cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST,
        CascadeType.REFRESH})
@JoinColumn(name="parent_id")
private List<Comment> replies;

public Comment(String message) {
    this.message = message;
}

public void addCommentReplies(Comment comment) {
    if(replies == null) {
        replies = new ArrayList<>();
    }
    replies.add(comment);
}

}

---

Here is adding existing comment reference as parent_id and adding new commments :

session.beginTransaction();
        
        int postId=1; // today is my birthday
        Post post = session.get(Post.class, postId);
        //add likes to the post
        
        Comment parentComment = session.get(Comment.class, 1);
        
        Comment replie1 = new Comment("First replied to comment - 1");
        
        Comment replie2 = new Comment("second replies to the comment - 2");
        
        
        
        int userId = 3;
        User user = session.get(User.class, userId);
        replie1.setUser(user);
        replie2.setUser(user);
        replie1.setPost(post);
        replie2.setPost(post);
        parentComment.addCommentReplies(replie1);
        parentComment.addCommentReplies(replie2);
        
        session.save(replie1);
        session.save(replie2);
        
        
        System.out.println("comment added successful..");
        
        
        session.getTransaction().commit();

Camboose