CODEWITHSUNDEEP
mysqldatabasedatabase-designone-to-many
grasshopper
grasshopper

Reputation: 1401

Database design for comments and replies

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.

Upvotes: 18

Views: 33225

Answers (2)

dnyaneshwar
dnyaneshwar

Reputation: 85

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.

enter image description here

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();

Upvotes: 1

mcalex
mcalex

Reputation: 6778

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

Upvotes: 30

Related Questions