Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    18

    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
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is best done with just one table

    create table Toys
    ( id integer not null primary key
    , parent integer null
    , constraint validparent foreign key (parent) references Toys (id)
    , name varchar(50) not null
    )

    search the web for adjacency model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    18
    but how can you do a foreign key onthe same table ??!!

    , constraint validparent foreign key (parent) references Toys (id)

    I'm working with access and mySQL

    thank you

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't understand your question

    are you asking how to do it in Access?

    in Access you have to name the primary key constraint


    create table Toys
    ( id integer not null constraint pkToys primary key
    , parent integer null
    , constraint validparent foreign key (parent) references Toys (id)
    , name varchar(50) not null
    )

    this syntax works in Access 97

    for more information, see Common DDL SQL for the Microsoft Access Database Engine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    18
    ok I try it !

    thank you

  6. #6
    Join Date
    Mar 2004
    Posts
    18
    ok the table is created by your script but I still don't understand what is >>> , constraint validparent foreign key (parent) references Toys (id)

    there is no foreign key created

    I get 3 columns in my Table >>> Id, parent, name

    there is a + on the ID , but I don't get a list of keys and if I want to add an existing key it says that the jey allready exists

    I look for adjacency model ....

    txs

  7. #7
    Join Date
    Mar 2004
    Posts
    18
    ok I have understood how it works

    then I have 2 questions :-))

    how it works for inserting updating ?
    for >>>Id parent name

    and if my table Toy allready exists
    with >>>Id parent name
    Id is numauto in my case

    how can I add the constraint to parent ??

    thanlk you

  8. #8
    Join Date
    Mar 2004
    Posts
    18
    ok I get all .. it works

    but I have tried in sted of integer at to have an autonumber


    create table Toys
    ( id COUNTER constraint pkToys primary key
    , parent integer null
    , constraint validparent foreign key (parent) references Toys (id)
    , name varchar(50) not null
    )

    and I get an error !

    hpw can I get the key as autoincrement ?

    thank you

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your syntax works perfectly in access 97
    Last edited by r937; 04-08-04 at 16:09.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2004
    Posts
    18
    ooops ! yes ! it works !

    and just a small point :-))
    if I have allready this table

    create table Toys
    ( id COUNTER primary key
    , parent integer null
    , name varchar(50) not null
    )

    how can i add the constraint ? is it possible ?

    constraint pkToys
    , constraint validparent foreign key (parent) references Toys (id)


    txs again

Posting Permissions

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