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

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

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

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

ok I try it !

thank you

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

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

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

your syntax works perfectly in access 97

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