Comment trees in sql and html

Question

I want to think of a way to display and query comments.

Each comment can have children and siblings. I would like to display 40 comments in a page. However, I would like to show all the children. The problem here is if a child has more then 40 comments beneath it this will not work out. Also, I have no idea how I might get the comments. I could query all comments that belong to media.id and build a tree based on its viewpoint, but wouldn't parsing and traversing all of that take time? I am not sure what's the best method of doing this.

The comments table looks like:

public class comment
{
    public PK id;
    public media media;
    public comment parent;
    public user_data author;
    public DateTime date;
    public string text;
    public Flags flags;
}

Answer 1

This article explains how to build a tree lineage into your table: http://www.sqlteam.com/article/more-trees-hierarchies-in-sql At that point you can query to get pages of 40 normally, sorting over the lineage field.
...Here's an example that shows how to do paging in t-sql: http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx

In your case, you would use SELECT ROW_NUMBER() OVER(ORDER BY lineage) AS RowNum or something to that effect.

With this method, your queries will be very fast.