Best MySQL setup for a weekly default schedule with alternate schedules in case of

Question

Hello again Stackoverflow!

For the past couple of days I've been thinking about a proper method to save a weekly schedule with hour intervals.

This is the schedule:

Monday:
00:00 - 08:00: Sleeping
08:00 - 09:00: Shower & Breakfast
09:00 - 17:00: Work
17:00 - 18:00: Groceries
18:00 - 20:00: Making & Eating dinner
20:00 - 22:00: Relaxing
22:00 - 23:00: Shower & prepare for bed
23:00 - 24:00: Sleeping

and this for monday all the way till sunday, ofcourse with different times and different things to do each day of the week. And at the end of the week, if there's not a different schedule for the upcoming week, it's fine, but if there is a different schedule for week 34, that the user can take the current schedule, keep the adjustments and "schedule" the new schedule for week 34 only.

Now as I've said, over the last couple of days I've been thinking of a proper way to have a MySQL setup for this, but I haven't thought of any. So could you guys please help me think of a proper MySQL setup for this?

Answer 1

Maybe this will get things started:

days_of_week
+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | Monday            |
|  2 | Tuesday           |
|  3 | Wednesday         |
|  4 | Thursday          |
|  5 | Friday            |
|  6 | Satday            |
|  7 | Sunday            |
+----+-------------------+

users
+----+-------------------+-----
| id | name              | ...
+----+-------------------+-----
|  1 | John              | ...
+----+-------------------+-----


schedules
+----+---------+-------------------+------------+
| id | user_id | name              | is_default |
+----+---------+-------------------+------------+
|  1 |       1 | Weekly Default    |          Y |
|  2 |       1 | Vacation          |          N |
+----+---------+-------------------+------------+

schedule_details
(NULL in day_of_week_id is the schedule for any day that is not explicitly set)
+----+-------------+----------------+-----------+-----------+--------------------------+
| id | schedule_id | day_of_week_id | from_time | thru_time | description              |
+----+-------------+----------------+-----------+-----------+--------------------------+
|  1 |           1 |           NULL |     00:00 |     08:00 | Sleeping                 |
|  2 |           1 |           NULL |     08:00 |     09:00 | Shower & Breakfast       |
|  3 |           1 |           NULL |     09:00 |     17:00 | Work                     |
|  4 |           1 |           NULL |     17:00 |     18:00 | Groceries                |
|  5 |           1 |           NULL |     18:00 |     20:00 | Making & Eating dinner   |
|  6 |           1 |           NULL |     20:00 |     22:00 | Relaxing                 |
|  7 |           1 |           NULL |     22:00 |     23:00 | Shower & prepare for bed |
|  8 |           1 |           NULL |     23:00 |     24:00 | Sleeping                 |
|  9 |           1 |              6 |     00:00 |     10:00 | Sleeping                 |
| 10 |           1 |              6 |     10:00 |     11:00 | Shower & Breakfast       |
| 11 |           1 |              6 |     11:00 |     17:00 | Play Golf                |
| 12 |           1 |              6 |     17:00 |     18:00 | Groceries                |
| 13 |           1 |              6 |     18:00 |     20:00 | Making & Eating dinner   |
| 14 |           1 |              6 |     20:00 |     22:00 | Relaxing                 |
| 15 |           1 |              6 |     22:00 |     23:00 | Shower & prepare for bed |
| 16 |           1 |              6 |     23:00 |     24:00 | Sleeping                 |
| 17 |           1 |              7 |     00:00 |     10:00 | Sleeping                 |
| 18 |           1 |              7 |     10:00 |     11:00 | Shower & Breakfast       |
| 19 |           1 |              7 |     11:00 |     17:00 | Go Sailing               |
| 20 |           1 |              7 |     17:00 |     18:00 | Groceries                |
| 21 |           1 |              7 |     18:00 |     20:00 | Making & Eating dinner   |
| 22 |           1 |              7 |     20:00 |     22:00 | Relaxing                 |
| 23 |           1 |              7 |     22:00 |     23:00 | Shower & prepare for bed |
| 24 |           1 |              7 |     23:00 |     24:00 | Sleeping                 |
| 25 |           2 |           NULL |     00:00 |     10:00 | Sleeping                 |
| 26 |           2 |           NULL |     10:00 |     11:00 | Shower & Breakfast       |
| 27 |           2 |           NULL |     11:00 |     17:00 | Play Golf                |
| 28 |           2 |           NULL |     18:00 |     20:00 | Go out to dinner         |
| 29 |           2 |           NULL |     20:00 |     22:00 | Relaxing                 |
| 30 |           2 |           NULL |     22:00 |     23:00 | Shower & prepare for bed |
| 31 |           2 |           NULL |     23:00 |     24:00 | Sleeping                 |
+----+-------------+----------------+-----------+-----------+--------------------------+

weekly_schedules
+----+---------+---------+-------------+
| id | user_id | week_no | schedule_id |
+----+---------+---------+-------------+
|  1 |       1 |      34 |           2 |
+----+---------+---------+-------------+

Selecting a weekly schedule:

