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.
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?