I'm re-designing a database that will facilitate registration at various events. One column in the event_person table describes the current status of the registrant. The forces that apply here are:
- Various events may have different sets of status values
- We may ship this as a framework to various event organizers
- We (or an organizer) may administer several events using the same database.
- There will be 'several' events per database (perhaps up to a few hundred), but we should be able to handle 10,000 or more.
- We anticipate that there will be only 'some' registrants per event (perhaps up to three hundred) but we should be able to handle up to a few thousand.
As a concrete example, one of our events needs these status values: 'applied', 'accepted', 'enrolled', 'withdrawn', 'dropped', 'waiting_for_space', 'staff', 'staff-associate'
Another event needs 'applied', 'in_evaluation', 'accepted', 'rejected', 'enrolled', 'dropped'
We can hold the status in a column that is an enum. The advantage of enum are space, speed, fixed values known to the database. The disadvantage is that the set of values has to be a superset of the needed values for any given event; or we have to give event staff the power to alter the enum in the schema... and event staff are not technical.
We can hold the status in a text column. The advantage is simplicity, but since the values are not known to the database, we have to hold the legal values in code, which means event staff must be able to alter our code (or at least a text table of values)... and event staff are not technical
We can have a status table holding event id and status values. The advantage is that the values are known to the database; event staff can update those values via a safe controller tool as opposed to 'going meta' to maintain their values. The disadvantage is added complexity when selecting information about a registrant's status and when showing a controller to staff who are allowed to change the status.
I suppose there are other options that we haven't considered.
Our initial design did not foresee multiple events and event staff, so we used an enum and hard coded the enum values in our controller code. That decision is now causing pain as we attempt to expand.
What choice would you recommend?