Threaded comment system nested set - multiple roots?

Question

Hi,

I'm implementing a threaded comment system for topics ie there is a topic and then threaded comments on each topic.

Nested set 'seems' like the popular way to go, but how should I implement the roots of each thread? For example:

• the comments could be one massive nested set. I may be wrong but it seems like it would be slower all as one tree.

• the comments could have one root thread for each topic. But then it would seem like I would have to make a blank root for the each topic, having blank roots just seems odd.

• each first level comment could be a root. This eliminates the blank root but seems like there would be a ton of root threads and say to render a page with 50 first level commments would I have to do 50 queries :S.

Am I missing something here, is there a better way to do this? I'm leaning towards blank root but it doesn't quite seem right

Thanks.

Answer 1

I think you'd usually have a separate foreign key for ‘owner topic’ in the comments table, rather than hide that information in the nested-set structure. IMO the left/right pair should only dictate nesting/ordering inside the owner context.

Then it doesn't matter if you have a single root or multiple-root nested set structure. (FWIW, I use multiple-root.) You can certainly grab all the comments for one topic in a single query either way.