Something I've done in the past is to add three columns to the db table: * lineage * depth * treeSortOrder (probably a better name lives out there)
e.g. id:1 'root node' (lineage = '/', depth=0, treeSortOrder=1) id:34 'child of root' (lineage = '/1/', depth=1, treeSortOrder=2) id:49 'child of child' (lineage = '/1/34/', depth=2, treeSortOrder=3) etc. The treeSortOrder will be distinct to the entire table, and be based on the order of the item within its parent and also the parents order within its parent, etc. Query the table and ordering by this value will give you the entire tree without needing recursion. For display purposes, looping over the records and adjusting indents based on the depth column is all that is necessary. Maintenance of these three values can be done using database triggers or through the code responsible for editing the table. Updates to the values need to be performed everytime a record is inserted, deleted or updated. Advantages: querying the heirarchical data becomes very quick Disadvantages: maintaining the extra data could be tricky and/or cause overhead, especially if you have more writes than reads. HTH Dominic On 25 November 2010 15:30, Jerry Barnes <critic...@gmail.com> wrote: > > How are you tracking parent/child relationship now? > > When I build trees, I use an adjacency list model with a column for parent > id, a column for current id, and a column for the description. When > building the tree, I retrieve the whole data set and then use a recursive > function to parse the tree. I pass the query into the function and use > query of query so I don't have to continually hit the database. This works > fine on small trees like nested menus. Unfortunately, it can be very slow > on large trees. On large trees, I build the tree and save the results in a > table with an indent column and sort order column. When new nodes are > added, I rerun the build and update the stored tree. Not optimal, but it > works. > > > Anyway, the administration of the trees is pretty easy. Just open a node > and pick it's new parent. > > J > > - > > No man's life, liberty, or property is safe while the legislature is in > session. - Mark Twain > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339534 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
