Re: Recursive join - blind alley?

"--CELKO--" <joe.celko_at_northface.edu> wrote in message news:a264e7ea.0401030806.496a2227_at_posting.google.com...
> >> I've done a fair bit of research. Which frequently brings up the
> words 'Joe Celko' and 'BOM'. <<
>
> Now I am obligated to provide a nested sets solution! Let me be
> sloppy and not put on all of the constraints for a tree:

Ah, I know how to tease them out!

> CREATE TABLE Events
> (event_name VARCHAR(35) NOT NULL,
> performance_nbr INTEGER NOT NULL, -- or date?
> performer_id INTEGER NOT NULL
> REFERENCES Performers (performer_id)
> ON UPDATE CASCADE,
actually might not be, probably more like a junction table between musicians and events

> fee DECIMAL(14,4) NOT NULL CHECK (fee >= 0.00),
> lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
> rgt INTEGER NOT NULL UNIQUE,
> CHECK (lft < rgt),
> PRIMARY KEY (event_name, performance_nbr, performer_id)
> );

> When you put in a node, put it in at the appropriate level in the
> tree. An arranger shows up at the highest event level for all
> performances (do they get paid by performance or by show? I don't know
> this trade).

Varies. Probably a 'buy-out'. We give you £2000 to write a set of arrangements for this tour, or anything else we want to use them for in the future.

> A nose flute player shows up at the lowest levels like
> "Elvis-2004 Tour; jail house rock number" and performances 1,2,3 and

It's actually getting quite hard to fix good nose flute players <g>

While you're there though. I want many trees in this forest. More than one root. How's that done? Start each one off at lft = max(rgt) + 1 (+margin of safety), rgt = lft+1?

Basically each tree will be relatively static. Certainly once a tour/series of events has passed there should be no need to add events. The depth of even the deepest tree wouldn't be much above 5 levels. And most (for this client) would be 2 - 1 root and 8 ish leaves.

Yours, Mike MacSween Received on Sat Jan 03 2004 - 22:24:42 CET