再次问好堆栈溢出！
在过去的几天里，我一直在考虑一种适当的方法来保存每周的时间表，每隔一小时。
这是时间表：
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
周一一直到周日，当然，一周中的每一天都有不同的时间和不同的事情要做。在一周结束时，如果下一周没有不同的时间表，那很好，但如果第 34 周有不同的时间表，用户可以采用当前时间表，保留调整并"安排"新时间表只在第 34 周。
现在正如我所说，在过去的几天里，我一直在想一种正确的方法来为此设置MySQL，但我还没有想到任何方法。那么你们能帮我想出一个合适的MySQL设置吗？
也许这会让事情开始：
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 |
+----+---------+---------+-------------+
选择每周时间表：
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 通过将AND ws.week_no = 22更改为AND ws.week_no = 34来测试它。
用户可以有任意数量的"计划"，其中一个必须是，并且只有一个可以是默认计划。默认计划用于没有显式计划替代的任何一周。
每个时间表都可以有任意数量的schedule_details来确定整个星期要进行的活动。
每个计划的详细信息可以包括一个默认日期（由day_of_week_id列中的 NULL 标识）以及该天的任意数量的活动。任何未明确定义的日期都将使用默认日期的计划。
更新
如果希望能够保留历史计划，则需要使默认计划生效日期。删除schedules.is_default列并将其替换为另一个表：
schedule_defaults
+---------+------------+-------------+
| user_id |schedule_id | eff_week_no |
+---------+------------+-------------+
| 1 | 1 | 18 |
+---------+------------+-------------+
然后，相应地调整选择。
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：http://sqlfiddle.com/#!2/e721c/10
我认为你的问题没有"最佳"答案。我给你一些模式，但可能还有更多的答案，这也很好。
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_id将从schedule_map获得。Elswhere schedule_id = MAX（schedule_id） + 1，将为此新计划创建新活动。让我们从该架构中获取今天的活动，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
我主要使用ORM，并且子查询存在问题，因此可以从其他查询中获得的当前日期的startOfWeek。
我不认为每个用户每周增加一个条目会成为问题。但是，如果您认为是，那么如果用户选择默认计划，则可以将其custom_schedule_id schedule_map设置为NULL，则可以将其设置为NULL。但是，您必须首先确定用户是否使用自定义计划，或者需要更复杂的查询。在示例中
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