Re: Database design for tree structure
- From: "Mark C. Stock" <mcstockX@Xenquery .com>
- Date: Sat, 24 Jun 2006 05:29:57 -0400
<wing0508@xxxxxxxxx> wrote in message
news:1151124216.568320.263160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
: 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
.
- References:
- Database design for tree structure
- From: wing0508
- Database design for tree structure
- Prev by Date: Re: mod_plsql / dad / character set problem
- Next by Date: External Tables and Datawarehouses
- Previous by thread: Database design for tree structure
- Next by thread: Re: Database design for tree structure
- Index(es):
Relevant Pages
|
