enum or varchar? Maybe a join table?

[griswolf]

I need to keep track of the status for a person who registers for an event. This is going to be part of a tool that is administered by non-programmers. Various events will have different sets of status values, and it is possible for several events with distinct or overlapping attendees to be stored in the same table. The question is: What is the best way to allow for this flexibility? I see three options:

ONE: Use an enum containing a super set of all possible status values:
CREATE TABLE user_event (
...
status enum('blah','bleh','blih','etc'),
...
);
TWO: Use a free form text field:
CREATE TABLE user_event(
...
status VARCHAR(30),
...
);
THREE: Use a join table
CREATE TABLE status_event (
id INTEGER auto_increment unique key,
event_id INTEGER foreign key references event(id),
status VARCHAR(30)
);
CREATE TABLE user_event (
...
status INTEGER foreign key references status_event(id),
...
);

and of course, FOUR: Something I didn't think of

In cases ONE and TWO, because the status field is or allows a super set of all appropriate values, I'll need to run some extra code to validate values. In case THREE, all and only legal values are in the join table.

What would you suggest is the best technique here? Why?

Thanks!

[pritaeas]

I'd go with 3, the link table. Option 1 with an enum requires a database change if something needs to be changed or is missing. Option 2 with a free form allows for a lot of different ways to mark the same type. Not recommended for filtering. Option 3 can be adjusted with ease by the users, without your interference. You'll probably need a way to connect some of the statuses to a specific event (another link table), if not all are needed for each event.

[griswolf]

Thanks pritaeas, as you can probably tell, I was leaning that way already, so it is nice to have confirmation.

I'm not sure whether another link table (event_id and status_id) is a better choice, or whether it might not be better to have an event_id field in table status_event, as in my initial post. The extra link table is better form; but given the small number of status values per event and the fairly small time density of events (surely no more than a few hundred per year), I'm torn between handling Yet Another Table and living with the duplicate status strings.

[pritaeas]

Don't think it would really matter. You'll have regrets only when you need to change it later (if ever).