Designing a threaded commenting system [closed]

Question

What would be the best way to design a threaded commenting system so that it doesn't hammer the database?

Answer 1

Modified pre-order tree traversal (or what Matt refers to as "nested set") is the way to go.

If you happen to be working in Django, there's a third-party app, django-mptt, that makes implementing MPTT in your models a one-liner.

Answer 2

SELECT ... START WITH ... CONNECT BY

Oracle has an extension to SELECT that allows easy tree-based retrieval.

This query will traverse a table where the nesting relationship is stored in parent and child columns.

select * from my_table
    start with parent = :TOP_ARTICLE
    connect by prior child = parent;

http://www.adp-gmbh.ch/ora/sql/connect_by.html