Re: Trees in SQL

Nis Jorgensen <nis_at_dkik.dk> wrote in message news:<6lm2iukcal9ctkeh7tf2hkjpj3dcbblm3f_at_4ax.com>...
> 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(!)

In case you want to give it a shot the following article might come in handy:

      Maintaining the transitive closure of graphs in SQL. 
       Guozhu Dong, Leonid Libkin, Jianwen Su and Limsoon Wong. 
       Int. Journal of Information Technology, 5 (1999), 46-78. 

You can find an electronic version on the page

  http://cm.bell-labs.com/who/libkin/publ.html

/Lennart Received on Wed Jul 03 2002 - 15:40:02 CEST