Re: Database design for tree structure

<wing0508_at_gmail.com> wrote in message
news:1151124216.568320.263160_at_r2g2000cwb.googlegroups.com...
: 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
:

oracle's CONNECT BY hierarchical query syntax handles this, including its ORDER SIBLINGS BY clause

you don't need path or depth -- that's derived data and would need to be recalculated for an entire branch when updating PARENT_ID

see http://www.psoug.org/reference/connectby.html for some examples or search tahiti.oracle.com for 'hierarchical queries'

++ mcs Received on Sat Jun 24 2006 - 04:29:57 CDT