#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Poland
    Posts
    57
    Rep Power
    12

    Tree Structure. How?


    Hi
    I just wondered what would be the best way to make a tree structure (filesystem) in database (postgresql in my case, but it's a general question).

    The best way I can think of for now is to do a structure like that:
    Code:
    nodes:
    id INT
    parent_id INT
    name VARCHAR( LIMIT )
    type BIT(2) -- 00 for file, 01 for directory, (links are optional) 10 for link to file and 11 for link to directory
    
    files:
    id INT
    content TEXT
    
    links: -- optional as I said
    from_id INT
    to_id INT
    I want it (structure) to be optimalised for the things like that:
    select all from a directory with a specified path/id
    insert/select a file using path/id
    read a whole directory and all subdirectories and all their subdiretories etc. etc. using path/id

    I won't be using any of listed below often so they don't need to be vary fast:
    getting path using id
    moving directories/files

    It's the best way to do this if you ask me, but maybe I'm just too tired so say what do you think.
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Check out our discussion of Graph representation in database. In it, we also discussed trees. Especially of interest to you should be the "materialized path" approach. Also, there is apparently an add-on to PostgreSQL in the 'contrib' section to imitate Oracle/MSSQL CONNECT BY syntax for adjacency list handling of tree data.

    Enjoy
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Poland
    Posts
    57
    Rep Power
    12
    thx for link

IMN logo majestic logo threadwatch logo seochat tools logo