Proper Data Structure for Commentable Comments

Posted by Wesley on Programmers See other posts from Programmers or by Wesley
Published on 2012-08-28T19:50:19Z Indexed on 2012/08/28 21:51 UTC
Read the original article Hit count: 382

Filed under:
|
|
|

Been struggling with this on an architectural level.

I have an object which can be commented on, let's call it a Post. Every post has a unique ID.

Now I want to comment on that Post, and I can use ID as a foreign key, and each PostComment has an ItemID field which correlates to the Post. Since each Post has a unique ID, it is very easy to assign "Top Level" comments.

When I comment on a comment however, I feel like I now need a PostCommentComment, which attaches to the ID of the PostComment. Since ID's are assigned sequentially, I can no longer simply use ItemID to differentiate where in the tree the comment is assigned. I.E. both a Post and a Post Comment might have an ID of '5', so my foreign key relationship is invalid.

This seems like it could go on infinitely, with PostCommentCommentComment's etc...

What's the best way to solve this? Should I have a field in the comment called "IsPostComment" or something of the like to know which collection to attach the ID to? This strikes me as the best solution I've seen so far, but now I feel like I need to make recursive DataBase calls which start to get expensive.

Meaning, I get a Post and get all PostComments where ItemID == Post.ID && where IsPostComment == true

Then I take that as a collection, gather all the ID's of the PostComments, and do another search where ItemID == PostComment[all].ID && where IsPostComment == false, then repeat infinitely.

This means I make a call for every layer, and if I'm calling 100 Posts, I might make 1000 DB calls to get 10 layers of comments each.

What is the right way to do this?

© Programmers or respective owner

Related posts about c#

Related posts about sql