views:

36

answers:

2

Hi, I want to store comment replies in database table. I have a table to store comments:

comment_id comment_par_id, comment_from comment_text comment date ....

New comment has par_id=0 while the replies has par_id set to comment id to which it was replied.

The nesting is just one level. Reply to a reply also has the same parent id.

Is this the best way to store the replies?

I looked few articles that recommends to create a separate table to store the replies. Then have a mapping column to point the comment in the main table.

Another alternate is to create a third table that stores the mapping like:

reply_id comment_id

Which is the best way?

No matter what, I only run a query to return the replies for a given comment. And it is the most running query and must run fast as we have millions of rows in the comment table.

A: 

It's a one (comment) to many (replies) relationship, so you should use two tables, with the replies table foreign keyed to the comments table.

ThatSteveGuy
I want the replies of same comment to be stored sequentially.reply_id comment_id reply_text1 43 This is reply #12 43 This is reply #23 48 THIS IS REPLY #14 48 THIS IS REPLY #2
Lisa
Then use a composite key on the replies table, composed of the commentID and then the replyID
ThatSteveGuy
A: 

If I understand you right, you have an "original post" of some kind, with a set of replies? Similar to how StackOverflow works, with an initial question, with a set of answers? If that is the case, there are a few options. There is the option of using a single table that supports different "types" of records. This choice has the benefit of only requiring a single table, however it also has the drawback of more ambiguity. One has to know that multiple types of records are stored in such a table, making it more confusing.

A better alternative is to have multiple tables, for each "type" of record. This removes the ambiguity, while adding complexity. From a different perspective, different "types" of similar records often have different data, even if some of the data is the same. By using separate tables, it is easier to add distinct traits to each type of comment (original vs. reply), without having to resort to a variety of oddball ways of storing and referencing he extra "unique" data in a single-table system.

jrista
It is like Stackoverflow answers to questions
Lisa
I would use separate tables, related by foreign key. I would have a table for the original posts (questions), and a table for replies (answers). The answers would relate to the question via a ParentPostID (or QuestionID) foreign key. Its pretty standard database practice.
jrista