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.
My question is this: is there a way to get Postgresql to do this recursive
query for me?
My other question is how to get from a topics path to a topic node id. That is,
can someone suggest a way to find the topic id if you have a path like:
/top/Computers/Software/Operating_Systems/Open_Source/
Thanks,
--
Bill Moseley
moseley@hank.org