Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Nov 2009
    Posts
    53
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question 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:

    Code:
    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:

    Code:
    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?

  • #2
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    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:

    bonecone (05-16-2011)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •