Results 1 to 4 of 4

Thread: hierarchical tables structure

  1. #1
    Join Date
    Jan 2004
    Location
    France
    Posts
    43

    hierarchical tables structure

    I want to create hierarchical tables (4 levels, but of course the possibility to have a level 5 will be better)

    What is best method ?

    Let’s say Tables to organize a toy-factory

    ----------------
    One table >>>
    Toy :

    Id_toy
    IdSup (the Id of superior element)
    Level (1 to 4)
    Name
    ----------------
    4 tables >>>
    Toy1 :

    Id_toy1
    Name
    --------
    Toy2 :

    Id_toy2
    Id_toy1
    Name
    --------
    Toy3 :

    Id_toy3
    Id_toy2
    Name
    --------
    Toy4 :

    Id_toy4
    Id_toy3
    Name
    ----------------
    One table >>>
    Toy :

    Id_toy
    Hierarchy (with separtors like : 4,21,224,6458)
    Level (1 to 4)
    Name
    ----------------
    One table >>>
    Toy :

    Id_toy
    Level_1
    Level_2
    Level_3
    Level_4
    Name

    (a 0 for no level) like : 4 | 21 | 0 | 0 , for level 2
    ----------------
    5 tables >>>
    Toy1 :

    Id_toy1
    Name
    --------
    Toy2 :

    Id_toy2
    Id_toy1
    Name
    --------
    Toy3 :

    Id_toy3
    Id_toy2
    Name
    --------
    Toy4 :

    Id_toy4
    Id_toy3
    Name
    --------
    Toys :

    Id_toys
    Id_toy1
    Id_toy2
    Id_toy3
    Id_toy4
    -----------------

    Another way ?



    Thank you

  2. #2
    Join Date
    Jun 2004
    Posts
    1
    you can maintain the entire hierarchy in a single table.


    ID
    name
    Super_id


    where for the first level the super_id will be null, as follows

    toy1
    name
    null

    for the second level it will be as follows

    toy2
    name
    toy1

    and for the third level it will be as follows

    toy3
    name
    toy2

    this structure can go on till any number of levels. you need not have multiple tables for them.

  3. #3
    Join Date
    Jun 2004
    Posts
    5
    It dependes on what will be the table used for. If you need lot of queries like "select all toys from subtree defined by it's root", you may add collumns LEVEL and RANK where RANK is order of items when you search the tree in-depth.
    Of course, time to time you need to shift the RANK value for all items when you are inserting new item.

  4. #4
    Join Date
    Aug 2004
    Posts
    12
    google "nested sets"

Posting Permissions

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