Title: Joe Celko’s Trees and Hierarchies in SQL for Smarties
Author: Joe Celko
Summary: A great book for those interested in managing hierarchies. Not written with MySQL in mind but highly applicable.
When I started looking into managing hierarchical data I found a lot of short articles that provided bits and pieces of the big picture, but nothing gave a good in-depth review of everything I needed to know to handle hierarchies.
One trend that began to develop was that the best information on the subject was coming from Joe Celko, a rather prolific author/speaker on all things SQL.
Joe Celko has a book in publication called SQL For Smarties, and this book had some basic information on handling hierarchies, which he followed up with Trees and Hierarchies in SQL for Smarties, a truly excellent book on the subject.
This book is not for the faint of heart, but it really is a must-read for those who are looking to handle hierarchies in SQL.
The book only addresses MySQL once, and while in that section it repeats Joe’s opinion of MySQL not being a real database, users of MySQL 5 can rest assured that the stored procedure examples in the book work without modification (thanks to Joe and MySQL 5 both using the standard PL/PSM stored procedure syntax).
Overall this is an excellent book for those looking to implement hierarchical data in SQL.
May 5th, 2007 at 1:45 am
Hi Mike,
Thanks for the illuminating article, “Managing Hierarchical Data in MySQL”. This article made me realize how much I have to learn, and how much I want to learn about using sql alone (I usually rely on php/mysql). Anyway, I want to get more into hierarchies, but I looked through the Celko book (through Amazon) and frankly it looks too advanced for me.
Can you recommend another book or resource to prepare me for Celko?
Thanks!!!
Audrey
July 11th, 2007 at 2:33 pm
Audrey,
I would suggest purchasing “The Book”. Amongst the circles of MySQL developers “MySQL” by Paul Dubois is the definitive guide to learning and using MySQL. It is for noobs and pro’s alike.
http://www.kitebird.com/mysql-book/
Good luck with your MySQL endevours!
- Jonathan
August 14th, 2007 at 12:15 am
Dear Sir,
I reside in India, and because of you have understood very clearly the Hiererchical database method. Mnay , many THANKS for making me wiser.
I have a very small query. In Nested Set Model, in Adding a new node area.
In the example , lft and rgt are 1 and 20 . while adding a new node ‘Games Console’ between ‘Television’ and ‘Portable Electronics’, two new number come in the table. This should change the number range to 1 and 22. Also all the numbering inside 22 will change.
In the SQL ststement I could not understand how recursive increment takes place or How the numbering is maintained? How to maintain the tables and the rgt and lft data if the table is pretty large.
Please advise me. I will be greatful. Thanks,
Amod
October 19th, 2007 at 4:14 am
contact me at my email — easy enought to find.
Inserting new subtrees (a node is a subtree of size one) is easy:
1) find the parent node of the inserted tree
2) increase all lft and rgt values by 2*(size of subtree) that are higher than the rgt value of the parent node
3) renumber and insert the subtree
The code is in TREES & HIERARCHIES IN SQL — go to Amazon.com
December 27th, 2007 at 1:57 pm
Joe,
I just wanted to thank you for writing this wonderful book on trees and hierarchies models in SQL. It was a lifesaver for the development of our CMS application.
Thanks,
TIago