Working with Hierarchical Data in MySQL
Some 10 years back, my first database ever was Oracle 7.smtng. In that time I didn’t develop websites but client/server applications for clients who needed mature database and could afford to buy it. But that was back days. So, after my background in databases with cursors, procedures and sub query support, for my day to day web developing I shifted to easier on going database – MySQL. When you build small to medium websites, that’s probably the best database there is. Although, used wisely it can handle lots of stress. Be sure to check out Cal Hendersons new book – Building Scalable Web sites on that subject. Those guys did a hellofa job with Flickr.
For years I’ we been degrading in ways of database usage, only working on my left and right joins. For many years I was prisoner of adjacency list model of my data hierarchy. Well, neither the projects I worked were that database creative. On our last project (www.smsoglasi.com) I had to do a slightly more complex category structure so I decided to use the long lost and forgot – preorder tree traversal algorithm. Joe Celko is by far the most respected author on the subject and there are better places on the net to read more on the subject.