Multiple boolean columns or a new table

Question

I want to store Doctors and their Schedules (times at which they are available). Schedules can be such that a doctor is available on particular days of the week (eg: a doctor works only Tuesday and Thursday).

I will need to query available doctors on a range of dates, and also, query the timings of a particular doctor.

My question is:

Should I create 7 boolean columns in the schedules table or, create a table schedule_days with a schedule_id and day columns. The day column would be an integer from 0 to 6, representing the seven days of the week.

Which implementation would be faster for reads?

score 0 · Answer 1 · answered Nov 29 '17 at 15:43

I don't think there is any one "right" answer here, even when limiting the question to "faster for reads" since the sql being submitted makes the difference here (will this get joined to other tables, how often, and how, etc.).

I would consider future scope though.

doctor_id | day_of_week

would allow you to, in the future add two new columns your client will inevitably ask for

doctor_id | day_of_week | time_in | time_out

Where the doctor_id | m | tu | w | th | f | sa | su will not.

However, if read is the utmost importance then consider

doctor_id | day_of_week_bitmask

Then just store an integer value representing the bitmask for the 7 days of the week. It will fit into a TINYINT so it's footprint is small too. You'll obviously have some overhead in reading the bitmask, but computers are quick with that sort of thing.

If I use `day_of_week_bitmask`, how would I query all doctors available on (say) Monday? — Vedant Agarwala, Nov 30 '17 at 07:15
You could hit it with whatever the integer representation is for monday. [check out this link](https://technet.microsoft.com/en-us/library/ee808920.aspx) Or you could do it properly using the `&` (BITAND) function [like at this question](https://stackoverflow.com/questions/11059182/select-users-from-mysql-database-by-privileges-bitmask). Also [more info on bit functions in mysql here](https://dev.mysql.com/doc/refman/5.7/en/bit-functions.html) — JNevill, Nov 30 '17 at 14:54

score 0 · Answer 2 · answered Nov 29 '17 at 15:45

Use a separate table with doctor_id and day. The day should match MySQL's DAYOFWEEK results, though, i.e. 1 for Sunday, 2 for Monday, etc.

Then querying the data will be easy. e.g.

select *
from doctors
where doc_id in
(
  select doc_id
  from doc_workingdays
  where day = dayofweek(current_date)
);

Multiple boolean columns or a new table

2 Answers2