Generally the method used to do this is to have a unique identifier for each node of the tree, and insert the identifier of the father node in the registration of the node.
The disadvantage of this technique is that one is obliged to make a SQL query for each node to retrieve all the son of a node. We can improve that by downloading all the nodes in the database, and rebuilding the tree by hand using an array indexed by parent node.
This technique is not really suitable for very large tree structures of several thousands of nodes.
So why don't trying by represent the tree differently, not as a tree, but as Nested Sets.
The Nested Set is an adjacent list realized in SQL as a tree.
The SQL for create the table is :
Selection of all nodes is very simple:
CREATE TABLE `tree` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR( 32 ) NOT NULL , `parentid` INT NOT NULL , `lft` INT NOT NULL , `rgt` INT NOT NULL , PRIMARY KEY ( `id` ))
SELECT n0.id FROM tree n0, tree n1 WHERE n1.id = 0 AND n0.lft BETWEEN n1.lft AND n1.rgt;
ASAP I will wrote a Java classes to maintain Nested Sets
keep in touch