By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,784 Members | 3,274 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,784 IT Pros & Developers. It's quick & easy.

Time dependant PK?

P: n/a
Or something to do with Nulls in PK. Which is obviously wrong.

OK, after a diversion of having to earn money its back to the free one. The
orchestral management system.

At the E-R level:

An event (concert etc.), a player (of musical instruments), an ensemble
(orchestra, band et al), an instrument.

These four things combined, a player (one single human being), playing one
or more musical instruments, as part of an ensemble (possibly of one), at an
event. Lets call that a performance (not the best term, but it'll do).

That entity, performance, seems to have a good identifier -
player,ensemble,event. It might even have a surrogate key, 'musician'. Those
of you with orchestral knowledge might have suggested desk, as I did for a
while, till I remembered that there are 2 string players to a desk. But the
surrogate key values would be something like '1st Clarinet', '2nd Clarinet'
etc. The PK/SK would be used as part of a PK in a junction table -
musicianInstrument - with a table Instrument. Because a musician may play
more that one instrument at a concert.

All well and good I think. At last I've cracked this corner of the model.

So the user is starting work. For this concert she needs 1st Violin 1/1
(player 1 on the 1st desk, the leader), 1st Violin 2/1, 1st Clarinet, etc.
etc. Each of those labels will be unique, and could be thought of as a
surrogate key. Whatever happends the PK of player+event+ensemble seems very
very right. At any particular musical event one player can only be playing
once as part of a particular ensemble. But she doesn't know the player yet.
There will be one. I've tested it logically on the extreme cases. The first
clarinet gets mown down by a bus on the way to the gig. In which case the
first clarinet record is deleted from the database - there wasn't a first
clarinet player in that chair that night. And so on.

But how do I cope with the fact that:

1. I want the database engine to enforce RI

2. I want to use what seems to be a very obvious natural key (even if I use
a genuine autonumber as a surrogate)

3. One field of the PK isn't known at data entry time.

I had a search of the archives. Too many arguments over autonumbers for me!

One way forward I considered:

Performance is actually, event/ensemble with a PK of musician (as in 1st
Clarinet, 2nd Clarinet etc.) which is posted out as a FK to the
musician/instruments table. And there is a junction table between
performance and player - called it booking. Which is one to many from player
to booking, but one to one from performance to booking. And the PK of that
is a combination of the FKs. So a player might be booked more than once for
various performance, but....damn, no that doesn't work, because the player
can then be booked more than once for different events.

Any ideas?

TIA, Mike MacSween

Nov 12 '05 #1
Share this Question
Share on Google+
43 Replies


P: n/a
On Wed, 3 Dec 2003 07:14:20 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:

[snippage throughout]
An event (concert etc.), a player (of musical instruments), an ensemble
(orchestra, band et al), an instrument.

These four things combined, a player (one single human being), playing one
or more musical instruments, as part of an ensemble (possibly of one), at an
event. Lets call that a performance (not the best term, but it'll do).
Ok. You might think about whether

<Some word I can't come up with>
-> musician
-> ensemble

is logicially similar to

Parties (supertype)
-> Persons (subtype)
-> Organizations (subtype)
That entity, performance, seems to have a good identifier -
player,ensemble,event.
If I've understood you so far, I'd think {ensemble, event name, event
date}. (Actually, I think you need "ensemble's" supertype.) Something
like "Mike's Wind Quintet", "Christmas with Mike's Winds" (an annual
event), and "2003-12-20" (this year's event). I imagine that
sometimes you know when the event will be, but you don't know who will
play.
But the
surrogate key values would be something like '1st Clarinet', '2nd Clarinet'
etc.
I think you mean "candidate key". But I'm not sure.
The PK/SK would be used as part of a PK in a junction table -
musicianInstrument - with a table Instrument. Because a musician may play
more that one instrument at a concert.
I think I'm with you so far.
All well and good I think. At last I've cracked this corner of the model.

So the user is starting work. For this concert she needs 1st Violin 1/1
(player 1 on the 1st desk, the leader), 1st Violin 2/1, 1st Clarinet, etc.
etc. Each of those labels will be unique, and could be thought of as a
surrogate key. Whatever happends the PK of player+event+ensemble seems very
very right.
I think I follow you, but I'm not sure I agree with you yet. It seems
to me like players are members of an ensemble much like persons are
employees of an organization. (Where "persons" and "organizations" are
subtypes of the supertype "parties".)
At any particular musical event one player can only be playing
once as part of a particular ensemble.

[big snip, as we wait for more info]

But the player might play more than one instrument as part of a
particular ensemble. (Percussionists play lots of instruments. I
always wanted to be a percussionist.)
--
Mike Sherrill
Information Management Systems
Nov 12 '05 #2

P: n/a

On Wed, 03 Dec 2003 16:54:17 -0500, Mike Sherrill
<MS*************@compuserve.com> wrote in comp.databases.ms-access:
At any particular musical event one player can only be playing
once as part of a particular ensemble.

[big snip, as we wait for more info]

But the player might play more than one instrument as part of a
particular ensemble. (Percussionists play lots of instruments. I
always wanted to be a percussionist.)


Yes, so perhaps Mike (MacSween) meant that a musician plays just one
instrument at any given moment, although multiple instruments may well
be played during a given piece. But then where would that leave Steve
Turre? Perhaps another component of the pk should be body parts, and
even parts of body parts (ie left/right sides of one's mouth in
Steve's case)?

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051
Nov 12 '05 #3

P: n/a
"Mike MacSween" <mi******************@btinternet.com> wrote in
news:3f***********************@news.aaisp.net.uk:
Or something to do with Nulls in PK. Which is obviously wrong.

OK, after a diversion of having to earn money its back to the free
one. The orchestral management system.

At the E-R level:

An event (concert etc.), a player (of musical instruments), an
ensemble (orchestra, band et al), an instrument.

These four things combined, a player (one single human being), playing
one or more musical instruments, as part of an ensemble (possibly of
one), at an event. Lets call that a performance (not the best term,
but it'll do).

That entity, performance, seems to have a good identifier -
player,ensemble,event. It might even have a surrogate key, 'musician'.
Those of you with orchestral knowledge might have suggested desk, as I
did for a while, till I remembered that there are 2 string players to
a desk. But the surrogate key values would be something like '1st
Clarinet', '2nd Clarinet' etc. The PK/SK would be used as part of a PK
in a junction table - musicianInstrument - with a table Instrument.
Because a musician may play more that one instrument at a concert.

All well and good I think. At last I've cracked this corner of the
model.

So the user is starting work. For this concert she needs 1st Violin
1/1 (player 1 on the 1st desk, the leader), 1st Violin 2/1, 1st
Clarinet, etc. etc. Each of those labels will be unique, and could be
thought of as a surrogate key. Whatever happends the PK of
player+event+ensemble seems very very right. At any particular musical
event one player can only be playing once as part of a particular
ensemble. But she doesn't know the player yet. There will be one. I've
tested it logically on the extreme cases. The first clarinet gets mown
down by a bus on the way to the gig. In which case the first clarinet
record is deleted from the database - there wasn't a first clarinet
player in that chair that night. And so on.

But how do I cope with the fact that:

1. I want the database engine to enforce RI

2. I want to use what seems to be a very obvious natural key (even if
I use a genuine autonumber as a surrogate)

3. One field of the PK isn't known at data entry time.

I had a search of the archives. Too many arguments over autonumbers
for me!

One way forward I considered:

Performance is actually, event/ensemble with a PK of musician (as in
1st Clarinet, 2nd Clarinet etc.) which is posted out as a FK to the
musician/instruments table. And there is a junction table between
performance and player - called it booking. Which is one to many from
player to booking, but one to one from performance to booking. And the
PK of that is a combination of the FKs. So a player might be booked
more than once for various performance, but....damn, no that doesn't
work, because the player can then be booked more than once for
different events.

Any ideas?

TIA, Mike MacSween


I think your issue lies in the choice of player instead of instrument in
the PK.

Consider the performance as follows: When you desire to produce a
performance, you know that you need specific instruments (e.g. First
Violin), That is what is in the score ( assuming you are talking formal
music and not a jam session), not the person who plays it.

Bob
Nov 12 '05 #4

P: n/a
"Mike Sherrill" <MS*************@compuserve.com> wrote in message
news:5j********************************@4ax.com...

Thanks for replying Mike.

The person has 2 advantages with me doing this:

1. I'm not charging her.

2. I know this business backwards.
On Wed, 3 Dec 2003 07:14:20 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:

[snippage throughout]
An event (concert etc.), a player (of musical instruments), an ensemble
(orchestra, band et al), an instrument.

These four things combined, a player (one single human being), playing oneor more musical instruments, as part of an ensemble (possibly of one), at anevent. Lets call that a performance (not the best term, but it'll do).
Ok. You might think about whether

<Some word I can't come up with>
-> musician
-> ensemble

is logicially similar to

Parties (supertype)
-> Persons (subtype)
-> Organizations (subtype)


No, I don't think so.
That entity, performance, seems to have a good identifier -
player,ensemble,event.
If I've understood you so far, I'd think {ensemble, event name, event
date}. (Actually, I think you need "ensemble's" supertype.) Something
like "Mike's Wind Quintet", "Christmas with Mike's Winds" (an annual
event), and "2003-12-20" (this year's event). I imagine that
sometimes you know when the event will be, but you don't know who will
play.


The actual number attributes of the entity 'ensemble' will be fairly small.
The agent/manager, the name, the style of music. Most other things are
potentially variable (though won't always be). Differing numbers of
musicians playing differing numbers and types of instruments.
But the
surrogate key values would be something like '1st Clarinet', '2nd Clarinet'etc.


I think you mean "candidate key". But I'm not sure.


Neither am I, but I think your right.
The PK/SK would be used as part of a PK in a junction table -
musicianInstrument - with a table Instrument. Because a musician may play
more that one instrument at a concert.


I think I'm with you so far.
All well and good I think. At last I've cracked this corner of the model.

So the user is starting work. For this concert she needs 1st Violin 1/1
(player 1 on the 1st desk, the leader), 1st Violin 2/1, 1st Clarinet, etc.etc. Each of those labels will be unique, and could be thought of as a
surrogate key. Whatever happends the PK of player+event+ensemble seems veryvery right.


I think I follow you, but I'm not sure I agree with you yet. It seems
to me like players are members of an ensemble much like persons are
employees of an organization. (Where "persons" and "organizations" are
subtypes of the supertype "parties".)


No, definitely not. This is completely freelance business. 7th November my
band had me, Rebecca, Kate, Barry, Nigel and Tor. 22nd November it had me,
Rebecca, Kate, Sean, Nigel and Andy. 21st of June it had Rebecca, Anji,
Gordon, Paul Sean and Alan (INSTEAD of me!).
At any particular musical event one player can only be playing
once as part of a particular ensemble.

[big snip, as we wait for more info]

But the player might play more than one instrument as part of a
particular ensemble. (Percussionists play lots of instruments. I
always wanted to be a percussionist.)


Yes, I know, that's what I'm modeling. Percussion is actually a complete
PITA. Most 'doubles', where a player plays more than once instrument
generally only involve an extra 1 or 2 instruments. Clarinet doubling flute,
Trumpet doubling flugel horn. A percussionist, especially in a pit band
might be playing vibes, glock, xylo, wood blocks, rattle, tubular bells,
tambourine, tam tam, cymbals etc. etc. That's not including kit or timps. So
generally 'percussion' is treated as one instrument, and we all accept it's
a group of instruments. Sometime timps is counted as a seperate instrument,
esp in symphonic orchestras where the timp player won't sully themselves
banging a tambourine. And sometimes the rest is divided up into kit, tuned
and untuned. So I may well not include percussion. But I'm sure if I get
this data model right I could do, if I wanted.

Since I posted this I've come up with a new ER. I'll post tomorrow. Now is
too tired.

Cheers, Mike
Nov 12 '05 #5

P: n/a
"Bob Quintal" <Rq******@pants.sympatico.ca> wrote in message
news:11******************************@news.teranew s.com...
[]
I think your issue lies in the choice of player instead of instrument in
the PK.
I was being inconsistent with my terminology. I'm actually only at the ER
model stage, so no PKs or similar. I'll post again
Consider the performance as follows: When you desire to produce a
performance, you know that you need specific instruments (e.g. First
Violin), That is what is in the score ( assuming you are talking formal
music and not a jam session), not the person who plays it.


Yes, you've understood perfectly. As a matter of fact it wouldn't matter
what the musical occasion was, so long as it part of the requirements is
that information about the people and instruments who took part in the
musical occasion needs to be recorded.

But there is a distinction in terminology. First Violin is not at
Instrument. It is a role in a musical ensemble. And the person fulfilling
that role will (probably) play an instrument called a Violin in fulfilling
that role. I'll give a better example. The right hand player sitting at the
first desk of the first violins is the Orchestra Leader. So in this
application would have the role of 'Leader'. He or she invariably (in a
symphony orchestra) plays a violin. So the 'role' name is not related,
necessarily, to the physical instrument played. In a theatre pit band there
are roles like Reed 1, Reed 2, etc. Those roles will require instruments
like Bflat Clarinet, A Clarinet, Flute, Tenor Saxophone and so on. But will
be different depending whether the role is Reed 1 in an ensemble providing
the music for a production of The Sound of Music, or for a production of
Crazy For You.

Percussion is the same, in spades. My approach to percussion is to give them
a big box of music, tell them to bring every instrument they own, and sort
it out themselves. I remember seeing Frank Sinatra at a concert in Dublin.
The chap I was with (not a musician) said 'that bloke at the back, he
doesn't know what he's doing, look at him running about all over the place'.
I said, 'no Lloyd, he's just the percussionist!

Mike MacSween

Nov 12 '05 #6

P: n/a
"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:ek********************************@4ax.com...
[]
Yes, so perhaps Mike (MacSween) meant that a musician plays just one
instrument at any given moment, although multiple instruments may well
be played during a given piece.
Yes, that's it.
But then where would that leave Steve
Turre? Perhaps another component of the pk should be body parts, and
even parts of body parts (ie left/right sides of one's mouth in
Steve's case)?


Oh believe me, I've considered it! But somehow I think the ROI may not be
sufficient!

Mike MacSween
Nov 12 '05 #7

P: n/a
OK, I was being inconsistent.

At this stage I have an ER model I'm developing, so shouldn't be talking PKs
and stuff.

I've refined it.

Can't do an ER diagram here so will just have to do a text description of
the entities. There may well be missing attributes.

Entities:

1. Ensemble (EnsembleName (identifier), EnsembleType, EnsembleManager,...)
2. Event (EventName (identifier), EventLocation, EventDate, EventStartTime,
EventFinishTime, EventBooker,...)
3. Performance (EnsembleName, EventName (combined identifier),
EnsembleFee,...)
4. Person (PersonId, LastName, FirstName,...)
5. Instrument (InstrumentName (identifier),...)
6. Player (InstrumentName, PersonId (combined identifier), AbilityLevel,...)
7. Seat (EnsembleName, EventName, SeatName (combined identifier),
PersonId,...)

Relationships:

TakesPartIn: one (optional) to many (mandatory) Ensemble to Performance
Includes: one (? time dependant) to many (mandatory) Event to Performance
Uses: one (mandatory) to many (mandatory) Performance to Seat
IsBookedAs: one (optional) to many (? time dependant, this is the query)
Person to Seat
Plays: one (optional) to many (mandatory) Person to Player
IsPlayedBy: one (optional) to many (mandatory) Instrument to Player
IsNeededBy: many (mandatory) to many (optional) Seat to Instrument

So in this model there are constraints which I would like to state, which
can't be represented by either the ER diagram (which we haven't got, but if
you can be bothered you can construct from above) or by the statement of
Entity types:

1. Once the date of the event has passed then the relationship IsBookedAs
becomes mandatory for Seat
2. Once the date of the event has passed then the relationship Includes
becomes mandatory for Event

Are those 2 constraints valid, or do they result from a mistake in the
model?

So although this is the early stage of the design process, and I am
deliberately avoiding going on to a relational model just for now, I am
thinking forward at little. Namely how will those 2 constraints be handled
in implementation?

Yours, Mike MacSween
Nov 12 '05 #8

P: n/a
On Thu, 4 Dec 2003 11:19:46 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:
OK, I was being inconsistent.

At this stage I have an ER model I'm developing, so shouldn't be talking PKs
and stuff.

I've refined it.

Can't do an ER diagram here so will just have to do a text description of
the entities. There may well be missing attributes.

Entities:

1. Ensemble (EnsembleName (identifier), EnsembleType, EnsembleManager,...)
2. Event (EventName (identifier), EventLocation, EventDate, EventStartTime,
EventFinishTime, EventBooker,...)
3. Performance (EnsembleName, EventName (combined identifier),
EnsembleFee,...)
4. Person (PersonId, LastName, FirstName,...)
5. Instrument (InstrumentName (identifier),...)
6. Player (InstrumentName, PersonId (combined identifier), AbilityLevel,...)
7. Seat (EnsembleName, EventName, SeatName (combined identifier),
PersonId,...)

Relationships:

TakesPartIn: one (optional) to many (mandatory) Ensemble to Performance
Includes: one (? time dependant) to many (mandatory) Event to Performance
Uses: one (mandatory) to many (mandatory) Performance to Seat
IsBookedAs: one (optional) to many (? time dependant, this is the query)
Person to Seat
Plays: one (optional) to many (mandatory) Person to Player
IsPlayedBy: one (optional) to many (mandatory) Instrument to Player
IsNeededBy: many (mandatory) to many (optional) Seat to Instrument

So in this model there are constraints which I would like to state, which
can't be represented by either the ER diagram (which we haven't got, but if
you can be bothered you can construct from above) or by the statement of
Entity types:

1. Once the date of the event has passed then the relationship IsBookedAs
becomes mandatory for Seat
2. Once the date of the event has passed then the relationship Includes
becomes mandatory for Event

Are those 2 constraints valid, or do they result from a mistake in the
model?

So although this is the early stage of the design process, and I am
deliberately avoiding going on to a relational model just for now, I am
thinking forward at little. Namely how will those 2 constraints be handled
in implementation?

Yours, Mike MacSween


Nov 12 '05 #9

P: n/a
On Thu, 04 Dec 2003 07:17:31 -0700, Tom van Stiphout
<to*****@no.spam.cox.net> wrote:

Oops, hit the wrong button.
Sorry Mike, I don't really have anything to add, other than "then
you'll have to implement it in code".

<clip>
Nov 12 '05 #10

P: n/a
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
But there is a distinction in terminology. First Violin is not at
Instrument. It is a role in a musical ensemble. And the person
fulfilling that role will (probably) play an instrument called a
Violin in fulfilling that role. I'll give a better example. The
right hand player sitting at the first desk of the first violins
is the Orchestra Leader. So in this application would have the
role of 'Leader'. He or she invariably (in a symphony orchestra)
plays a violin. So the 'role' name is not related, necessarily, to
the physical instrument played. In a theatre pit band there are
roles like Reed 1, Reed 2, etc. Those roles will require
instruments like Bflat Clarinet, A Clarinet, Flute, Tenor
Saxophone and so on. But will be different depending whether the
role is Reed 1 in an ensemble providing the music for a production
of The Sound of Music, or for a production of Crazy For You.


So, for each piece, you need a table that maps the "roles," as
you're calling it. That would be a join table between the
particular piece and the list of possible roles. Since pieces are
in genres that have different expected roles, you'd probably want
to filter the list of possible roles to make data entry easier. For
instance, if the piece is a symphonic piece, you'd want one list of
roles, if a pit band for a Broadway musical, a different set of
roles.

So, I'd see something like this:

tblConcert -- dates, times, venue, contractor name, contact infor.,
etc.

tblPiece -- a work performed on the concert, with its "genre" as an
attribute.

tblRole -- lookup table of musician roles, with a unique key on
genre/role.

tblPieceRole -- a map of the roles for each piece, probably should
have a surrogate key for connection to the next table.

tblPieceMusician -- the join table that connects the piece to the
musicians. The question is: should it connect to the surrogate PK
of tblPieceRoles or to tblPiece? I think the former, though the
table is named more to be understandable than to show what it
actually does.

tblMusician -- the obvious, of course.

Now, the hard part is ease of use. The structure above requires
that you enter every piece on a program and then the
instrumentation and then the musicians. Many programs have a single
instrumentation for the whole thing, so you really only need one
entry. Or, you need an easy method to copy the Role map and the
musicians from one piece to another.

Sound right?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #11

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message []

I thought you'd never get here David!
So, I'd see something like this:

tblConcert -- dates, times, venue, contractor name, contact infor.,
etc.

tblPiece -- a work performed on the concert, with its "genre" as an
attribute.

tblRole -- lookup table of musician roles, with a unique key on
genre/role.

tblPieceRole -- a map of the roles for each piece, probably should
have a surrogate key for connection to the next table.

tblPieceMusician -- the join table that connects the piece to the
musicians. The question is: should it connect to the surrogate PK
of tblPieceRoles or to tblPiece? I think the former, though the
table is named more to be understandable than to show what it
actually does.

tblMusician -- the obvious, of course.

Now, the hard part is ease of use. The structure above requires
that you enter every piece on a program and then the
instrumentation and then the musicians. Many programs have a single
instrumentation for the whole thing, so you really only need one
entry. Or, you need an easy method to copy the Role map and the
musicians from one piece to another.

Sound right?


Perhaps. But I need an instrument entity. I probably wouldn't actually
reduce the program to piece detail. Because it's too complex and variable.
Excepting that my outline of Events, as I'm calling them, is so far based
upon a recursive relationship concept.

Event (World Tour) contains Events (US leg of tour, European leg), and so on
down to 'Rehearsal with repetiteur'. So each piece in a concert could be a
sub event. But I don't think I'll get there.

This is all getting a bit difficult to visualise. Give me a few hours and
I'll find a bit of web space and bung a pretty ER diagram on it.

Yours, Mike MacSween
Nov 12 '05 #12

P: n/a
http://www.btinternet.com/~mike.macsween/

something like that. 'Sound' is a crap entity name. It' basically the
individual instruments that will be required on the gig.

Mike
Nov 12 '05 #13

P: n/a
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
http://www.btinternet.com/~mike.macsween/

something like that. 'Sound' is a crap entity name. It' basically
the individual instruments that will be required on the gig.


Three questions:

1. why a direct relationship between Seat and Performance, when the
indirect relationship is already there via Sounds? How can you
insure that the two relationships are not in conflict?

2. why not collapse Sounds and Seat? I'm guessing that Sounds means
something like "violin I" and seat is "desk 1 seat b." Otherwise, I
can't see any reason for maintaining separate tables. This is the
kind of normalizations I'd avoid, and collapse these two relations
into a single table, with repetition of data, simply in order to
keep the releationships simpler.

3. why is person linked to Seat and not Player? How do you know
*which* instruments the person is playing at that seat? All of the
ones implied by Player records? Or a limited number? A one-to-many
between player and seat would give you exact information on that.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #14

P: n/a
I've posted a new one. Have a look at that.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
http://www.btinternet.com/~mike.macsween/

something like that. 'Sound' is a crap entity name. It' basically
the individual instruments that will be required on the gig.


Three questions:

1. why a direct relationship between Seat and Performance, when the
indirect relationship is already there via Sounds? How can you
insure that the two relationships are not in conflict?

2. why not collapse Sounds and Seat? I'm guessing that Sounds means
something like "violin I" and seat is "desk 1 seat b." Otherwise, I
can't see any reason for maintaining separate tables. This is the
kind of normalizations I'd avoid, and collapse these two relations
into a single table, with repetition of data, simply in order to
keep the releationships simpler.

3. why is person linked to Seat and not Player? How do you know
*which* instruments the person is playing at that seat? All of the
ones implied by Player records? Or a limited number? A one-to-many
between player and seat would give you exact information on that.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #15

P: n/a
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
I've posted a new one. Have a look at that.


There seem to be no graphics in it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #16

P: n/a

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
I've posted a new one. Have a look at that.


There seem to be no graphics in it.


http://www.btinternet.com/~mike.macsween/

seemed OK just now. Assuming I haven't got some sort of local cache

Mike
Nov 12 '05 #17

P: n/a
Mike MacSween wrote:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
I've posted a new one. Have a look at that.


There seem to be no graphics in it.


http://www.btinternet.com/~mike.macsween/

seemed OK just now. Assuming I haven't got some sort of local cache

Mike


Mike,

FYI, I tried to load the page and got no images either.

--

Peter Palmieri
Nov 12 '05 #18

P: n/a
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78. ..
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
>I've posted a new one. Have a look at that.


There seem to be no graphics in it.


http://www.btinternet.com/~mike.macsween/

seemed OK just now. Assuming I haven't got some sort of local
cache


What in the hell are you using to produce the web page? It looks
like a Word document or something, saved as horrendously verbose
XML.

Sorry, but it doesn't work in any browser but IE, which I don't use
because of its vast inferority (not least of which being all the
security holes).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #19

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74.. .
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78. ..
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:

>I've posted a new one. Have a look at that.

There seem to be no graphics in it.


http://www.btinternet.com/~mike.macsween/

seemed OK just now. Assuming I haven't got some sort of local
cache


What in the hell are you using to produce the web page? It looks
like a Word document or something, saved as horrendously verbose
XML.


Yep. That's what I did. I've done it as a graphic but can't seem to FTP up
to the server now. I'll resume when I've got that going.

Mike
Nov 12 '05 #20

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
http://www.btinternet.com/~mike.macsween/

something like that. 'Sound' is a crap entity name. It' basically
the individual instruments that will be required on the gig.
Three questions:

1. why a direct relationship between Seat and Performance, when the
indirect relationship is already there via Sounds? How can you
insure that the two relationships are not in conflict?


Because I think they are different entities. The sound of an instrument
being played at a concert is one entity. A musician being on the stage at a
concert is a different thing.
2. why not collapse Sounds and Seat? I'm guessing that Sounds means
something like "violin I" and seat is "desk 1 seat b."
Well, 'Sound' might just be 'Violin'. I'm looking at this and seeing not
natural identifier. Which could mean one of 2 things:

a) I've got the model wrong, or

b)Nobody's ever bothered to do this before. Which I don't expect they have.
Otherwise, I
can't see any reason for maintaining separate tables. This is the
kind of normalizations I'd avoid, and collapse these two relations
into a single table, with repetition of data, simply in order to
keep the releationships simpler.
Actually it's a ER diagram. So none of us can see anything about
normalisation yet. And there aren't any relations either.
3. why is person linked to Seat and not Player? How do you know
*which* instruments the person is playing at that seat? All of the
ones implied by Player records? Or a limited number? A one-to-many
between player and seat would give you exact information on that.


I can't remember what the original ER looked like, sorry.

Cheers, Mike
Nov 12 '05 #21

P: n/a

P: n/a
rkc

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>: Three questions:

1. why a direct relationship between Seat and Performance, when the
indirect relationship is already there via Sounds? How can you
insure that the two relationships are not in conflict?
Because I think they are different entities. The sound of an instrument
being played at a concert is one entity. A musician being on the stage at

a concert is a different thing.
2. why not collapse Sounds and Seat? I'm guessing that Sounds means
something like "violin I" and seat is "desk 1 seat b."
Well, 'Sound' might just be 'Violin'. I'm looking at this and seeing not
natural identifier. Which could mean one of 2 things:

a) I've got the model wrong, or

b)Nobody's ever bothered to do this before. Which I don't expect they have.
Otherwise, I
can't see any reason for maintaining separate tables. This is the
kind of normalizations I'd avoid, and collapse these two relations
into a single table, with repetition of data, simply in order to
keep the releationships simpler.


Actually it's a ER diagram. So none of us can see anything about
normalisation yet. And there aren't any relations either.


That's very true. You have your major entities established, but no one
but you can tell why you've chosen them. There are no relations because
there are no attributes. You should at least mention the relationships.
A player plays an instrument. An instrument makes a sound? A player has
a sound? A seat has a person? A person has a seat? A seat has a
sound? An Event has Events?

Are sound and seat both relevant to who's supposed to show up when,
where and how much they will be paid?




Nov 12 '05 #23

P: n/a
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86. ..
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
>http://www.btinternet.com/~mike.macsween/
>
>something like that. 'Sound' is a crap entity name. It'
>basically the individual instruments that will be required on
>the gig.


Three questions:

1. why a direct relationship between Seat and Performance, when
the indirect relationship is already there via Sounds? How can
you insure that the two relationships are not in conflict?


Because I think they are different entities. The sound of an
instrument being played at a concert is one entity. A musician
being on the stage at a concert is a different thing.


I'm looking now at your newer ER and I'm concerned that there are
way too many circular relationships. I like to have one
hierarchical path from one entity to another, because there's not
way to avoid the possibility of contradictory data.

I guess I simply don't understand what you mean by "Sounds." I was
assuming it was a category that amounted to "the unique types of
instrument class needed for the particular performance/piece." In a
string quartet, the sounds would be violin, viola and cello (or
Violin I, Violin II, viola and cello, depending on whether you
mapped to unique instruments or unique voice roles). In an
orchestral piece you might have those three/four plus contrabass,
horn, oboe, bassoon, trumpet and timpani. If those are attached to
a performance, then the seat becomes a child of the sound, and
shouldn't need a relationship to performance. The seats for your
string quartet would be:

SOUND SEAT
violin I
II
viola viola
cello cello

For the orchestra:

SOUND SEAT
Violin I 1a
1b
2a
2b
Violin II 1a
1b
2a
2b
Viola 1a
1b
2
Cello 1a
1b
2
Bass 1
2
Horn I
II
Oboe I
II
Bassoon I
II
Trumpet I
II
Timpani 1

Or something like that? Is that what you're aiming for with sounds?
I'm assuming the flexibility of terminology (sounds instead of
instrument) is due to retaining "instruments" for the table of
possible instruments and also for allowing flexibility. Maybe
"sounds" means "parts?" That would map well to musical theater pit
bands, where one part has multiple instruments.
2. why not collapse Sounds and Seat? I'm guessing that Sounds
means something like "violin I" and seat is "desk 1 seat b."


Well, 'Sound' might just be 'Violin'. I'm looking at this and
seeing not natural identifier. Which could mean one of 2 things:

a) I've got the model wrong, or

b)Nobody's ever bothered to do this before. Which I don't expect
they have.


