What version mssql? I though 2005 had native support for hierarchical data sets? Much like Oracle.
Mark On Thu, Sep 18, 2008 at 3:00 PM, Brad Wood <[EMAIL PROTECTED]> wrote: > My advice is stay away. > > Nested sets (left right node) are great for calculating ancestor and > descendant nodes, but they are a brittle data structure meaning if some math > goes wrong and the wrong nodes get pointed at the wrong parent you can get > fubared. (or if a developer tries to go in and "fix" the nesting by hand) > > If your data is changing on a regular basis, updates are going to be Hades. > You will get locks happening all over the table any time you add a new node > because of the ripple affect. Also, the logic to copy and paste a node with > its children elsewhere in the tree can be enough to make you want to shoot > yourself. > > Truthfully, I would stick with an adjacency list (parent_id). You can > update the table often and not bother the other records. It is harder to > screw up the data. Moving subtrees around is as simple as updating the top > node of the subtree to have a new parent. The only real downside is if you > find yourself needing to calculate descendant and ancestor nodes. > > That being said, there are ways to mitigate the short-comings of both > systems but I don't care for them much because they usually require keeping > de-normalized data updated. > > ~Brad > > ----- Original Message ----- > From: "Mike Kear" <[EMAIL PROTECTED]> > To: "CF-Talk" <cf-talk@houseoffusion.com> > Sent: Wednesday, September 17, 2008 11:30 PM > Subject: Nested sets under load > > >> I'm designing an application that will require a whole series of >> heirarchies - its an action plan where the plans can be quite complex. >> The idea is a business event happens (a sale or a project is started >> ) and that triggers a whole set of events that must follow. >> >> I'm thinking of using nested sets to lay out the heirarchies of >> events, because they are so flexible adn its so easy to move things >> about with them. But i'm a bit worried about how it might perform >> under load. >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312741 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4