# Miscellaneous > Structured Query Language (SQL) >  hierarchical tables structure

## castali

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

----------


## rayvish

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.

----------


## martas

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.

----------


## cristian.babu

google "nested sets"

----------

