Re: Nested sets, BOM, with many roots?

"Mike MacSween" <mike.macsween.nospam_at_btinternet.com> wrote in message news:<3ff6b2d4$0$52888$5a6aecb4_at_news.aaisp.net.uk>...
> It's an orchestral management system.
>
> World Tour contains
> Country tours contain
> State/County tours contain
> City dates contain
> Rehearsals, Performances (actual 'events')
>
> Been poking around all this. I veer from adjacency lists to nested sets.
> Nested sets look a lot more amenable to SQL. The only way I can get the
> adjacency lists to do some of the things I want will be by constructing SQL
> in code (I think). But nested sets always seem to end up at one root.
> Whereas I want multiple roots. Elton John world tour contains this which
> contains that etc. etc. Berlin Philharmonic world tour contains this which
> contains that etc. etc. There, hopefully, won't be any cross contamination
> (!), i.e. a child will only have one parent, but I seem to have 2 root nodes
> there. Or else are the both held under one root which is 'the business'. In
> which case things like number-of-levels queries will always be one off.
>
> TIA, Mike MacSween

If you have a preference for adjacency list, but need to do aggregates over a subtree, or among ancestors, representing the transitive closure of parent child relation in a separate relation might do the job for you. You could check out:

  http://fungus.teststation.com/~jon/treehandling/TreeHandling.htm

It is a bit sloppy, but I think it will give you an idea of the idea. Regarding trees in db's there is a set of links at:

  http://troels.arvin.dk/db/rdbms/links/#hierarchical

HTH
/Lennart Received on Sat Jan 03 2004 - 23:09:51 CET