Re: Recursive join - blind alley?

Fletcher and Mikito

Thanks. Good ideas, but I'm pushing this forward. I know what you mean about table structure not always doing the whole job. But sometimes thinking aloud like this gets me forward.

I think the recursive join/BOM may be the way. Forget payments. If a musician is 'booked' for an 'element' (at last, I've found the right word) of a production, then they are ipso facto 'booked' for that elements parent, grandparent etc. So the idea that a musician can't be booked for Tuesday's show (child element) and for the week's run of the show (parent element) makes sense. We know they're booked for part/all of the week's run if they're booked for the Tuesday (+Wednesday etc.). As a matter of fact Joe Celko's nested sets might very well be a better solution here, as it looks easier to drill up/down the structure using standard SQL. There might be some quite complex triggers to write to enforce this though. Time will tell, but it's a worry at the back of my mind. A common 'use case' is the client books musicians for, let's say, a weeks run of a show before she has the details. e.g. - 'Mike, can I book you for Annie 15-22 of November, it'll be about £400 for the week but I haven't got the details yet'. In my scenario when the dates are known she (or the system) will have to unbook me for the week's run (which is now a parent element) and book me for each date. It might be easy or not.

But the payments is easier (at least at an ER model) that I thought. I simply group a set of 'musician booked for element' together into a payment group. So people may get paid at the 'parent' level, but only coincidentally. Other times the payment group may be completely unrelated to the element/sub-element structure. For instance a month long show, consisting of 4 single week runs, each consisting of 7 shows. That's the BOM type structure. But the trumpet player can't make any of the Tuesday shows. So she puts a 'dep' in. The dep's 4 tuesdays fees are 'grouped' into one payment. My mistake was that I was equating payments with the organisational structure of the thing.

I need to investigate this a lot further of course. My experience of finding an 'ideal' schema is that the implementation becomes a nightmare, un-updateable queries and so on. Or difficult deletions.

Yours comments, as always, most welcome.

Yours, Mike MacSween

"Fletcher Arnold" <fletch_at_home.com> wrote in message news:bt3o5v$ft$1_at_hercules.btinternet.com...
> "Mike MacSween" <mike.macsween.nospam_at_btinternet.com> wrote in message
> news: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
>
>
>
> If you're looking for another perspective - here's a different direction
to
> consider:
>
> Do not rely on the table structure to provide the information you
require -
> use a system of product codes and masking instead.
>
> Here are some product codes you might use:
>
> WT2004-XX-XX-XX-000 World Tour 2004
> WT2004-US-XX-XX-000 US leg of 2004 World Tour
> WT2004-US-NJ-HB-000 Hoboken Concert
> WT2004-US-NJ-HB-001 Hoboken Concert - Dress rehearsal
> WT2004-US-NJ-HB-002 Hoboken Concert - Show 1
> WT2004-US-NJ-HB-003 Hoboken Concert - Show 2
> WT2004-US-NJ-HB-101 Hoboken Concert - All shows incl. rehearsal
>
> WT2004-EU-GE-XX-000 Germany
> WT2004-EU-GE-HD-000 Heidelberg Concert
> WT2004-EU-GE-HD-003 Heidelberg Concert - Show 2
>
> This system allows you to code the products so you can retrieve the detail
> you need. For example, find all the payments for the 2004 world tour -
you
> just need WHERE ProductCode LIKE "WT2004*" or perhaps you need all years,
> all concerts in Germany - WHERE ProductCode LIKE "??????-EU-GE-??-???"
>
> I think you just need to accept there some things are too complicated to
be
> handled by table structure alone and some form of meaningful analysis
codes
> are needed. If you consider how a large supermarket might handle, say, a
> specially priced multi-pack of fruit juice. For pricing reasons, they
need
> a unique product code with a price, but for other reasons they may need to
> know that they sold a pack with a total volume of 4 litres. The coding of
> the product will let them analyse how much apple juice, orange juice, all
> juices, how many multi-packs etc have been sold but no use will be made of
> relational structures (eg you would not need tblMultiPack and tblItem).
>
> I hope you are able to take this idea and apply it to your database - it
> should make certain aspects a million times easier, more flexible and more
> in line with how products are traditionally categorized.
>
> </MyTuppenceWorth>
>
> Fletcher
>
>
Received on Fri Jan 02 2004 - 22:12:35 CET