> 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231029 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