Just working through what I wrote above, why not "parts" for
"sounds?" That seems to me to be what you mean, no? Then the parts
table would be a map to the printed parts, which determine how many
people you need. Seats would then map how many people play from
each part (one part on a stand).

One reason to collapse the two tables would be because only
orchestral or symphonic band music have mass sections where there
are multiple copies of a part. That is, in an orchestral string
section for a large orchestra, you might have 32 players working
from 16 printed Violin I parts. But it's only in the string section
of the orchestra that this is the case. For all other orchestral
instruments, there is one printed part per unique voice line (with
the exception of cases where both lines are printed on one part,
divisi, which sometimes happens, say, with simple brass or woodwind
parts). The "parts" table is really a listing of the unique roles
that are played in the musical texture as laid out in the score
(assuming a score, of course). If most of the contracting is for
orchestral concerts, obviously, it becomes more helpful to separate
the two entities into two tables. If most of it is for chamber
music(or other soloistic music), though, the benefit is less
obvious.
Otherwise, I
can't see any reason for maintaining separate tables. This is
the kind of normalizations I'd avoid, and collapse these two
relations into a single table, with repetition of data, simply
in order to keep the releationships simpler.


Actually it's a ER diagram. So none of us can see anything about
normalisation yet. And there aren't any relations either.


What do the black and open dots mean? Required and optional?
3. why is person linked to Seat and not Player? How do you know
*which* instruments the person is playing at that seat? All of
the ones implied by Player records? Or a limited number? A
one-to-many between player and seat would give you exact
information on that.


I can't remember what the original ER looked like, sorry.


Well, now I'm concerned that everything is related to everything
else. I understand the relationship of Instrument both to person
and to "sounds." What I don't understand is all the relationships
between Seat and other tables. Isn't seat a child of Sounds? If so,
why is it related both to Person and to Player? Shouldn't Person be
implied by Player? And isn't it the Sounds that the player should
be attached to? And why should Seat be attached to performance?
Isn't Sounds a child of performances, so that performance will be
the grandfather of any record related to Sounds?

My proposed ER is here:

http://www.bway.net/~dfassoc/examples/ER/DWF.jpg

I'm wondering about the recursive relationship for events. I'm
assuming this is so that you can have an event occur on multiple
nights. Wouldn't it be better to have a table of dates and times
for the performance, rather than trying to track it through a
recursive relationship?

Actually, now that I think about it, maybe that's what events is?

Now that I think about it, I have some problems with your
terminology. Event seems to me like "Guys and Dolls at the Podunk
Community Theater" and Performance would have separate records for
"Thursday", "Friday" and "Saturday."

