Recursive join - blind alley?
Date: Fri, 2 Jan 2004 08:53:47 -0000
Message-ID: <3ff5319b$0$52881$5a6aecb4_at_news.aaisp.net.uk>
Regular viewers may want to turn off now.
This will be an orchestral management system. Musicians and other staff being booked/paid for jobs.
A job may contain other jobs, e.g:
World Tour contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
New Jersey tour contains Hoboken concert (and others)
Hoboken concert contains dress rehearsal, 1st show, 2nd show
Or a job may be single:
My band plays at Simon Foreman's barmitzvah
To account for the variability I imagined a recursive join. I've done a fair
bit of research. Which frequently brings up the words 'Joe Celko' and 'BOM'.
I'm not sure that the BOM is exactly what I need, but it's close. I actually
think an adjacency list is a better solution in this case than nested sets,
though probably implemented with (at least) 2 tables. This area may or may
not be the problem. As a matter of fact the same problems would arise, I
think, with a static structure, e.g:
Production-<Events
In the 'real world', the person I'm doing this for, a musician may be booked for a show, will need to be booked for some/all of the events in that show, but may be paid at the show level. i.e. they are booked for 7 shows, but are paid a total of $548.34 for all shows together. Involved at a 'child' level but paid at a 'parent' level. Here there seems to be duplication, we KNOW they are involved with the parent if they are involved with any of the children, and so on all the way up the hierachy.
Some people may be involved with a parent but NOT it's children. An arranger writes the orchestrations for a week long show, but doesn't turn up on the shows. So there is no certainty that people can only be involved at the lowest child level.
But if on some occasions people are involved at a child level and no parent level (paid by the individual show) whereas on others they are involved with both (booked for the gigs, paid by the week) there's a difference, sometimes there are duplicate data, sometimes there aren't.
OK, if it's just payment that's the problem, spin if off into 2 junction tables:
Event -<Fee>-Musician
Event-<Booked>-Musician
That separates the two things that are getting muddled. But this now looks strange. People are now getting paid for jobs they might not be involved in. How so? It would actually accomodate copyright payments and suchlike (which aren't part of the requirements) but it still looks strange. And I suspect complex to implement.
I realise this isn't the first time I've asked for help on this, but I have done a huge amount of pondering/studying and am asking for help because I think I may have thought myself into a corner. Another perspective would be valued.
TIA, Mike MacSween Received on Fri Jan 02 2004 - 09:53:47 CET