Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
5  
Please don't crosspost. –  delnan Aug 28 '12 at 20:00

2 Answers 2

up vote 11 down vote accepted

All you need is a PostID for each thing that a user writes, and a ParentPostID that's nullable and points to a parent PostID. If it's null, it's a Post, and if it's not null, it's a comment. Comments can have comments.

Basic tree structure.

share|improve this answer

Here is a sample for SQL Server 2005+ based on Scott's answer above.

CREATE TABLE posts 
(
    PostID INT NOT NULL CONSTRAINT PK_Posts PRIMARY KEY IDENTITY(1,1)
    , ParentPostID INT NULL, PostText NVARCHAR(255)
);

INSERT INTO posts VALUES (NULL, 'This is top-level post #1');
INSERT INTO posts VALUES (NULL, 'This is top-level post #2');
INSERT INTO posts VALUES (1, 'This is a comment on post #1');
INSERT INTO posts VALUES (1, 'This is also a comment on post #1');
INSERT INTO posts VALUES (3, 'This is a sub-comment on comment #1');
INSERT INTO posts VALUES (2, 'This is a comment on post #2');
INSERT INTO posts VALUES (2, 'This is a comment on post #2');

WITH Comments(ParentID, PostText)
AS 
(
    SELECT ParentPostID, posts.PostText 
FROM posts 
WHERE ParentPostID IS NOT NULL
    UNION ALL
    SELECT ParentPostID, posts.PostText 
FROM posts 
    INNER JOIN Comments c ON posts.PostID = c.ParentID 
)
SELECT p1.PostText AS Post, Comments.PostText AS Comment
FROM Comments 
LEFT JOIN posts p1 ON Comments.ParentID = p1.PostID
WHERE ParentID IS NOT NULL;

Here is the result:

    Post                            Comment
    This is top-level post #1       This is a comment on post #1
    This is top-level post #1       This is also a comment on post #1
    This is a comment on post #1    This is a sub-comment on comment #1
    This is top-level post #2       This is a comment on post #2
    This is top-level post #2       This is a comment on post #2
    This is top-level post #1       This is a comment on post #1
share|improve this answer
1  
I love it when I get down-voted without an explanation, on what seems to me to be a perfectly logical answer. –  Max Vernon Sep 4 '12 at 20:48
1  
I guess it was because you did not end your statements with ;) –  ypercube Sep 4 '12 at 21:38
    
@ypercube lolololol yeah, I guess! –  Max Vernon Sep 4 '12 at 21:40
1  
I've corrected slight issues with the formatting and added a results table to show that my code does in fact answer the question quite nicely. –  Max Vernon Sep 5 '12 at 13:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.