Hi (Merry Christmas and Happy New Millennium everyone!),
I've been wondering what would be the best way to do things like threaded
messages on Postgresql- e.g. where you have the usual parent-child
relationships between entities- each entity has a unique id and a parent id.
So far I'm considering two reasonable options.
<options>
Option 1 - do it the obvious "proper way" - have the application do selects
recursively.
e.g. To select child items
<pseudocode>
traverse ( id, orderspec) {
select id,subdata from tablename where parentid=id order by orderspec
for each id
display(id,subdata)
traverse(id, orderspec)
}
</pseudocode>
Option 2 - do it the kludgy limited way.
Keep the same structure as Option 1 but add a new text column I'll call
geneaology (or gene for short).
For a structure like
1
|_
| |
2 3
|_
| |
4 5
Item 4 will have gene=00000001,00000003,00000004,
Where the ids are all zeropadded _hexadecimal_ and delimited by commas (for
easier human processing if necessary).
So to select all items with parent id=3 (including item id=3) one would use
a query like:
select * from tablename where gene like '00000001,00000003,%' order by
orderspec;
Option 2.1 - use a combination of the two, e.g. use option 1 when the depth
gets crazy.
Option X - any ideas?
</options>
The trouble with option 1 is it seems it will be quite slow when you have
lots of items at significant depths.
The trouble with option 2 is the geneaology column can get quite huge (9 X
depth bytes) if the depth increases (e.g. there is a flame war ;) ). What
is the recommended max indexable text length in Postgresql 7.0? If it won't
index well then option 1 may actually be better.
If option 2 indexes well, I'm thinking of using it and limiting the depth
to say maybe a hundred. I figure in practice it's time for a new thread
once the depth is > 100 - it's mutated enough to warrant a new geneaology ;).
Oracle has a "start with" feature, but even so I'm not sure if the
performance is better that way - it may just be for application level
convenience.
Any comments or suggestions welcome.
Thanks,
Link.