RE: parent - child relations
Although I believe that there are lots of reason why you should do this in the db (outlined in my last mail regarding this) , if you are using XML and you want to do calculations, its very easy in XSLT or using the cf XML functions .. for example to sum up subtotals in invoices the xPath function sum(/customer/invoice/subtotal) does the trick WG > Although xml is the proper way to display data, I need to calculate data. > I'm still working on it. If I come up with anything (hopefully) > I'm going to > post it to the forums. > > Thanks everyone, > Mahmut ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: parent - child relations
Although xml is the proper way to display data, I need to calculate data. I'm still working on it. If I come up with anything (hopefully) I'm going to post it to the forums. Thanks everyone, Mahmut - Original Message - From: "Rob Rohan" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, January 08, 2003 11:08 AM Subject: RE: parent - child relations > It seems though, if you are need that much nested data there is probably a > better solution to the problem then this type of tree model (i.e. using > master - detail tables) that a database might be more optimized for (or > perhaps xml might be a solution). > > Rob > > -Original Message- > From: webguy [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 08, 2003 10:54 AM > To: CF-Talk > Subject: RE: parent - child relations > > > The problems with that method is path enumeratation etc. > It's easy if you have an id and just want the category under x, > > But what if you want the nodes from root to a deeply nested node. It turns > out to be a very expensive query... > Thats why the Nested Tree Model is useful (see my last mail) > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: parent - child relations
Yes true. DB are a rational, rather than hierarchical data. LDAP is a closer match, and of course the file system is another. The problem with moving it to XML/other is that you can't do joins on the data. Many DB's have extensions to help with trees. It will be interesting to see what Microsoft will do in this area, as SQL server engine is going to be used to implement the new MS File system. WB > It seems though, if you are need that much nested data there is probably a > better solution to the problem then this type of tree model (i.e. using > master - detail tables) that a database might be more optimized for (or > perhaps xml might be a solution). > > Rob > > > The problems with that method is path enumeratation etc. > It's easy if you have an id and just want the category under x, > > But what if you want the nodes from root to a deeply nested node. It turns > out to be a very expensive query... > Thats why the Nested Tree Model is useful (see my last mail) ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: parent - child relations
Rob Rohan wrote: > It seems though, if you are need that much nested data there is probably a > better solution to the problem then this type of tree model (i.e. using > master - detail tables) that a database might be more optimized for (or > perhaps xml might be a solution). Databases indeed are not particularly well suited for this type of data, but it is not like they will choke once you reach the 100 records. There are basically 3 models, nested sets, adjacency tree and a path method. Which one is best suited for you depends on many things, such as size of the dataset, (relative) insert/update/delete/select frequency and even the used database (DB2 has a rather complete implementation of WITH which helps with adjacency lists, and Oracle has CONNECT for that). But if you wish to store really large trees, I would recommend consulting somebody who knows your database platform in dept, because database internals such as table locking strategies and index concurrency will kill your performance if you choose the wrong one. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: parent - child relations
It seems though, if you are need that much nested data there is probably a better solution to the problem then this type of tree model (i.e. using master - detail tables) that a database might be more optimized for (or perhaps xml might be a solution). Rob -Original Message- From: webguy [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 10:54 AM To: CF-Talk Subject: RE: parent - child relations The problems with that method is path enumeratation etc. It's easy if you have an id and just want the category under x, But what if you want the nodes from root to a deeply nested node. It turns out to be a very expensive query... Thats why the Nested Tree Model is useful (see my last mail) ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: parent - child relations
The problems with that method is path enumeratation etc. It's easy if you have an id and just want the category under x, But what if you want the nodes from root to a deeply nested node. It turns out to be a very expensive query... Thats why the Nested Tree Model is useful (see my last mail) WG > -Original Message- > From: Rob Rohan [mailto:[EMAIL PROTECTED]] > Sent: 08 January 2003 18:49 > To: CF-Talk > Subject: RE: parent - child relations > > > That is a very cool article. It is Somewhat like storing a linked > list. here > is some ascii art that might help :) > > |--| > \/ | > +===+ | > | affs | | > +---+ | > |pk |--| > |info | > |fk | > +===+ > (pk = primary key > fk = foreign key) > > So the first item has nothing in the foreign key, and all others > have their > foreign key set to their parents primary key. for example > (mssql server) > > -- adding a new affiliate > INSERT INTO affs ( > info, > fk > )values( > 'this is a new aff', > (SELECT pk FROM affs WHERE info = 'parent affiliate' > ) > > then go get all the affiliates for one affiliates you could do > SELECT * FROM affs WHERE fk = (SELECT pk FROM affs WHERE info = 'parent > affiliate') > > Hope that helps a bit, > Rob > > -Original Message- > From: Mahmut Basaran [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 08, 2003 8:24 PM > To: CF-Talk > Subject: parent - child relations > > > Hi All, > > I need some advices (and sample codes if possible) about how to > implement a > parent child style database. I read the articles at sql team > > http://www.sqlteam.com/item.asp?ItemID=8866 > > Anyone has a better and/or easier solution for nested trees ? > > By the way I'm going to use it to calculate sales totals of > affiliates where > an affiliate can have their own sub affiliates, and a sub > affiliate can have > its own ones too etc... > > Any advise is greatly appreciated, > > > Mahmut Basaran > > ~~oO Measure Twice Cut Once Oo~~ > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: parent - child relations
I suppose to be proper I should say |--| *| | +===+ | | affs | | +---+ | |pk |--| |info |1 |fk | +===+ (pk = primary key fk = foreign key) Rob ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: parent - child relations
That is a very cool article. It is Somewhat like storing a linked list. here is some ascii art that might help :) |--| \/ | +===+ | | affs | | +---+ | |pk |--| |info | |fk | +===+ (pk = primary key fk = foreign key) So the first item has nothing in the foreign key, and all others have their foreign key set to their parents primary key. for example (mssql server) -- adding a new affiliate INSERT INTO affs ( info, fk )values( 'this is a new aff', (SELECT pk FROM affs WHERE info = 'parent affiliate' ) then go get all the affiliates for one affiliates you could do SELECT * FROM affs WHERE fk = (SELECT pk FROM affs WHERE info = 'parent affiliate') Hope that helps a bit, Rob -Original Message- From: Mahmut Basaran [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 8:24 PM To: CF-Talk Subject: parent - child relations Hi All, I need some advices (and sample codes if possible) about how to implement a parent child style database. I read the articles at sql team http://www.sqlteam.com/item.asp?ItemID=8866 Anyone has a better and/or easier solution for nested trees ? By the way I'm going to use it to calculate sales totals of affiliates where an affiliate can have their own sub affiliates, and a sub affiliate can have its own ones too etc... Any advise is greatly appreciated, Mahmut Basaran ~~oO Measure Twice Cut Once Oo~~ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: parent - child relations
Check out David article and stored procs http://www.codebits.com/ntm/ A smart implimention of J.Cleckos model... WG > -Original Message- > From: Mahmut Basaran [mailto:[EMAIL PROTECTED]] > Sent: 09 January 2003 04:24 > To: CF-Talk > Subject: parent - child relations > > > Hi All, > > I need some advices (and sample codes if possible) about how to > implement a parent child style database. I read the articles at sql team > > http://www.sqlteam.com/item.asp?ItemID=8866 > > Anyone has a better and/or easier solution for nested trees ? > > By the way I'm going to use it to calculate sales totals of > affiliates where an affiliate can have their own sub affiliates, > and a sub affiliate can have its own ones too etc... > > Any advise is greatly appreciated, > > > Mahmut Basaran > > ~~oO Measure Twice Cut Once Oo~~ > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
