On PHPBuilder.com today there's a new tutorial showing you a way to work with hierarchical data in MySQL from your PHP application in two different ways (methods).

The hierarchical data structure is one of the most common data structures in programming. It represents a set of data where each item (except the root) has one parent node and one or more children nodes. Web programmers use hierarchical data for a number of applications, including content management systems, forum threads, mailing lists, and e-commerce product categorization. In this tutorial , I will explain how to manage hierarchical data using MySQL and PHP. Specifically, I will explore the two most popular hierarchical data models: adjacency list model and nested set model.

They start with the adjacency list model, showing how to do a left join on the table on the parent ID to record ID and then filter through them, merging them into a main array as needed. The nested set model is slightly different and involves two new columns, a "lft" and "rgt", that point you to the records around the current one. This method makes the actual SQL query to find the structure a bit simpler, but isn't quite as flexible as the first method.

On the Ibuildings techPortal there's a new tutorial from Sam de Freyssinet that follows up on a previous article he wrote about using HMVC (Hierarchical-Model-View-Controller). In this new article, he talks about some of the things you can do with that pattern to increase performance.

Hierarchical-MVC has been shown to make large web applications easier to scale out, but there is a price to pay— namely overall performance. This article will investigate ways of improving performance within HMVC web applications using asynchronous processing and some good old caching techniques. Predominantly this article will use examples written for the Kohana Framework; however all the concepts portrayed here could apply to any framework or web application.

He starts by pointing out what's wrong with the typical HMVC application - namely that the clean separation of code structure (the hierarchical part) costs the application in overall speed and memory usage. He suggests a simple technology for helping ease the load - caching as much information as possible, mostly in the HTTP client code since HMVC applications rely heavily on it. Code samples includd show how to implement this in a simple Kohana application.

On the Ibuildings techPortal today there's a new post by Sam de Freyssinet about a slight modification to a well-known pattern (MVC) by adding some scalability - the Hierarchical-Model-View-Controller pattern.

It can also be very costly in time and resources to re-architect software that not scaled well. Ideally applications should grow organically as required and without large sums of money being exchanged in the process. [...] The Hierarchical-Model-View-Controller (HMVC) pattern is a direct extension to the MVC pattern that manages to solve many of the scalability issues already mentioned [in this post].

He talks about the benefits of the HMVC style and how its multiple parts work together and how it promotes code reuse more than the traditional MVC pattern. Some sample code is included so you can get your hands dirty with more than just the concepts behind the pattern including a more real-world example, their service (Gazouillement) that works similarly to twitter.

Daniel Cousineau submitted a new blog post he's come up with that looks at using hierarchical data in a MySQL database.

I recently had fun with an all-to-common issue with SQL driven websites: hierarchical data. For those who don't like big words, think trees. Other people have already discussed storage methods, and I would actually highly suggest you read the writeup if you haven't already.

He includes a sample table you can work from and some code that can be used to generate a select (based on a $depth variable) parent/child values from it. A few simple function calls later and you have a nested array or lots of little subarrays maintaining the parent/child relationships the database has.