Best way to store weekly event in MySQL?

Question

I have a table of weekly events that run on certain days of the week (e.g. MTWTh, MWF, etc.) and run on a certain time (e.g. 8am-5pm). What's the best way to store day of week information in MySQL to make retrieving and working with the data easiest? My CakePHP app is going to need to retrieve all events happening NOW().

For time of day, I would just use TIME. For days of the week, I had considered a 7-bit bitfield, a varchar ("MTWThFr" type deal) for the days of the week, but both of those seem like clunky solutions (the varchar being clunkier).

Any suggestions?

Answer 1

Here's a straightforward way:

EventID Title    Mon Tue Wed Thu Fri Sat Sun BeginningDate EndDate
1       MyEvent  0   0   0   1   0   0   0   14-01-2010    14-01-2033

How to use:

Simply set a 1 on the days you want to run it. Since the 7-days calendar is not likely to change any time soon, that structure should be immutable. You can choose any combination of days.

To recap:

Run every Thursdays:

EventID Title    Mon Tue Wed Thu Fri Sat Sun BeginningDate EndDate
1       MyEvent  0   0   0   1   0   0   0   14-01-2010    14-01-2033

Run every Thursdays & Mondays:

EventID Title    Mon Tue Wed Thu Fri Sat Sun BeginningDate EndDate
1       MyEvent  1   0   0   1   0   0   0   14-01-2010    14-01-2033

Further more, you get only one row per event schedule, which is easier and cleaner to handle programmatically.

For example, to find all events to be executed on monday, do:

select * from Events where Mon = 1

Answer 2

Can you add an DayOfWeek column in your table and make it an int? Valid values for that would be 1 thru 7. You could add a constraint on that to enforce that rule. For time, how about a BeginTime columns and an EndTime column? They would be int's as well 0-24

For an event at 5:00 pm on Monday would look like this in your table

Event_ID DayOfWeek BeginTime EndTime
1        2         1700      1800

Answer 3

Why not have several lines, each line having only one column containing the day of week. This column would be just a simple :

ENUM("Monday", "Tuesday", ...)

Then, in PHP you could use date and strtotime functions to get the name of the day :

echo date('l');
echo date('l', strtotime('mon'));
// Outputs "Monday"

It is way more readable to store the name of the day.