Re: Trees in SQL

On 1 Jul 2002 15:57:06 -0700, ray7866_at_yahoo.com (Raymond W.) wrote:

>
>The scope of my project is to know at each entity level how much cash
>is on hand at each level taking into consdieration all the subs
>underneath. For example, Sub1b would just be its own cash of 450.
>However, Sub1 would be 1010 (80%x450+50%x300+100%x300+own cash of
>200). I would need this report for each sub.
>
>I have looked into "nested sets" that Celko proposes:
>http://www.intelligententerprise.com/001020/celko.shtml
>but I have problems figuring how to apply the ownership % and also how
>to deal with the split ownership (how does the "worm go around the
>box" in this case).

The first important thing here is: You are not dealing with a tree, but with a "directed acyclic graph". Thus Celko's nested sets are not really something for you.

My suggestion would be to use an additional transitive closure table, updated by triggers on the original table as well as on itself(!)

-- 
Nis Jorgensen
Amsterdam

Please include only relevant quotes, and reply below the quoted text. Thanks