Hi Barney, Agree wholeheartedly with performance comments re: my approach. Never said it was good - I just didn't know about nested sets in '97!!! Am looking to port to a nested set model based on your comments.
Do you know any easy way to get the nest level for each record using a nested set without additional queries? For example, a fully extended sidebar navigation pulling from a nested set page table with a different style for each indent level . . . Best Wishes, Peter -----Original Message----- From: Barney Boisvert [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 01, 2006 4:22 PM To: CF-Talk Subject: SPAM-LOW: Re: Nested Set Model > That doesn't sound very static at all. The question is how often it happens. I'd rather spend 20-fold the time to move categories 50 times a day than I would spending 3-fold the time to recall the stuff 5,000 times a day. > If these changes are infrequent and or the table will always remain > very small, you should be ok. I have several sets that number in the thousands. No performance issues at all with moving subtrees around. Mods still complete in tens of milliseconds. When you consider caching extra data in an adjacency list (like the depth and/or path data), suddenly your tree modifications require changing just as many nodes as you'd have to mod in with a nested set. If I reorder peer nodes with a nested set, I have to change the nodes and all their decendants. If I do it with the path data as Peter proposed, I have to do the same thing. The same thing goes for moving subtrees (since reordering is just a special case of moving a subtree). With Peter's proposal, I also have slower recall (searching substrings, rather than numeric indexes), and more wasted storage (the length of his globalOrderer field). Bottom line, make sure you consider all the ramifications of your "efficiency" improvements, or you could be worse off (paying the same price, but not gleaning all the benefits) cheers, barneyb On 2/1/06, Brad Wood <[EMAIL PROTECTED]> wrote: > > From what you describe, nested sets is probably better for your > > scenario. > > Actually I'm not so sure of this statement based on this quote of > Mike's: > > "my client wishes to be able to move categories to any other category > and also reorder a category within a particular level" > > That doesn't sound very static at all. If this table has the > potential to grow very large at all, I would keep away from the left > node right approach IF AND ONLY IF your client truly wishes to make > changes on a regular basis. > > If these changes are infrequent and or the table will always remain > very small, you should be ok. > > > are you saying that I could do without the level column and rely > instead > on the query to determine which level each category is on > > Yes, but I wouldn't recommend it for performance. My point was, any > time the structure changed, you would need to update your > de-normalized level count-- definitely a down side to the adjacency > list. > > > is it not necessary to utilize a parent_id column using nested sets? > > That is correct. It wouldn't hurt anything I suppose, but you have to > keep everything in synch that way since you are basically storing the > relationships two different ways at the same time. > > And in comment to Barney's statement: > > "There also isn't any way to order the children of a given node." > > Actually there is, but it would require an additional ordering key > (Which Barney did mention later) like an "item_sort" column which > would define the order of sibling nodes. > > This conversation could go on forever. I would ask yourself the > following questions: > > 1. How large will this tree grow? Hundreds, thousands, hundreds of > thousands... 2. How often will the data in this table be modified? > Once a week, day, minute... > 3. How often is this table selected from? Ties in with items one and > two, which will both place locks on the table while they are updating. > 4. How do you most need the data? Single record, all > ancestors/descendants, all records at a given depth... > > Have fun with whichever method you choose. We can help you with > recursion if you need it. Barney has already provided some excellent > sample query for the nested set. > > ~Brad > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231041 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
