How to implement a nested comment system?

Question

What would be the ideal way to implement this sort of thing? The idea I have in my head right now is to have a comments table and have each comment have a thread identifier and parent comment identifier. The thread identifier would indicate to which thread the comment belongs to and would allow for a simple MySQL statement using the WHERE clause. Each comment would have an auto_increment identifier as per usual database design and the parent identifier column would indicate which comment this comment is a child of.

This type of design would put most of the stress on the PHP aspect of things because it would only be one SQL call to get all comments from a thread. Another implementation I found was having an SQL query for each nesting level. This solution would place the stress on the SQL sides of things.

How would SO implement this? Currently I'm at a loss because I am not sure which solution is the "best" solution and I am still quite new to database design, PHP, and JQuery.

Thanks.

Answer 1

Look at Managing Hierarchical Data in MySQL, specifically the section called "Nested Set Model". You may have to read through it a few times before it makes sense (I did) but it's worth it. It's a very powerful way to work with nested data and retrieve the parts you want with only one query.

On the downside, for updates you have to do a lot more work.