What's the best way to store the days of the week an event takes place on in a relational database?

I don't think it's hard to write queries if we use the bit option. Just use simple binary math. I think it's the most efficient method. Personally, I do it all the time. Take a look:

 sun=1, mon=2, tue=4, wed=8, thu=16, fri=32, sat=64. 

Now, say the course is held on mon, wed and fri. the value to save in database would be 42 (2+8+32). Then you can select courses on wednesday like this:

select * from courses where (days & 8) > 0

if you want courses on thu and fri you would write:

select * from courses where (days & 48) > 0

this article is relevant: http://en.wikipedia.org/wiki/Bitwise_operation

you can put days of week numbers, as constants in your code and it will be clear enough.

Hope it helps.

I would avoid the string option for the sense of purity: it adds an extra layer of encoding/decoding that you do not need. It may also mess you up in the case of internationalization.

Since the number of days in a week is 7, I would keep seven columns, perhaps boolean. This will also facilitate subsequent queries. This will also be useful if the tool is ever used in countries where the workweek starts on different days.

I would avoid the lookup because that would be over-normalization. Unless your set of lookup items is not obvious or could possibly change, it's overkill. In the case of days-of-the-week (unlike US states, for example), I would sleep soundly with the fixed set.

Considering the data domain, I don't think that a bitfield would achieve any significant space savings for you and would just make your code more complex.

Finally, a word of warning about the domain: a lot of schools do weird things with their schedules where they "swap days" to balance out an equal number of weekdays of each type over the semester despite holidays. I am not clear about your system, but perhaps the best approach would be to store a table of the actual dates in which the course is expected to take place. This way, if there are two Tuesdays in a week, the teacher can get paid for showing up twice, and the teacher for the Thursday that was canceled will not pay.

A possible #4: Why does it need to be a single column? You could add 7 bit columns for each day of the week to the table. Writing SQL against it is simple, just test for a 1 in the column of your choice. And the app code reading from the database just hides this in a switch. I realize that this is not normal form and I usually spend quite a bit of time trying to undo such designs from previous programmers, but I somewhat doubt that we're going to add an eighth day to the week any time soon.

To comment on the other solutions, I would probably groan if I encountered the lookup table. My first inclination as well was the bit field with a few custom database functions to help you write natural queries against that field easily.

I'll be curious to read some of the other suggestions that people come up with.

Edit: I should add that #3 and the suggestion above are easier to add indexes to. I'm not sure how one could write a SQL query like "get me all classes on Thursday" for the #1 or #2 queries that wouldn't result in a table scan. But I may just be dim tonight.