Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
05-15-2011, 12:22 AM #1
- Join Date
- Nov 2009
- Thanked 0 Times in 0 Posts
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
Category A |_Category AB | Category B |_Category BA |_Category BAA
Can anyone else share how they store and retrieve directory structures?
05-15-2011, 01:09 AM #2
- Join Date
- Feb 2011
- Thanked 395 Times in 395 Posts
Basically you have 2 database model options afaik - the Nested Set Model and the Adjacency List Model.
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.
Users who have thanked bullant for this post: