Table design advice needed please
Quote:
> I've got a table design for my application and I am having such trouble
> writing the SQL to use it that I am beginning to think I may have got it
> wrong: your advice would be welcome.
> The application assists with drawing up a rota for people who play in a
> regular series of bands. There are Events (days on which a band is
> required) and Players; each band offers a number of Roles, and a player
> may be assigned concurrently to more than one role (Singer & Piano
> player for example). Before the rota is drawn up players may signal
> their availability for a given event.
> So I have: an Event table, keyed by date; a Player table, keyed by a
> counter; a Band table which holds details of the roles which exist. So
> far I'm happy with that. The problem comes with how to recognise (a)
> statements of availability for future events and (b) assignments to a
> given event.
> Currently I have a PlayerEvent table which allows players to signal
> their unavailability for particular events, and a PlayerEventBand table
> which contains a record for each assignment of a player to an event (so
> there may be more than one record for an event if the player is singing
> and playing guitar, for example).
> PlayerEvent PlayerEventBand
> ----------- ---------------
> PlayerId PlayerId
> EventDate EventDate
> Availability RoleAssigned
> What's worrying me is that those two are so similar that I may have got
> the thinking wrong; and I'm having great trouble finding SQL to give me
> a Player's "status" for a given event: in other words whether s/he has
> an availability status AND whether or not s/he is assigned (The
> assignment part of the application needs to know whether someone can be
IMHO, you are alright here..one is like a 'volunteering record' and the
other is like an 'assignment recrd.' That is a 'normalized' schmeme b/c
none of the fields will ever be empty, and they're keyed, hopefully, on
the ID. I would imagine that any player could volunteer for as many jobs
as he wants to ( multiple PlayerEven records) but can only be assigned
to a single job.
You can make your volunteer records at will, no problem. There are
many ways to find out if someone has already been assigned...among them:
SQL$ = SELECT Count (*)
As [TotalCount]
From PlayerEventBand
Where PlayerId = thisPlayerID
And EventDate = thisEventDate
Create a recordset on that SQL and if recRs![TotalCount] > 0, then that
guy is already assigned and you'll know not to give him another
assignment.
There are other ways..in the PlayerEventBand you sould make ID and
EventDate the unique primary key, then if you to add another one of them
the db will spit back an error at you ...which you can trap...but I dont
it that way.
Anyway, i hope I have understood [some] of the issues you wanted help
on.
thomas-
--
=====================================
