Re: Hierarchal data

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
high-level).
[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.

    Cheers,
    douglas

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
>
>  http://www.sitepoint.com/article/1105/1
>
>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.
>