Re: Tree structure

There are many ways of implementing a tree structure in an RDBMS, each approprate for differing requirements. The major concepts that have to be expressed are Node and Edge (the connection from 1 node to another). Other concepts may be needed such as Hierarchy (an instance of an entire tree structure).

The simplest, and least useful is the non-identifying self-referencing table. Here a row is a node and an edge at the same time.

More elaborate solutions may include two different node tables, (eg. for parts vs. sub-assemblies) two edge tables, a hierarchy table (to accomodate multiple tree structures), and an indirect edge table (which must be constructed through a cursor query) to enable the retrieval of all parents or children of a node, whether direct or indirect in one simple query.

Parker Shannon wrote:

> The solution requires two tables, a parent table and child table. Let's say
> I want to see the bill of materials structure for an auto.
>
> ITEM Table
> ID Name
>
> 01 Auto
> 02 Engine
> 03 Axle
> 04 Tires
> 05 Piston
> 06 Valve
>
> ITEM STRUCTURE Table
> ID ID Relationship Quantity
>
> 01 02 Component 1
> 01 03 Component 1
> 01 04 Part 4
> 02 05 Part 8
> 02 06 Part 32
>
> Dominic Yip wrote in message <34F65AA9.1C1A_at_netvigator.com>...
> >Is it an extended SQL or you have to do it thru store proc or some
> >kind of languages.
> >
> >Thanks
> >
> >Gary England wrote:
> >>
> >> Oracle has a ... CONNECT BY PREVIOUS ... START WITH ... clause that
> >> defines the parent/child relationships of columns with a single table.
> >> It must be a single table select... no joins or views.
> >>
> >> ªü¼Ö¥K wrote:
> >> >
> >> > Hi,
> >> > Does anyone know how to implement a tree data structure into RDBMS ?
> >> > and provide a grouping
> >> > query for any note within the tree.
> >> > e.g.
> >> > a
> >> > / \
> >> > b c
> >> > / \ / \
> >> > e f g h
> >> > ... ... ... ...
> >> > query for group b will included e, f ... etc.
> >> > thanks in advice.
> >> >
> >> > arnox,
> >> > 8-)
Received on Thu Mar 05 1998 - 00:00:00 CST