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 dmoz.org). 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 Moseley||Date: 2004-01-23 06:57:09|
|Subject: Re: Hierarchal data|
|Previous:||From: Tom Lane||Date: 2004-01-23 03:29:00|
|Subject: Re: HowTo configure and compile with openssl and jdbc on |