I once heard of a system for representing threaded comments in a relational database, wherein the ID of each comment was a slash-separated list of its ancestors, e.g.:
1 Initial comment
1/1 Re: Initial comment
1/2 Re: Initial comment
1/2/1 Re: Re: Initial comment
1/2/2 ...
These sorts of IDs have the wonderful property that you can efficiently select any subtree using a LIKE query, e.g. "SELECT * FROM comments WHERE path LIKE '1/%'". These queries are efficient because they use string btree indexes. And the results may even be sorted the right way, provided you have monotonically increasing IDs within each level.
I think I read about this technique being used on Slashdot a few years ago, but I can't remember the name or find any reference. Still, it seems like a way to achieve the same benefits that the OP cites for comments with PostgreSQL's ltree support on any database with reasonable string indexing.
That, or restrict the maximum number of comments at a given level to a large, but fixed number that you can express as a fixed size number, say two digits in base 62 (digits are 0-9a-zA-Z). Or, if you use postgres, make the index column a list of integers.
1 Initial comment
1/1 Re: Initial comment
1/2 Re: Initial comment
1/2/1 Re: Re: Initial comment
1/2/2 ...
These sorts of IDs have the wonderful property that you can efficiently select any subtree using a LIKE query, e.g. "SELECT * FROM comments WHERE path LIKE '1/%'". These queries are efficient because they use string btree indexes. And the results may even be sorted the right way, provided you have monotonically increasing IDs within each level.
I think I read about this technique being used on Slashdot a few years ago, but I can't remember the name or find any reference. Still, it seems like a way to achieve the same benefits that the OP cites for comments with PostgreSQL's ltree support on any database with reasonable string indexing.
Anyone know the name of that technique?