Assuming you engage the same musicians for all three nights, the
Sounds should be attached to the Event record, and all performances
would inherit the same personnel. Of course, we all know it doesn't
happen that way, especially for long-running engagements.

So, I'm thinking you need some way to account for that. I'm not
sure what to do. If you have the personnel attached to the date
table and through that to the event header (where the venue and
sponsor information is stored), you'll need to replicate all the
personnel for each of the date records when they are all the same.
I'd rather have it be a structure where you have to do extra work
for the *exceptions* rather than for the data to all be replicated
just in case one or two records need to differ.

I'm thinking in this direction:

You have an event.

The event has the same music performed on it each time.

The personnel on different nights may differ, but the
*requirements* of what needs to be performed do *not* change.

So, that means that the date needs to be part of the relationship
between the seat and the player.

Think of it this way: assuming the program for each performance is
the same (it isn't always, of course, but let's start with that
assumption), and that the personnel for all pieces on the program
is not the same, you end up with a set of musical ensembles that
are required. Say you have an orchestral concert, and two of the
pieces are for string orchestra and one is for strings plus horns,
oboes, bassoons, trumpets and timpani. Now, that means there are
two unique ensembles needed. But, assume that one of the string
orchestra pieces is from the 18th century and the other is by
Mahler. That means you need a small string orchestra for the one,
and a large string orchestra for the other. Half the string section
will play on two pieces, the other half on three. The non-strings
will only play on one.

