I'm currently in the planning phase of building a scheduling web app (for volunteer staffing of events), and I've got a question for those with more experience.
Background: There's a calendar of events, and any user at any time can register for any of the events. At a later time, but before that event, one of the admins will step in and select a "Staff List" out of those that registered, and the rest will be put into an "Alternate List".
What I've been thinking so far is that there will be an Event table, a User table, and then three others:
The question then becomes two part:
That second question is really the one I'd like to see discussed. That seems like a lot of duplicated material (everything in either UserStaff or UserAlt will always be in UserEvent), so I was thinking of creating a unique key for the UserEvent table, in addition to the composite key, that the other tables (UserStaff and UserAlt) will refer to. On the plus side, there is less duplicated content, on the down side there's an intermediary table (UserEvent) that needs to be referenced in almost every query this way.
Hopefully I've been clear enough, and thanks in advance.
I would have the following tables:
User (UserID, firstname, lastname, etc.)
Event (EventID, Name, Date, Location, Capacity, etc.)
EventRegistration (EventRegistrationID, UserID, EventID, ParticipantTypeID, etc.)
ParticipantType (ParticipantTypeID, Name)
ParticipantType.Name is one of "participant" or "staff".