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.
>