Jessica Kennedy wrote: > I have looked over the various methods for displaying hierarchal data with > MySql, and there don't really seem to be many elegant solutions-- the best > method (according to many) seemed to be creating a column for "lft" & "rgt" > values as a nested tree structure
That is called a nested set and is pretty much optimized for reading. > which is completely impractical in this situation because I would have to > re-assign those variable every single time somebody inserted a record into my > database (which should be many times/day). How many is many? Let's say that recalculating your nested set is going to take 15 seconds every time it is updated. Is that really a problem? > I found the syntax here: http://www.evolt.org/node/4047 however, I know > basically nothing about proper syntax for stored procedures, and have really > not found a lot that has helped. Why, > oh, why is this syntax giving me errors? Because that syntax is for MS SQL Server 6.5 and you are using MySQL. > Equally as important, does this method of creating a temp table seem like an > appropriate solution to my problem? Not if you intend to do this on every request. What I have done before is used a denormalized model where the records themselves had an adjacency list model, but I maintained a nested set in a separate table. With a trigger on the records themselves that only fired when the ID and/or parentID fields changed and updated the nested set the rest of my code could use either the adjacency list or the nested set depending on what was easiest. The only thing to watch out for is the way you handle concurrency issues in your trigger code. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292114 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