OK, how do you map that?

Do you assign the full-time string player three times, the
large-orchestra string player twice and the brass once?

Sounds like a lot of work.

OK, I've been working on this. I've added entities and changed the
names. For performance, ensemble and event, I've completely
restructure based on the assumptions above.

The idea is to have groups of players that you can assign, rather
than to have to assign players for every item on the program.

First, I've renamed Sounds as Part, as outlined above.

Now, instead of Sounds/Part being connected to Performance, I've
added a layer of tables to allow you to defined ensembles and
pieces at a performance, those being:

Section (small string orchestra, additional strings, non-strings)
SectionEnsemble (many-to-many join)
Ensemble (the ensemble itself, small string orchestra, large
string orchestra, full orchestra)

The these tables work is this:

You create a section record for the small string orchestra and
assign the players to it.

You then create a record for the additional strings needed for the
large string orchestra.

Then create a section record for the non-string players.

Then you create three ensemble records, small string orchestra,
large string orchestra and full orchestra.

In the SectionEnsemble record, you'd join the relevent sections to
the relevant ensembles.

So, with data like this:

Section
SectionID Section
10 Small string section
11 Additional strings
12 Non-strings

Ensemble
EnsembleID Ensemble
1 Small string orchestra
2 Large string orchestra
3 Full orchestra

SectionEnsemble
SectionID EnsembleID
10 1
10 1
10 1
11 2
11 2
12 3

Now, I've obviously gone beyond an ER diagram here, but you get the
idea.

This allows you to create blocks of players and use and re-use them
anywhere on the program.

Now, above the Ensemble table, between it and the Performance
table, I've added a "pieces" table, which is there to allow you to
record information on how much an ensemble plays. This is necessary
for pay, since someone who plays six pieces gets paid more than
someone who plays just one, I assume. So, an ensemble would be used
in one or more pieces on a program.

Another reason that's needed, is because the pieces on different
performances might be different. For instance, an orchestra might
give a weekend concert on Friday and Saturday evenings and on
Sunday afternoon, but with, say, a different concerto performed on
Sunday afternoon.

Now, some problems here:

You have to set up the ensemble each time, as the parent is a piece
on a particular performance. If you've got a regular performing
ensemble with particular players, such as the core of a standing
orchestra, you'd probably want to re-use that ensemble in multiple
concerts. The structure I've outlined wouldn't really allow you to
do that, unless you put a many-to-many join table between Piece and
Ensemble. That would allow you to re-use an ensemble once it's been
defined.

The other complication is that this assumes that your booking is
going to have lots of sections that have the same part mappings. If
one small string section has 3 desks but another has 5 desks,
you'll need to define them twice, even if it actually happened to
be the same organization/musicians. So, you'd need to think about
defining this. The smartest way would be that the smallest group
would be your core and then each expansion would be an extra add-on
section.

Another complication is that with all this re-use, you have no way
to omit particular performers and replace them with someone else.
Say you're regular 4th chair utility 1st violin is on tour and is
not available, but she's part of your definition of the string
section for the Podunk Philharmonic. How do you omit that player
and replace her with a ringer for one performance, without
completely recreating the Podunk Philharmonic string section
definition?

I'm thinking that perhaps for a particular performance (or piece)
you could have an override table where you indicate that a
particular player is replaced by a different player. This would
look something like:

PieceID PlayerID ReplacementPlayerID
100 1 19
100 2 [Null]

This would show you that on a particular piece, PlayerID 1 that is
part of the ensemble defined for that piece is replaced by player
19, and that player 2 is not playing and not replaced by anyone.

Now, this would make for some pretty tricky SQL, but as it's a set
of exceptions rather than the norm, so it might be useable.

OK, there's some of my thoughts, and my proposals for solving the
problems I've thought of. I'm guessing that the issues I pointed
out in your original ER reflect your solutions to certain problems
that I've not even anticipated, so you can tear apart my proposal
accordingly.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #24

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message

Wow. Thanks for putting all that effort in.

I'm ill, my family's ill and I've got heavy work for next few days.

I'll do a reply soon.

As Rick said, I haven't really given even a half complete conceptual data
model anyway so not really fair to expect people to comment.

Mike
Nov 12 '05 #25

P: n/a
rkc

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .
I'm ill, my family's ill and I've got heavy work for next few days.

Geez. Hope ya'll are feeling better soon.

Here's my erd offering based on your first post before all the
complications were introduced.

http://www8.brinkster.com/rkc/mcsween.gif

I probably should bow out of this thread since you and
David are, I believe, both degreed, professional musicians.

I pretended to play guitar in high school.

In-A-Gada-Da-Vida honey...

Nov 12 '05 #26

P: n/a
rk*@yabba.dabba.do.rochester.rr.bomb (rkc) wrote in
<3o*******************@twister.nyroc.rr.com>:
"Mike MacSween" <mi******************@btinternet.com> wrote in
message news:3f***********************@news.aaisp.net.uk.. .
I'm ill, my family's ill and I've got heavy work for next few
days.
Geez. Hope ya'll are feeling better soon.

Here's my erd offering based on your first post before all the
complications were introduced.

http://www8.brinkster.com/rkc/mcsween.gif


Not bad, actually. You seem to have reached some of the same
conclusions I did.

I would only say that your ER is incomplete, and has one error. The
relationship between instrument and player is in the wrong place --
you've gone through instrument. For doubling musicians, this won't
work. That is, a read player may play clarinet and soprano sax and
oboe and flute (well, not likely all of those, but doubling on
multiple winds is simply not unusual in many repertories). A
flutist can easily switch in a piece between flute, piccolo and
alto or bass flute -- not at all unusual in a number of
repertories. A bassoonist might play contrabassoon in some places.
And so forth.

