Adjacency List Model and Nested Set Model for hierarchical data in MySQL

I am working on classifieds website these days. These kind of websites are getting famous these days and they also have average kind of traffic. while working on database designing for websites like classifieds / auction / web directories, we have to take care of few things like

1. This is a tree structure which may have n levels.
2. And there has to a straight way to traverse from top to bottom and from bottom to top.
3. Strong tree traversal algorithm.
4. Retrieving a Single Path between two nodes.
5. Depth of nodes and depth of sub-tree and lots of other things.

For this we need a strong table design and relationship structure. In MySQL we can manage this hierarchical data with two different models.

1. The Adjacency List Model
2. The Nested Set Model

The Adjacency List Model is a very basic table structure which maintain the column lets say "parent" to maintain the parent child relationship. (It actually hold the PK of parent node). But this model again has some disadvantages, described in brief on the link at the bottom.

so,I came across a another hierarchical model called as nested set model. In this type of hierarchical data model parent nodes envelope there children. This is based on algorithm called as preorder tree traversal algorithm. This is a very good concept while working on tree structure tables.

Source of information : http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

0 comments:

Post a Comment

Share this post with your friends