Best way to store and retrieve comment replies in sql server

Question

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.

Answer 1

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.

Answer 2

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.