Database design for tree structure

Dear All

I have designed a database which for tree structure.

the table design as like as follow

id BIGINT
parentid BIGINT
name VARCHAR
depth INT
path VARCHAR

the record as like as follow
id parentid name depth path

1   0         -      0      .1.
2   1         food   1      .1.2.
3   1         toy    1      .1.3.
4   2         noodle 2      .1.2.4.
5   2         game   2      .1.3.5.

The above example can reduce the retrieval process For example, I need can the nodes under the root (id = 1), than I can using single sql statement to retrieve all nodes by using the field "path"
If I want to get the nodes under the food (id = 2), than I can using single sql statement to retrieve all nodes by using the field "path" again.

However, the above structure cannot handle the sorting, if i want to sort by name, it cannot be done, any design than can reduce the complexity of sql statement but still can sorting by using the name?

Thanks Received on Fri Jun 23 2006 - 23:43:36 CDT