Magento Hierarchical Category Database Design

First of all, there are two ways to manage hierarchical data in MySql(or other relational database). One is Adjacency List Model and the other is The Nested Set Model. They both have pros and cons. Again, i recommend you read this article http://dev.mysql.com/tech-resources/articles/hierarchical-data.html first before you start to work on hierarchy related issue.

Magento uses Adjacency List Model for some reasons, which i don't know what they exactly are. The problem of Adjacency List Model is traversing hierarchy tree is very difficult and low efficient. Usually you have to recursively query database to find out the depth/level of a node.

To address the problem of Adjacency List Model, Magento's category table has three extra columns:level, path, children_count. As the names suggest, 'level' stores the level of a node, 'path' saves the path from the root node to this node and 'children_count' records the number of children the node has. If you are interested in what they really look like in Magento's database, you can simply download and install Magento.

As you can imagine, with the three extra columns, traversing hierarchy tree becomes extremely easy.

You may also find out that in this way, the difficult part goes to manipulating: adding/deleting/moving a node. For each of these operations, you have to update those three extra columns for the nodes involved in your operation.

However, there is no perfect solution to hierarchy data(even in Nested Set Model, manipulation is very hard) because relational database is not designed to handle hierarchical data, not like XML. It is a trade-off. We either sacrifice one side for the other side. And i think traversing hierarchy is obviously the most critical part.