I'm not sure Celko described all the queries in those articles (the url I sent plus the followups in April and May that year. You'll have to hunt down those follow-ups. But, for a much easier reference, look at Joe Celko's _SQL For Smarties_, where in one of the later chapters, he covers the nested set hierarchy more completely. I actually used that chapter when I did my first implementation of that concept. For me, I needed a hierarchy that would stand up in any reasonable database. It worked out very well, even though I had to adapt the model to work across multiple tables. The book is money well spent.
The following Usenet article is where I first was convince that this would work for me. http://groups.google.com/groups?q=celko+CONNECT+BY+from+Oracle+in+SQL-Se rver%3F&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=%230%23O0OiFBHA.1932%40tkmsftng p04&rnum=4 That's a horrible URL. If it doesn't work right, go to your favorite usenet archive and look for : celko "CONNECT BY from Oracle in SQL-Server?" He posted a very nice article in July 2001 that explains a lot of the concept, including How to get a complete subtree stom one node. Reading the results is slightly tricker, since a columnar result set can't obviously look like a tree. As for having to update a large portion of the tree to add or subtract one node, yes. That's necessary. But for the cases I've dealt with, this is business structure data, and it changes very rarely by comparison to daily transactional information. The payoff is that every query against the tree is faster than it would have been under the CONNECT-BY architecture particular to Oracle. Oh, and in my case, and in what Celko describes, the update to move the LFT/RGT values to make room for a new node can be done in one update statement, even in mysql. So it's tricky to write, but very simple to operate. Kevin -----Original Message----- From: Grégoire Dubois [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2003 6:28 AM To: Kevin Fries Cc: [EMAIL PROTECTED] Subject: Re: Conception - Tree - Recursivity -Address book - Query speed Thank you very much for your answer. I read the article. I've understood that this nested tree model (with the "worm", I liked the image), permits to retrive very easiely the path of a node, and some other things. But : I've understood that if we want to modify the tree, all left and right numbers of all the nodes of the tree have to be changed. Why not... But biggest problem, I don't see how it permits me to get the whole tree in a recursive maner. I haven't seen a request that would give me something like that : (root(node1, node2(node3, node4))) corresponding to : root | | node1 node2 | | node3 node4 Kevin Fries wrote: >My advice is to consider an alternate storage approach. Instead of >storing parent id's, store the trees using the Nested Set model. > http://www.dbmsmag.com/9603d06.html > >About halfway down the article he presents a model where you never >store the parent id, but you do store two columns (LEFT and RIGHT) >which represent the boundaries of the child records, and their >respective LEFT and RIGHT information. > >That sounds complicated, and it is. But it is an ANSI-compliant >solution, and it can be used to get terrific results. > >His article is followed up in the april and may issues, as well, also >online. > >In addition, you can follow up his article by looking in Usenet groups >for the term "Nested Set Hierarchy" or "Nested Set Model". > > >Good luck with it. >Kevin > >-----Original Message----- >From: Grégoire Dubois [mailto:[EMAIL PROTECTED] >Sent: Wednesday, April 02, 2003 6:54 AM >To: [EMAIL PROTECTED] >Subject: Conception - Tree - Recursivity -Address book - Query speed > > >Hello all, > >I am putting multiple "address book" (trees) into a MySQL database. >These "address book" are made of "directories" and "persons". > >It gives something like that for the tables : > > Directory > -------------- > ID > Name > ID_father (the reference to the father directory) > > > Person > ------------- > ID > Name > ID_father (the reference to the father directory) > > >Creating the tree doesn't give me problem. Where I ask me some >questions, is the speed to get the whole tree from the database in a >recursive way. >The way the tree is defined in the database, I will be obliged to make >for each node a SELECT to know all its children, then do the same for >the children, etc... Each SELECT will give me a ResultSet. And from each > >ResultSet I will know if the node has got childrens, and if yes, I'll >have to make new requests to the database (one for each child), etc... > >As I will have a lot of clients that will ask for those trees, I am >wondering if this technique won't take too much ressources and be too >slow. > >I know that Oracle uses CONNECT BY PRIOR for this type of problem. But, >as this functionnality isn't available yet in MySQL, I'm asking you for >the best solution you would think of. > >Thank you very much for your help. > >PS : Any website talking about database conception would be very >helpfull to me if you know some. > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
