Re: Recursive join - blind alley?
Date: Fri, 2 Jan 2004 12:28:47 +0000 (UTC)
Message-ID: <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 - 13:28:47 CET