-
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
-
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.
-
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.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|