SELECT dow.id
      ,dow.name
      ,sd.from_time
      ,sd.thru_time
      ,sd.description

  FROM days_of_week      dow

  JOIN users             u
    ON u.id              = :user_id

  LEFT OUTER
  JOIN weekly_schedules  ws
    ON ws.user_id        = u.id
   AND ws.week_no        = :week_no

  JOIN schedules         s
    ON s.user_id         = u.id
   AND ( (ws.week_no IS NULL AND s.is_default = 'Y')
      OR (ws.week_no IS NOT NULL AND s.id = ws.schedule_id)
       )

  LEFT OUTER
  JOIN (SELECT DISTINCT schedule_id, day_of_week_id
          FROM schedules        ss
          JOIN schedule_details sds
            ON ss.user_id       = :user_id
           AND sds.schedule_id  = ss.id
           AND sds.day_of_week_id IS NOT NULL
       ) sdow
    ON sdow.schedule_id    = s.id
   AND sdow.day_of_week_id = dow.id

  JOIN schedule_details  sd
    ON sd.schedule_id      = s.id
   AND ( (sdow.day_of_week_id IS NOT NULL AND sd.day_of_week_id = sdow.day_of_week_id)
      OR (sdow.day_of_week_id IS NULL AND sd.day_of_week_id IS NULL)
       )

 ORDER BY dow.id, sd.from_time

SQLFiddle: http://sqlfiddle.com/#!2/7fc91/8 Test it by changing AND ws.week_no = 22 to AND ws.week_no = 34.

User can have any number of "schedules" of which one must be, and only one can be the default schedule. The default schedule is used for any week that doesn't have an explicit schedule override.

Each schedule can have any number of schedule_details identifying the activities to be taken throughout the week.

Each schedule's details can include one default day (identified by NULL in the day_of_week_id column) with any number of activities for that day. Any day that is not explicitly defined will use the default day's schedule.

Updated

If you want to be able to retain historical schedules, then you would want to effective date the default schedule. Eliminate the schedules.is_default column and replace it with another table:

schedule_defaults
+---------+------------+-------------+
| user_id |schedule_id | eff_week_no |
+---------+------------+-------------+
|       1 |          1 |          18 |
+---------+------------+-------------+

Then, adjust the SELECT accordingly.

SELECT u.name
      ,dow.id
      ,dow.name
      ,sd.from_time
      ,sd.thru_time
      ,sd.description

  FROM days_of_week      dow

  JOIN users             u
    ON u.id              = :user_id

  LEFT OUTER
  JOIN weekly_schedules  ws
    ON ws.user_id        = u.id
   AND ws.week_no        = :week_no

  JOIN schedule_defaults sdef
    ON sdef.user_id      = u.id
   AND sdef.eff_week_no  = (SELECT MAX(eff_week_no)
                              FROM schedule_defaults
                             WHERE user_id      = :user_id
                               AND eff_week_no <= :week_no
                           )

  JOIN schedules         s
    ON s.user_id         = u.id
   AND ( (ws.week_no IS NULL AND s.id = sdef.schedule_id)
      OR (ws.week_no IS NOT NULL AND s.id = ws.schedule_id)
       )

  LEFT OUTER
  JOIN (SELECT DISTINCT schedule_id, day_of_week_id
          FROM schedules        ss
          JOIN schedule_details sds
            ON ss.user_id       = :user_id
           AND sds.schedule_id  = ss.id
           AND sds.day_of_week_id IS NOT NULL
       ) sdow
    ON sdow.schedule_id    = s.id
   AND sdow.day_of_week_id = dow.id

  JOIN schedule_details  sd
    ON sd.schedule_id      = s.id
   AND ( (sdow.day_of_week_id IS NOT NULL AND sd.day_of_week_id = sdow.day_of_week_id)
      OR (sdow.day_of_week_id IS NULL AND sd.day_of_week_id IS NULL)
       )

 ORDER BY dow.id, sd.from_time

SQLFiddle for retaining history: http://sqlfiddle.com/#!2/e721c/10

Answer 2

I think there is no 'best' answer to your question. I give you some schema but there could be more answers which would be good as well.

shedule_map          schedule       activity
------------------------------------------------
id                    id             id
user_id               schedule_id    schedule_id  
default_schedule_id   user_id        date
                      startOfWeek    start_hour
                                     end_hour
                                     activity_description

'Schedule' table every week will gain one entry per user. If user choose default schedule for current week in 'schedule' table schedule_id will be obtained from schedule_map. Elswhere schedule_id = MAX(schedule_id) + 1 and new activities for this new schedule will be created. Lets get today's activities from that schema for user_id = 10:

SELECT act.start_hour, act.end_hour, act.activity_descripton
FROM activity act
INNER JOIN schedule s
USING (schedule_id)
WHERE s.startOfWeek = (
  SELECT MAX(startOfWeek)
  FROM schedule
  WHERE startOfWeek < NOW()
)
AND s.user_id = 10
ORDER BY act.start_hour

I use mostly ORMs and there are problems with subqueries so startOfWeek adequate to current date you can obtain from other query.

I don't think one additional entry per user per week can be a problem. But if you think it is then you can add to 'schedule_map' table column 'custom_schedule_id' setting it to NULL if user choose default schedule. But then you would have to determine firstly if user use custom schedule or not or more complicated query will be needed. On example

SELECT act.start_hour, act.end_hour, act.activity_descripton
FROM activity act
INNER JOIN schedule s
ON act.schedule_id = s.schedule_id
INNER JOIN schedule_map sm
ON sm.user_id = s.user_id 
AND sm.custom_schedule_id = s.schedule_id
WHERE s.user_id = 10
ORDER BY act.start_hour
UNION ALL
SELECT act.start_hour, act.end_hour, act.activity_descripton
FROM activity act
INNER JOIN schedule s
ON act.schedule_id = s.schedule_id
INNER JOIN schedule_map sm
ON sm.user_id = s.user_id 
AND sm.default_schedule_id = s.schedule_id 
AND sm.custom_schedule_id IS NULL
WHERE s.user_id = 10
ORDER BY act.start_hour