Managing Hierarchical Data

I'm not sure if you ever encountered with this, anyway hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories.

Most of the time I'll use the "Adjacency List Model" to handle this. It's easy to understand, and the code needed is simple, too. What then, are the downsides of the adjacency list model? In most programming languages, it's slow and inefficient. This is mainly caused by the recursion. We need one database query for each node in the tree. As each query takes some time, this makes the function very slow when dealing with large trees.

So if you want a different approach with better performance, you might want to consider the "Nested Set Model". Read more about this model here written by Mike Hillyer.