I am presently within the planning phase of creating a arranging web application (for volunteer staffing of occasions), and I have got an issue for individuals with increased experience.

Background: There is a calendar of occasions, and then any user anytime can sign up for the occasions. At another time, before that event, among the admins will part of and choose a "Staff List" from individuals that registered, and also the relaxation is going to be put in an "Alternate List".

What I have been thinking to date is the fact that you will see a celebration table, a person table, after which three others:

  • UserEvent
    • Maps customers to occasions they registered to. Doesn't mean either employees nor the Alt list membership.
  • UserStaff
    • Maps customers to occasions they registered to, as well as are actually staffing.
  • UserAlt
    • Much like UserStaff

The question then becomes two part:

  • Is a great way to get it done?
  • Should all of individuals three associative tables possess the user id and also the event id?

That second question is usually the one Let me see talked about. That appears like lots of copied material (my way through either UserStaff or UserAlt will be in UserEvent), so I believed of making a distinctive key for that UserEvent table, additionally towards the composite key, the other tables (UserStaff and UserAlt) will make reference to. Around the plus side, there's less copied content, around the lower side there's a middleman table (UserEvent) that should be recommended in nearly every query by doing this.

Hopefully I have been obvious enough, and thanks ahead of time.

This appears good, although you might like to consider mixing your User - Event association tables into one, and getting a column on that table that signifies the objective of the association, i.e. Event, Staff, or Alt. This could effectively obviate the requirement for the duplication you describe within the UserEvent tables, since Staff and Alt might be regarded as supersets of Event for many reasons.

Just one benefit of the approach is it enables in order for there to become multiple kinds of User - Event associations, such just like you possess a User who's a Staffer to have an Event although not a Participant, or perhaps a User who's just an Alt this method helps you save from needing to enumerate all of the possible combinations. Now, in case your design clearly identifies that you could have only a particular group of User Participation types, this may introduce an amount of dissociation you wouldn't want you might would rather have explicit constraints around the group of participation levels that the User might have with an Event. Without having that tightly specified set, however, this technique enables for adding more Participation roles easily (and without disturbing existing Participation roles).

I'd possess 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.Title is just one of "participant" or "staff".

Not really a direct response to your question, but here's a site I like. It's tons (and tons) of sample schema. I generally avoid using it as being definitive (obviously), but it sometimes will is there a problem on something I wasn't considering.