Skip site navigation (1) Skip section navigation (2)

Re: Hierarchal data

From: Douglas Trainor <trainor(at)uic(dot)edu>
To: Bill Moseley <moseley(at)hank(dot)org>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Hierarchal data
Date: 2004-01-23 06:32:49
Message-ID: (view raw or whole thread)
Lists: pgsql-novice
You have a fundamental problem if you want to go from high-level
to low-level if you only store the parent_id (from low-level to 
[in a booming voice]:  Feed your head.  Good luck. 

Ignoring databases for a moment, if you want to go both ways, you
need a doubly-linked-list.  Back in the day, of course, we would try
and minimize storage and use a tricky XOR scheme.

In any case, throw an example on paper and see why your scheme
will not work.  You need a better reference than SITEPOINT for
what you want to do... What they say does not apply to you.


Bill Moseley wrote:

>I realize this is a classic problem, but I'm a NOVICE after all.
>I want to represent hierarchal topics (just like  I've seen
>two ways to represent the data.  Both are described at
>And in another article by Joe Celko about using Modified Preorder Trees.
>I'm leaning toward using the simpler "adjacency list model" where each
>node (topic) in the tree just lists its parent.
>    create table topic (
>        topic_id    serial PRIMARY KEY,
>        name        varchar(64),
>        parent_id   int  -- possible to use "REFERENCES topic" but allow NULL?
>    )
>The problem becomes then how to find the path from a given node to the
>root node.  I'm working with perl and currently what I'm doing is a
>recursive call to the database.  That's going to be slow if I have to
>look up many of those.

In response to


pgsql-novice by date

Next:From: Bill MoseleyDate: 2004-01-23 06:57:09
Subject: Re: Hierarchal data
Previous:From: Tom LaneDate: 2004-01-23 03:29:00
Subject: Re: HowTo configure and compile with openssl and jdbc on

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group