Re: Hierarchal data

I didn't receive much feedback from this post.  Would psql-general be a 
better list to post this question?  Or is there a better place to ask a 
general database design question?

Thanks,

On Thu, Jan 22, 2004 at 05:28:09PM -0800, 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.
> 
> 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@xxxxxxxx
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
Bill Moseley
moseley@xxxxxxxx


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx