Re: How to express a b-tree in SQL?

From: Thomas Muller <ttm_at_nextra.com>
Date: 2000/02/22
Message-ID: <NBws4.4700$kz6.86204_at_news1.online.no>#1/1

George hart <hart_at_rohan.sdsu.edu> wrote in message news:38B08B7E.D2C4721_at_rohan.sdsu.edu...
> Hi,
>
>
> I was wondering if anybody has an opinons on the best way to express
> a binary tree data structure in a relational table. Also if anybody
> could point me to a book or online document which discusses how to do
> this I would be very interested.
>

CREATE TABLE node (

    node_id NUMBER PRIMARY KEY,
    parent NUMBER REFERENCE node(node_id), // is NULL for root

    left         NUMBER REFERENCES node(node_id), // may be null
    right       NUMBER REFERENCES node(node_id), // may be null

    data_id NUMBER REFERENCES data(data_id) NOT NULL );

CREATE TABLE data (

    data_id NUMBER PRIMARY KEY,
    <whatever>
);

I'm not sure how you will manipulate the tree effectively in standard SQL, but if you use ORACLE you might want to utilize the CONNECT clause.

--

Thomas

Received on Tue Feb 22 2000 - 00:00:00 CET