Storing Days in mysql Database

Question

I would like to know the most efficient way of storing days of the week in a mysql database. I have a list of events that occur every week and want to return them in a list format as a schedule on the matching day.

I'm not sure which is the best way to go about this as it's not a specific date just rather a day?

Answer 1

For your particular scenario I would go with ENUM type, it is both readable and efficient.

CREATE TABLE MY_TABLE
(
 -- other fields
  EVENT_DAY ENUM('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')
);

The nice thing about ENUMs is that you can write queries that are easy to read (e.g., WHERE EVENT_DAY = 'FRIDAY') without giving up on performance, since internally MySQL stores data efficiently using a numeric representation.

---

Just to play the Devil's Advocate, Chris Komlenic wrote a very interesting post recommending Reference Tables instead of enums. Anyway, I think that your problem fits his article "Criteria for when it might be okay to use enum". And a reference table to model days of the week sounds a little bit overkill (I would go with TINYINT as a second option).

Answer 2

You can store the weekdays and their standard ODBC index numbers in a table. (Use the INNODB engine throughout.)

create table weekdays (
  weekday_num integer primary key,
  day_of_week char(3) not null,
  unique (day_of_week)
);

-- Values correspond to return values from the dayofweek() function.
insert into weekdays values (1, 'Sun');
insert into weekdays values (2, 'Mon');
insert into weekdays values (3, 'Tue');
insert into weekdays values (4, 'Wed');
insert into weekdays values (5, 'Thu');
insert into weekdays values (6, 'Fri');
insert into weekdays values (7, 'Sat');

If application programs need to present these days and their indexes in a list, they can just query the database for the right values. (Or generate code by querying the database during make <your_program_name>.)

Then create a table of recurring events.

create table recurring_events (
  event_name varchar(20) primary key,
  day_of_week char(3) not null,
  foreign key (day_of_week) references weekdays (day_of_week)
);

insert into recurring_events values
('Trash pickup', 'Mon'),
('Bookmobile', 'Mon'),
('Grocery store', 'Wed'),
('Tutoring', 'Fri');

Using a join means the dbms should be able to avoid evaluating dayofweek() for every row in your table.

select e.*
from recurring_events e
inner join weekdays w on e.day_of_week = w.day_of_week
and dayofweek(current_date) = w.weekday_num;