Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002

    Question Unanswered: Hierarchal Design For Nested Folders Using MySQL

    I posted a question in the design and concepts folder that got me only half way to solving my problem. I need to figure out how to do a Hierarchal Design in MySQL to represent multiple layers of nested folders. I could do the following

    Table 1
    Folder ID(pk) Folder Name

    Table 2
    Filename Folder ID (fk)

    But that would only provide for one layer of nested folders. According to the response I received in that other forum folder this is possible with Oracle... does anyone know how to do this in MySQL?


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    the way to store your hierarchy is just like you had it in your first example

    create table tasktable
    ( task_id primary key
    , task_descr
    , task_parent
    , foreign key (task_parent)
    references tasktable (task_id) )

    as for retrieving, well, only oracle does that recursively

    in mysql you will have to bring in the entire tree (select all rows of the table) into your scripting language (php) and traverse the tree with code

    either that, or traverse the tree by repetitively calling the database one subtree level at a time, but the performance here sucks mightily

    finally, you could compromise and use a single query with a number of self-joins, and while this is quite efficient, it can only traverse the tree as many levels deep as the number of self-joins you are willing to code into the query



  3. #3
    Join Date
    Dec 2002

    Re: Hierarchal Design For Nested Folders Using MySQL

    Another way to do it:

    create table task
    (id1 int not null,
    id2 int not null auto_increment,
    descr varchar(50) not null,
    primary key(id1,id2));

    This way is easy to get the "father" instancies by selecting rows with id2=1. "child" instancies will follow the father's id1. Getting/checking a task with no subtask is much easier than in the other implementation.


    id1 id2 descr
    1 1 'First Task'
    1 2 'First Task first subtask'
    2 1 'Second Task with no subtask'
    3 1 'Third Task'
    3 2 'Third Task first subtask'
    3 3 'Third Task second subtask'

    It's up to you...

Posting Permissions

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