Category: mysql

Working with Hierarchical Data in MySQL

Author: seven June 12, 2006

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 ( 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.

Must read:

Database wizard Joe Celko's SQL for Smarties (Worship Joe Celkoooo!)

Managing Hierarchical Data in MySQL

Storing Hierarchical Data in a Database

CEO/CTO at Nivas®
Neven Jacmenović has been passionately involved with computers since late 80s, the age of Atari and Commodore Amiga. As one of internet industry pioneers in Croatia, since 90s, he has been involved in making of many award winning, innovative and successful online project. He is experienced full stack web developer, analyst and system engineer. In his spare time, Neven is transforming retro-futuristic passion into various golang, Adobe Flash and JavaScript/WebGL projects.