Categorized | News

Storing Hierarchical Data in a Database

Posted on 05 June 2003 by Demian Turner

Thanks to Harry for this link.

Whether you want to build your own forum, publish the messages from a mailing list on your Website, or write your own cms: there will be a moment that you’ll want to store hierarchical data in a database. And, unless you’re using a XML-like database, tables aren’t hierarchical; they’re just a flat list. You’ll have to find a way to translate the hierarchy in a flat file.

Storing trees is a common problem, with multiple solutions. There are two major approaches: the adjacency list model, and the modified preorder tree traversal algorithm.

The ‘adjacency list’ is more commonly known as the id-parent id method, or more recently as the brain dead method, and it works great.  The following article covers the theory on the first page, but more interestingly it gives a good explanation of the modified preorder tree traversal algorithm on pages 2 and 3, familiar if you have ever worked with classes like PEAR::Tree.

Bookmark and Share

Leave a Reply



Demian Turner's currently-reading book recommendations, reviews, favorite quotes, book clubs, book trivia, book lists



PHPkitchen recommends you also check out the following sites :

Accounting for Small Businesses

FreeAgent sign-up