So, the connection from seat is to the player, and the seat has its
own selection of instruments. Seat really amounts to "role" or
"single musician who does multiple tasks."

Your seat should be connected to instruments with "Seat entails
playing Instrument" and then another parallel relation from Seat to
Player with "Seat occupied by Musician." Then in your description
of the musician, you'd also want to have a list of the instruments
that musican plays, so you'd need another connection from musician
to Instrument that would be "Musician plays Instrument."

Your single path directly through instrument to musician does not
allow enough flexibility. It's not that you can't get the job done,
just that the connection between seat and instrument does not
reflect the way pay is calculated. What actually controls it is
that a seat is the pay unit, and the exact pay for that seat is
determined via union rules. A seat with a single instrument gets
one pay scale, a seat with doubling gets a different pay scale.
I probably should bow out of this thread since you and
David are, I believe, both degreed, professional musicians.

I pretended to play guitar in high school.

In-A-Gada-Da-Vida honey...


But could you play the radio?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #27

P: n/a
rkc

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90.. .
rk*@yabba.dabba.do.rochester.rr.bomb (rkc) wrote in
<3o*******************@twister.nyroc.rr.com>:
So, the connection from seat is to the player, and the seat has its
own selection of instruments. Seat really amounts to "role" or
"single musician who does multiple tasks." Your seat should be connected to instruments with "Seat entails
playing Instrument" and then another parallel relation from Seat to
Player with "Seat occupied by Musician." Then in your description
of the musician, you'd also want to have a list of the instruments
that musican plays, so you'd need another connection from musician
to Instrument that would be "Musician plays Instrument."

Your single path directly through instrument to musician does not
allow enough flexibility. It's not that you can't get the job done,
just that the connection between seat and instrument does not
reflect the way pay is calculated. What actually controls it is
that a seat is the pay unit, and the exact pay for that seat is
determined via union rules. A seat with a single instrument gets
one pay scale, a seat with doubling gets a different pay scale.


Well, conceptually I thought a musician is linked to a seat because
they know how to play the required instrument(s). It wouldn't
be physically modeled that way.

I was wondering what attributes a Seat would have.
Instrument would be one except it can be multi-valued.
Musician.
You're saying pay scale could be an attribute of Seat?
I pretended to play guitar in high school.

In-A-Gada-Da-Vida honey...


But could you play the radio?


Nope. Just In-A-Gada-Da-Vida.

Nov 12 '05 #28

P: n/a
rk*@yabba.dabba.do.rochester.rr.bomb (rkc) wrote in
<bx*********************@twister.nyroc.rr.com>:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90. ..
rk*@yabba.dabba.do.rochester.rr.bomb (rkc) wrote in
<3o*******************@twister.nyroc.rr.com>: So, the connection from seat is to the player, and the seat has
its own selection of instruments. Seat really amounts to "role"
or "single musician who does multiple tasks."

Your seat should be connected to instruments with "Seat entails
playing Instrument" and then another parallel relation from Seat
to Player with "Seat occupied by Musician." Then in your
description of the musician, you'd also want to have a list of
the instruments that musican plays, so you'd need another
connection from musician to Instrument that would be "Musician
plays Instrument."

Your single path directly through instrument to musician does
not allow enough flexibility. It's not that you can't get the
job done, just that the connection between seat and instrument
does not reflect the way pay is calculated. What actually
controls it is that a seat is the pay unit, and the exact pay
for that seat is determined via union rules. A seat with a
single instrument gets one pay scale, a seat with doubling gets
a different pay scale.


Well, conceptually I thought a musician is linked to a seat
because they know how to play the required instrument(s). It
wouldn't be physically modeled that way.

I was wondering what attributes a Seat would have.
Instrument would be one except it can be multi-valued.
Musician.
You're saying pay scale could be an attribute of Seat?


My conceptualization was that "Seat" meant a person to do the work
required. That might involve playing a single instrument. It might
involve playing several. Union rules pay the latter person more, so
you can't have a single instrument per seat.

Basically, my reading is that a musician is assigned to a seat
because they meet the criteria for discharging the duties assigned
with that seat (i.e., they can play the instruments needed at that
seat). Thus, it seems to me that the connection is from musician to
seat, with seat having certain attributes that limit the musicians
appropriate for assignment to that seat. It is not a linear path
from seat through instruments to musician.

At least, that's the way I see it.

As to pay scale as attribute of seat, pay scale is determined by
the definition of what duties the seat entails. I'm sure there are
union rules about it, but I don't know if they are based on
principles (1 instrument is $X/hour, 2 is $Y/hour, 3 is $Z/hour) or
if they are arbitrary (a person playing alto sax is paid $X/hour, a
person playing alto and soprano sax is paid the same, the person
playing alto sax and clarinet is paid $Y, etc.).

I don't know if Mike's app needs to know about all of that. And the
pay scale rules are different for different union locals (often
governed by contract, such as in the case of Broadway theaters here
in NYC, which has a contract with the AF of M local here).

But it's still necessary for contracting, even if you don't use the
app to determine the pay rates, because contracting a gig requires
figuring out exactly how many musicians are needed and then
locating the people to play the parts. In my model, I distinguished
parts and seats, because I was using parts to represent printed
parts, not unique lines in the music. In an orchestra, as many as
32 people can be playing the same part (e.g., in the violin
sections of a full orchestra) so you might want to have flexibility
in the minimum requirements. At minimum, one person has to play the
individual part, but you'd like to have 32 people playing it. In
order to define the thing that can be 1 or 32, it's best, I think,
to put it in a separate table. Then you can contract your
orchestra, and if all you can find is 28 people for the 1st violin
section, you don't have a problem. Putting the two together will
lead to duplication of information. That's obviously a
normalization issue, but in terms of entities, I think it's clear
that parts are not the same thing as seats. But you could collapse
them if you wanted and I don't think it would do great damage to an
app.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #29

P: n/a
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:3o*******************@twister.nyroc.rr.com...

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .
I'm ill, my family's ill and I've got heavy work for next few days.

Geez. Hope ya'll are feeling better soon.


Thanks
I probably should bow out of this thread since you and
David are, I believe, both degreed, professional musicians.


I wouldn't worry about that.

Anyway, I've decided to change it. I was thinking about this from an 'ideal'
usage case. Or rather 2 cases:

1. The client knows what instruments are required at a performance (16
violins, 3 clarinets, 2 flutes etc.) but not who, or how many players are
required. That was the purpose of the 'Sound' entity type.

2. The client knows what people are required (16 violinists, 3 reeds
players) but not what instruments they'll be playing, or how many.

Now I've thought some more. The first case isn't realistic. Firstly no
orchestral fixer/band leader or whoever actually works like that. The only
time they might possibly, would be fixing a recording session, with
overdubs. Let's think about it. In most orchestras most players play one
instrument. Only in the woodwind is there a lot of 'doubling' (playing two
or more instruments) and in the percussion. Percussion is actually more a
case of one instrument made up of many. So in a large orchestra very very
careful assignment of instruments to players might just save a couple of
players wages. But then what? You have to find (and pay the extra for) a
woodwind player who is actually competent on Clarinet, flute, alto flute,
piccolo, alto sax etc. etc. And can change from one to the other at the drop
of a hat and maintain some sort of embouchure.

And how would we know in case one that we need 3 clarinets? Only by looking
through the complete score(s) for each piece and seeing what is the greatest
number of clarinets _that are played simultaneously_. And doing the same for
flute, alto sax etc. And with quite a bit of work we could perhaps find that
we could make do with 3 instead of 4 reeds players. But first of all there
would be the question of quality of player. And then it could only be
achieved, probably, by moving music from stand to stand during the course of
the performance. None of these are things which most MDs or players would
tolerate, at least only under sufference. In the average Broadway pit band
that work has already be done, to some extent, by the arranger/music
copyist. The big symphonic pieces have standard ensembles, Sir Simon isn't
going to ask the principal clarinet of the Berlin Phil to play oboe a bit to
save a few quid.

Generally fixers book a team. 1st violin playing violin etc. etc. 1st reed
Clarinet doubling flute. If the job is particularly tight then she might
say, can we get away with 3 woodwind, knowing that when it comes to
rehearsal things might be missing. But this is too much complexity to make
it worthwhile.

