Re: parent/child relationship in the same table.

Thanks, Kendall.

I found an article by Joe Celko that seems to be working pretty nicely for me. It's located at
<http://www.intelligententerprise.com/001020/celko1_1.shtml>. He's also posted the text to this article in response to similar questions regarding trees in this newsgroup.

To my eye, it looks pretty clever even though it is a hack. I managed to tweak it slightly so that I only need to renumber very small subsets of the table with every insert. My real table actually contains messages and replies (like this newsgroup) so the "trees" are small but numerous. I'm not sure if it's a better approach than building the path with each insert but it's working. And it seems like it can be ported to other databases pretty easily.

The thing that I don't like about it is that there are now three extra columns that are really just implementation details that say nothing logically about the data I'm trying to model. Fabian Pascal would not be pleased. But his explanation for modeling trees used some EXPLODE() function that, as far as I can tell, doesn't exist anywhere. (I'm supposed to put pressure on my DBMS vendor to provide the right solutions--think they'll listen?)

Once I get a larger data set, I might try experimenting with the path-based approach to see how it performs and how the code looks.

Thanks again,
Jason. Received on Tue Nov 20 2001 - 18:24:48 CET