What is best way to store hierchical data in Mysql for more than one root?

Question

i have read so many blogs and links to save the hierarchical data in mysql database like nested set modal *Transitive Clousure Modal* Child Parent Hierchy . but i am little bit confuse can any body please suggest me what is the best method to store hierarchical for more than one root.

e.g
Root1
|
|---Child 1
|    |--Child 1 of 1
|    |--Child 2 of 2
|
Root 2
|    
|--Child 2
|    |--Child 1 of 2
|    |--Child 2 of 2

Thanks in adavance :)

Answer 1

When you use a table to store a hierarchy, each object in the hierarchy needs a parent. So your node might have these columns:

 nodeid    int not null not zero              the id of the node in this row
 parentid  int not null, but can be zero      the id the node's parent
 nodename  varchar                            the node's name
 etc etc.                                     other attributes of the node

With this table layout any parentless node (that is, any node with parentid = 0) is a root node. You can have as many of these in your table as your application requires.

The example you showed might be represented like this:

 nodeid  parentid  nodename
 ------  --------  --------
 1       0         Root1
 2       1         Child 1
 3       2         Child 1 of 1
 4       2         Child 2 of 1
 5       0         Root2
 6       5         Child 2
 7       6         Child 1 of 2
 8       6         Child 2 of 2