So I'm ditching sounds. I'll post another ER when I find an ISP who lets me
FTP on broadband, instead of insisting I use their crappy analogue dialup!

That recursive join on Events then, what's all that about?

To allow for (at least) this scenario - Week long show, containing Band
call, dress rehearsal, show, show, show, matinee show, show. Perhaps at
different venues, using different players. For different fees. Some paid for
the whole week, some by the show, deps just for the show, pianist paid for
whole week and extra for vocal rehearsals etc. But I'm the first to admit I
haven't worked it through completely yet.

Thanks for your input.

Mike MacSween
Nov 12 '05 #30

P: n/a
Mike, did you know that you are conversing with a homosexual who
publishes pedophile fantasies? Please encourage David Fenton to
get out of this newsgroup.
In article <3f***********************@news.aaisp.net.uk>
"Mike MacSween" <mi******************@btinternet.com> wrote:


"David W. Fenton" <dX********@bway.net.invalid> wrote in message

Wow. Thanks for putting all that effort in.

I'm ill, my family's ill and I've got heavy work for next few days.

I'll do a reply soon.

As Rick said, I haven't really given even a half complete conceptual data
model anyway so not really fair to expect people to comment.

Mike




Nov 12 '05 #32

P: n/a
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
http://www.btinternet.com/~mike.macsween/


Well, with your explanation of why you had Sounds in there, I do
understand why you've now taken it out.

But I think it's denormalized.

The structure I outlined allowed you to map out the score in a
table called Parts and then have Seats assigned to the parts. I
think that has a certain utility.

I also think that your new ER has the connection in the wrong
place, from PERSON to SEAT instead of from PLAYER to SEAT. You can
only tell what instrument the person who plays multiple instruments
is playing by looking at the Seat. But a seat might be for two
instruments. Your description in your other reply made it sound
unusual, but it's not that unusual, even in classical music. As you
say, the doublings are usually not determined ad hoc, but are in
the parts themselves. This summer I was involved in a music
festival where Die Fledermaus was performed. We used an
orchestration with a reduced wind complement, that had been
arranged from the full orchestral score in order to keep down the
number of musicians (we had a small pit, for one, but also a small
budget). Several of the winds doubled, but this was in their
*parts*, which were prepared not something that we required them to
swap printed parts back and forth.

That's why I went with a separation between PARTS and SEATS,
because I felt it captured the printed music on the one side (the
minimum requirements) and the way you got it performed on the other
(how many seats you filled for each part).

