[mmcgrana]

You might consider how much of the structure and ordering you actually need to maintain in the database, and how much you could compute in your app servers after pulling the raw data from the database.

For example, say that your comments table stored the user id, the post id, the parent comment id (where the parent is the one the user clicked "reply" on), the vote count, and a timestamp. Then to render a page for all of the comments for a particular post, you could just pull all of the comments keyed by that post id and then order/arrange them however you want in your application code, such as by timestamp or number of votes. This doesn't require any tricky db writes and generally removes load from the db (which is desirable since its the hardest part of the app to scale).

The harder part is dealing with a case like the user comments page on neww.yc, where you show all of the user's comments along with of the descendants of those comments. Aside from the traditional approaches to storing hierarchies in relational dbs like the ones you linked to, I know of two other possibilities. One is to store the path of nodes to the root as lexicographically-comparable strings. The other is to create a separate table that stores ancestry relationships, in which for each node you have one row for each of its ancestors in the tree up to the root. Again, both of these approaches would allow you to pull out exactly the comments that you needed, but you would have to do the arranging in the application code.

[RyanGWU82]

I worked at a company where we used a decimal number to represent a tree for a threaded forum. It meant that we could use an ORDER BY clause and quickly get all the descendants sorted, like this:

7.401 7.40101 7.40102 7.4010201 7.40103 7.4010301

The downside was that it wasn't nearly this straightforward -- numbers seemed to be assigned haphazardly. And the scheme wasn't documented well, so it was nearly impossible for new programmers to understand.

I would not recommend this approach again. If you need high-performance searches for descendants, a separate table relating ancestors to descendants will be much more straightforward in the long-run.