Scott,
> NODES__________________________________
> ParentID Varchar(20)
> ParentType varchar(20)
> ChildID varchar(20)
> ChildType varchar(20)
This looks difficult... mainly becuase, in your shema, a "parent" node
can be its own child or grandchild, resulting in an endless loop.
If that was not your intention, you may wish to think of using a
different structure.
There are two main ways for you to approach this problem in 7.2.x:
1) Buy Joe Celko's "SQL for Smarties" and read up on pure-SQL tree
implementations, which he covers in far more depth than I want to here;
2) Explore the tree module in /contrib in your Postgresql source.
I understand from Joe Conway that in 7.3, you will have another choice,
as Postgres will offer support for Oracle's tree-like "IS CONNECTED BY"
expression.
Good luck!
-Josh Berkus
P.S. also, there's some articles on tree structures up at techdocs:http://techdocs.postgresql.org/