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

    Question Hierarchal Design For Nested Folders

    I'm trying to design a system to have nested items in a database. For example, I need to be able to create a task entry in a project system and have tasks that are nested off of it and so on. Most of what I have seen is in fixed depth nests where you know when designing the db or app how many layers the folders will drill, but this has to be a system with that variable unknown. So far I was thinking of starting with just one table

    Tasks

    Task ID | Task Title | Task Nesting
    -------------------------------------------
    example records
    -------------------------------------------
    1 Task One
    2 Task Two 1
    3 Task Three 2

    So with this example you can see that Task 3 is a subtask of task two which is a subtask of task one.

    Can anyone shed some light to if this is a good way to do this... other better ways.... and how can I pull this data once it has been created - what type of SQL statement will return the nests the way they are shown?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Hierarchal Design For Nested Folders

    Originally posted by Jdogg
    I'm trying to design a system to have nested items in a database. For example, I need to be able to create a task entry in a project system and have tasks that are nested off of it and so on. Most of what I have seen is in fixed depth nests where you know when designing the db or app how many layers the folders will drill, but this has to be a system with that variable unknown. So far I was thinking of starting with just one table

    Tasks

    Task ID | Task Title | Task Nesting
    -------------------------------------------
    example records
    -------------------------------------------
    1 Task One
    2 Task Two 1
    3 Task Three 2

    So with this example you can see that Task 3 is a subtask of task two which is a subtask of task one.

    Can anyone shed some light to if this is a good way to do this... other better ways.... and how can I pull this data once it has been created - what type of SQL statement will return the nests the way they are shown?
    What you are proposing is a very standard way of implementing a hierarchy, where each subtask relates to just 1 task. so it is fine.

    Another model, which works for this case AND where a subtask may belong to more than one task is:
    TASK( task_id, task_title );
    TASK_LINK( parent_task_id, child_task_id );

    Whichever approach you take, traversing a hierarchy via SQL is DBMS-dependent. In Oracle, there is a CONNECT BY clause - e.g.:

    SELECT task_id, task_title, LEVEL
    FROM tasks
    START WITH task_id = 1
    CONNECT BY PRIOR task_id = parent_task_id;

    1 Task One 1
    2 Task Two 2
    3 Task Three 3

    (LEVEL shows how far "down" the hierarchy the row is)

    Other DBMSs would have a different approach.

  3. #3
    Join Date
    Dec 2002
    Posts
    3

    Re: Hierarchal Design For Nested Folders

    That was interesting and very helpful. I plan on moving us to Oracle sometime next year, but until that time do you or does anyone else know of MySQL native SQL that queries for hierarchal records? I checked mysql.com and couldnt find any reference to that.

Posting Permissions

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