Best way to store & retrieve a directory structure in database

Has anyone here created some sort of directory structure before, with categories & subcategories, that they stored in a mysql database?

This first time I tried this, I set up each record with a category name, category id and parent id (The parent id for root-level categories was 0). I figured there was some sort of recursive function that would retrieve the tree structure from the database. However, I just couldn't figure it out. In the end I settled on a different solution.

I stored my category records like this:


Category Name Order Level

Category A 1 0
Category AB 2 1
Category B 3 0
Category BA 4 1
Category BAA 5 2


The fields were printed out by their order number ascending, and indented based on their level number like this:


Category A
|_Category AB
|
Category B
|_Category BA
|_Category BAA


From this could be inferred the parent-child relationships between categories. But now the order numbers have to be refreshed every time a category is added or deleted, which is more error-prone than just storing the parent-child relationships.

Can anyone else share how they store and retrieve directory structures?

Basically you have 2 database model options afaik - the Nested Set Model and the Adjacency List Model (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html).

The pros and cons of each along with some example sql code to perform certain tasks are discussed in the above link.

Personally, I now always use the Nested Set Model. Once you get your head around how the lft and rgt values for each category are used to store the category's location in the hierarchy life should become a lot easier imo.