The seat, I felt, should be attached to the PLAYER records, in a
many-to-many (I don't think my proposed ER reflected that). Why?
Because you're hiring a person to play a particular instrument
(which is what the PLAYER table captures, the N:N relationship
between PERSON and INSTRUMENT). You're going to need a many-to-many
somewhere for seats that are doublers. Your other choice is to put
the join table between SEAT and INSTRUMENT, and maybe that's
better, as now that I think of it, you're going to have to do that,
anyway.

Hmm. Changed my mind. I think I do prefer your approach. The PLAYER
table is helpful for picking instrumentalists from a list (when
assigning to a seat, your dropdown could be limited to those people
who play the same instruments as those required for the seat), so
it should stay, but I think you're right that it is the person
you're hiring, and you find out what they are playing from the
instruments connected with the seat.

Collapsing your original SOUNDS and SEATS tables into one is
something I suggested, but in my ER I didn't do it, but renamed
SOUNDS as PARTS (which may not have been what you intended) as a
way of making it work better for contracting for ensembles where
major doubling is a big issue (like orchestral music).

As to your EVENTS recursive join, I think that's one way of doing
it, but my long discourse on defining ensembles and then attaching
them as a body to different performances has more utility.

The idea is that you define a configuration of people with an
ensemble record (made up from connections of sections, as outlined
in my post about the concert with a small string orchestra, large
string orchestra and full orchestra). Once the ensemble is created,
you can then attach it to multiple "performances" (which would
include rehearsals -- you'd probably want a different name for the
table than performances, though I can't think of one). You'd then
need something to connect all the "performances" together, but the
EVENT table seems to me to already do that. The advantage of my
floating ensemble definition is that you don't have to repeatedly
re-attach the people (something your recursion accomplishes, too),
but both your method and mine have the problem of accounting for
variation at different instances of the performance.

Here's an example:

Concert with three pieces, one with winds and two without.

Two rehearsals.

Two performances.

Rehearsal one is for only two of the pieces (the ones without
winds), rehearsal two for all three (including the winds, of
course).

In your recursive structure, how do you record the fact that the
winds only participate in one rehearsal?

Another example:

Concert performed 3 times.

Program for first 2 performances is identical, for 3rd performance,
the Brahms violin concerto is replaced by a Mozart violin concerto
with smaller orchestra and fewer winds.

How does your recursive structure deal with that?

Mine works because the ensembles are attached to pieces which are
themselves attached to performances (which are in turn attached to
the top-level event record that connects it all together). I don't
see how your structure can do that.

For the record, my ER is at:

http://www.bway.net/~dfassoc/examples/ER/DWF.jpg

Also, I had several of the relationships reversed in my original.
Those have been fixed in the current one. I haven't changed the
connection between SEAT and PLAYER, but now believe that the
relationship should be between SEAT and PERSON, as you have it in
yours currently. I'm not sure I'd collapse your SOUNDS and SEAT (my
PART and SEAT) -- I think there's great utility in keeping them
normalized, especially if much of the contracting is for orchestral
performances, or any ensemble in which more than one person plays a
single part (any string section in non-chamber music will have
this, even in Broadway musicals).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc
Nov 12 '05 #33

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90.. .
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
http://www.btinternet.com/~mike.macsween/
Well, with your explanation of why you had Sounds in there, I do
understand why you've now taken it out.

But I think it's denormalized.


You can't tell if it's normalised or not. I've not given you any fields, so
you can't see any functional dependencies. It's an ER diagram, part of a
conceptual data model that may or may not be turned into a relational model.
The seat, I felt, should be attached to the PLAYER records, in a
many-to-many (I don't think my proposed ER reflected that). Why?


Yes, I thought that too, for a while.

[]

The recursive join. 'Events' is a lousy name.

But here goes:

Month long tour of Canada, production of Annie, is an event
Seats booked are a, b, c, d.

First week is an event.
seats needed are only b, c, d

second week on a, b, c are needed
first day of second week only b, c are needed.

So everybody you need at any point in the whole think is booked onto the top
level event, with progressive fine levels of granularity as you go lower.

But that's kind of a rough outline. I need to do a great deal more work to
see it that'll hold water.

Yours, Mike
Nov 12 '05 #34

P: n/a
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90. ..
mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:
>http://www.btinternet.com/~mike.macsween/
Well, with your explanation of why you had Sounds in there, I do
understand why you've now taken it out.

But I think it's denormalized.


You can't tell if it's normalised or not. I've not given you any
fields, so you can't see any functional dependencies. It's an ER
diagram, part of a conceptual data model that may or may not be
turned into a relational model.


If you're repeating the connection to the instrument, that's
duplicate data.
The seat, I felt, should be attached to the PLAYER records, in a
many-to-many (I don't think my proposed ER reflected that). Why?


Yes, I thought that too, for a while.


I changed my mind by the end of the message!
[]

The recursive join. 'Events' is a lousy name.

But here goes:

Month long tour of Canada, production of Annie, is an event
Seats booked are a, b, c, d.

First week is an event.
seats needed are only b, c, d

second week on a, b, c are needed
first day of second week only b, c are needed.

So everybody you need at any point in the whole think is booked
onto the top level event, with progressive fine levels of
granularity as you go lower.

But that's kind of a rough outline. I need to do a great deal more
work to see it that'll hold water.


Event is more like JOB or CONTRACT, which is composed of various
performance dates, rehearsals, etc.

Yes?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #35

P: n/a
rkc

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .
To allow for (at least) this scenario - Week long show, containing Band
call, dress rehearsal, show, show, show, matinee show, show. Perhaps at
different venues, using different players. For different fees. Some paid for the whole week, some by the show, deps just for the show, pianist paid for
whole week and extra for vocal rehearsals etc. But I'm the first to admit I haven't worked it through completely yet.

Thanks for your input.


After your original post my impression was that you lost sight of the forest
because of your familiarity with the individual trees. I see now that you
really
haven't decided what you're trying to model.

Me, I'm still trying to figure out the Subject line.


Nov 12 '05 #36

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
If you're repeating the connection to the instrument, that's
duplicate data.


I repeat. Its an ER diagram. Part of conceptual data model. Which may or may
not be turned into a relational database. Normalisation is a process applied
to tables. There aren't any.

I don't think there are any duplicated data in that. Of course I may be
wrong. But the data are 'these instruments will be played by the musician
sitting in this chair' and 'this musician will be sitting in that chair' and
'this musician is capable of playing these instruments'. I can't see any
duplication there. Each of those data can exist independantly of each other.
The seat, I felt, should be attached to the PLAYER records, in a
many-to-many (I don't think my proposed ER reflected that). Why?


Yes, I thought that too, for a while.


I changed my mind by the end of the message!
[]

The recursive join. 'Events' is a lousy name.

But here goes:

Month long tour of Canada, production of Annie, is an event
Seats booked are a, b, c, d.

First week is an event.
seats needed are only b, c, d

second week on a, b, c are needed
first day of second week only b, c are needed.

So everybody you need at any point in the whole think is booked
onto the top level event, with progressive fine levels of
granularity as you go lower.

But that's kind of a rough outline. I need to do a great deal more
work to see it that'll hold water.


Event is more like JOB or CONTRACT, which is composed of various
performance dates, rehearsals, etc.

Yes?


Exactly.

Yours, Mike
Nov 12 '05 #37

P: n/a
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:64***************@twister.nyroc.rr.com...

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .
To allow for (at least) this scenario - Week long show, containing Band
call, dress rehearsal, show, show, show, matinee show, show. Perhaps at
different venues, using different players. For different fees. Some paid for
the whole week, some by the show, deps just for the show, pianist paid for whole week and extra for vocal rehearsals etc. But I'm the first to

admit I
haven't worked it through completely yet.

Thanks for your input.
After your original post my impression was that you lost sight of the

forest because of your familiarity with the individual trees. I see now that you
really
haven't decided what you're trying to model.
Actually, I know exactly what I'm trying to model. The thing I've just
described above. Just haven't arrived at a satisfactory model yet.
Me, I'm still trying to figure out the Subject line.


Yes, crap isn't it!

Cheers, Mike
Nov 12 '05 #38

P: n/a
rkc

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .
Actually, I know exactly what I'm trying to model. The thing I've just
described above. Just haven't arrived at a satisfactory model yet.


I see two major phases of user activity. No doubt overly simplified.

1) Figure out the needed 'Seats' required for a 'Performance'.

I have no idea how that is done. How is that done?
Maybe that's where Parts, Sounds, Ensemble and all the other stuff
that seems superfluous to me comes in.

Maybe someone else just tells you.

2) Hire a 'Person' who can perform the duties required of that 'Seat'.

Done using a database of 'Persons' and 'Instruments' played.
The 'Seat is what needs to be filled, right? Part, Sound, etc boil down
to the person sitting in the seat playing the Instrument.
The booking mentioned in your first post and not seen since.
Me, I'm still trying to figure out the Subject line.


Yes, crap isn't it!


You seem to have been further along back than you are now.
Now, there really isn't a question. Just an argument about whether
you can normalize a concept.


Nov 12 '05 #39

P: n/a
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:qa*****************@twister.nyroc.rr.com...

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .
Actually, I know exactly what I'm trying to model. The thing I've just
described above. Just haven't arrived at a satisfactory model yet.
I see two major phases of user activity. No doubt overly simplified.

1) Figure out the needed 'Seats' required for a 'Performance'.

I have no idea how that is done. How is that done?
Maybe that's where Parts, Sounds, Ensemble and all the other stuff
that seems superfluous to me comes in.

Maybe someone else just tells you.
More or less. They'll say - 'we need an orchestra for this show. It's a big
budget so don't skimp on the strings, you can afford a proper flute player
(not a sax player doubling on flute)'. I got to wrapped up in doubling.
Which isn't a major issue. And for most symphonic/concert pieces, and
Broadway shows it's already been worked out.
2) Hire a 'Person' who can perform the duties required of that 'Seat'.
Yes.
Done using a database of 'Persons' and 'Instruments' played.
The 'Seat is what needs to be filled, right? Part, Sound, etc boil down
to the person sitting in the seat playing the Instrument.
The booking mentioned in your first post and not seen since.
That's what one of the attributes of Seat is. The booking of the musician.
Another attribute(s), is the instruments required on that seat.
Me, I'm still trying to figure out the Subject line.


Yes, crap isn't it!


You seem to have been further along back than you are now.
Now, there really isn't a question. Just an argument about whether
you can normalize a concept.


Er, yes. Not sure what you mean. But I've taken two steps back to go one
forward. Or something. I'm happy with the instrument/player/seat thing now.
Not so happy with this recursive join and that area. See new ER. With entity
types already!

http://www.mikemacsween.com/ER.htm

Cheers, Mike MacSween
Nov 12 '05 #40

P: n/a
rkc

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .
You seem to have been further along back than you are now.
Now, there really isn't a question. Just an argument about whether
you can normalize a concept.
Er, yes. Not sure what you mean. But I've taken two steps back to go one
forward. Or something. I'm happy with the instrument/player/seat thing

now.

That first sentence does sound kinda stupid. Thought then than, typed than.
Not so happy with this recursive join and that area. See new ER. With entity types already!

http://www.mikemacsween.com/ER.htm


What is the meaning of Ensemble in this case?


Nov 12 '05 #41

P: n/a
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:Ax*****************@twister.nyroc.rr.com...
What is the meaning of Ensemble in this case?


Nothing more than the name of the band/orchestra really. Because it can be a
variable line up, both in terms of instruments and people.

Mike
Nov 12 '05 #42

P: n/a
rkc

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:Ax*****************@twister.nyroc.rr.com...
What is the meaning of Ensemble in this case?
Nothing more than the name of the band/orchestra really. Because it can be

a variable line up, both in terms of instruments and people.


So it's nothing more than a name for an established group of persons that
are able to fill the needed seats.

As for the recursive Events problem, I think D.W. had that right when he
suggested that the major entity might be a Contract that is filled by
participating in Events like rehearsals, perfomances and whatever else
muscians are required to do to get ready to do what they do.
Nov 12 '05 #43

P: n/a
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:MP*****************@twister.nyroc.rr.com...
So it's nothing more than a name for an established group of persons that
are able to fill the needed seats.
But not necessarily the same persons. Or the same instruments. More like a
concept. Man.
As for the recursive Events problem, I think D.W. had that right when he
suggested that the major entity might be a Contract that is filled by
participating in Events like rehearsals, perfomances and whatever else
muscians are required to do to get ready to do what they do.


Yes. That might be the way to go. I'd call it a Production consisting of
Events.

What I'm trying to get though is to allow for a multi level structure, with
variable number of levels:

1. A single string quartet gig is one thing.

2. Elton John world tour, contains USA leg and European leg. USA leg
contains state tours, state tour has city gigs. Philadelphia gig has sound
check, band rehearsal, show 1, show 2.

That's where I think the recursive join could be the answer. I've read
around similar structures. In one book a similar idea for employees in a
Giant Firm, Firm, Division, Plant, Department type structure was only to
have employees linked to the lowest level (i.e. instances of the entity with
no 'children'). That looked like the way to go. Easy to tell what 'parent'
they are part of due to their membership of a 'child'.

The problem with my thing is that sometimes people will be paid at a parent
level. In other words you want to book the player for gigs 1,2,3 of the
show, but without individual fees for the gigs, but rather an overall fee
for the show. Wayne Gillespie had a similar idea for a models database he
did. I might end up using his structure. But I think it limits me to 2
levels.

Yours, Mike MacSween
Nov 12 '05 #44

This discussion thread is closed

Replies have been disabled for this discussion.