DB Schema Organization

Question

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:

• UserEvent
  • Maps users to events they registered to. Does not imply either the Staff nor the Alt list membership.
• UserStaff
  • Maps users to events they registered to, and also happen to be staffing.
• UserAlt
  • Similar to UserStaff

The question then becomes two part:

• Is this a good way to do it?
• Should each of those three associative tables have the user id and the event id?

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.

Answer 1

This seems good, although you might want to consider combining your User - Event association tables into one, and having a column on that table that indicates the purpose of the association, i.e. Event, Staff, or Alt. This would effectively obviate the need for the duplication you describe in the UserEvent tables, since Staff and Alt could be considered to be supersets of Event for most purposes.

One benefit of this approach is that it allows for there to be multiple types of User - Event associations, such as if you have a User who is a Staffer for an Event but not a Participant, or a User who is just an Alt; this approach saves you from having to enumerate all the possible combinations. Now, if your design explicitly specifies that you can only have a certain set of User Participation types, this might introduce a level of dissociation you don't want; you may prefer to have explicit constraints on the set of participation levels that a User may have on an Event. If you don't have that tightly specified set, on the other hand, this system allows for adding more Participation roles easily (and without disturbing existing Participation roles).

Answer 2

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".

Answer 3

Not a direct answer to your question, but here's a site I like. It's got tons (and tons) of sample schema. I generally don't use it as definitive (of course), but sometimes it will give me an idea on something I wasn't thinking of.