Re: Database design for tree structure

wing0508_at_gmail.com wrote:
> 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

The table structure above:

id BIGINT

 > parentid BIGINT
 > name VARCHAR
 > depth INT
 > path VARCHAR

is not Oracle. What product are you using and what version of that product?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org