Re: Recursive join - blind alley?

Mike

after reading this thread and your 'hierachical thread', this is what I've put together this model

now I've setup an 'event' to have a (parentEvent, childSeq), but it could also be a nested set

thoughts ?

activity
  activityId (pk)
  activityName

ie. setup, teardown, dancing, stagehand, flute, drum, tourguide, copyright payment
++++++++

contactType
  contactTypeId (pk)
  contactType

ie. musician, agency, company
+++++

contactInfo
  contactInfoId (pk)
  contactName
  address
  phone
  fax
  email
  contactTypeId (fk)
  parentContactInfoId (fk)

ie. bill, anne, ford modelling agency, acme company,joe ++++++++

provider
  providerId (pk)
  contactInfoId (fk)
  unionId (fk)

ie. bill, anne, ford modelling agency, acme company +++++

providerActivity
  providerId (pk)
  activityId (pk)

ie. bill-setup, bill-teardown, anne-drum, acme company-dancing +++++++

union
  unionId (pk)
  unionName

ie. actra,
++++++

unionActivity
  unionId (pk)
  activityId (pk)
  flatRate
  hourlyRate

ie. actra-setup, actra-teardown, actra-musician +++++

providerUnion
  providerId (pk)
  unionId (pk)

ie. bill-actra, anne-actra
++++++++

location
  locationId (pk)
  locationName

ie. little Johnny's house, MGM Grand
+++++

locationContact
  locationId (pk)
  contactInfoId (pk)

ie. Johnny's mother, Johnny's Father, MGM Grand general manager ++++++

event
  eventId (pk)
  eventName
  startDateTime
  endDateTime
  contactInfoId (fk)
  locationId (fk)
  parentEventId (uk) (or nested set)
  childSequence (uk)

ie. world tour, US leg, state tour, NewJersey appearance, dress rehersal, first

show, Johnny's birthday party,
++++

eventActivity
  eventId (pk)
  eventSeq (pk)
  activityId (fk)
  providerCount

ie. world tour-scheduling, first show-dancing, first show-drum ++++

eventActivityStaffing
  eventId (pk)
  eventSeq (pk)
  activityId (pk)
  contactInfoId (fk)

ie. first show-dancing-joe (acme company employee), first show-drum-anne
+++++

agreement
  agreementId (pk)
  providerId (fk)
  eventId (fk)
  agreementDate

ie. anne-first show, acme company-first show ++++

agreementDetail
  agreementId (pk)
  detailId (pk)
  activityId (fk)
  startDateTime
  endDateTime
  flatRate
  hourlyRate
  status (negotiation, booked, completed, paid)

ie. anne-first show-drum
++++

"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
Received on Tue Jan 06 2004 - 12:06:31 CET