MySQL 如何根据每个可预订实体每天都有不同时间表的时间段构建和查询约会系统？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15627408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to structure and query an appointment system based on time slots where each bookable entity has a different time table daily?
提问by AlfredBaudisch
I'm developing a lawyer booking system, where a person can book an appointment at a given time in a given day (the next lawyer's available day).
我正在开发一个律师预约系统，一个人可以在给定日期的给定时间（下一个律师有空的日子）预约。
Let's say it is a ZocDoc for lawyers. The same structure, with appointments based on time: http://goo.gl/djUZb
假设它是律师的ZocDoc。相同的结构，基于时间的约会：http: //goo.gl/djUZb
I'm using MySQL and PHP.
我正在使用 MySQL 和 PHP。
The table schema:
表架构：
CREATE TABLE `laywer_appointments` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`lawyer_id` INT unsigned,
`day_of_week` tinyint(3) unsigned DEFAULT '1',
`slot_date` date DEFAULT NULL,
`slot_time` time DEFAULT NULL,
`status` tinyint(4) NOT NULL DEFAULT '0',
`client_id` int(11) DEFAULT NULL, -- client_id = NULL means free slot
);
Point 1)
第 1 点）
Each lawyer has default time slots based on the day of week (status = 0 means available). When inserting default slots, I don't provide a date, just day_of_week. Example data:
每个律师都有基于星期几的默认时间段（状态 = 0 表示可用）。插入默认插槽时，我不提供日期，只提供 day_of_week。示例数据：
+-----------+-------------+-----------+-----------+
| lawyer_id | day_of_week | slot_time | status |
+-----------+-------------+-----------+-----------+
| 1 | 1 | 08:00 | 0 |
| 1 | 1 | 08:30 | 0 |
| 1 | 1 | 09:00 | 0 |
| 1 | 1 | 10:30 | 0 |
| 1 | 4 | 14:30 | 0 |
| 1 | 4 | 16:40 | 0 |
| 2 | 1 | 10:20 | 0 |
| 2 | 1 | 14:00 | 0 |
| 2 | 3 | 15:50 | 0 |
+-----------+-------------+-----------+-----------+
Point 2)
第 2 点）
A lawyer can add a time slot to a specific day(even if this day is from a different day of week from his default slots) and can also lock(status = -1) one of the default slots in a specific day (i.e. he is on a meeting or he is sick):
律师可以将时间段添加到特定日期（即使这一天与他的默认时间段来自不同的一周中的一天），并且还可以锁定（状态 = -1）特定日期的默认时间段之一（即他正在开会或生病）：
+-----------+-------------+-----------+-----------+-----------+
| lawyer_id | day_of_week | slot_time | slot_date | status |
+-----------+-------------+-----------+-----------+-----------+
| 1 | 1 | 16:00 | 12/03/13 | 0 |
| 1 | 6 | 11:00 | 26/04/13 | 0 |
| 1 | 6 | 12:00 | 26/04/13 | 0 |
| 2 | 1 | 10:00 | 01/01/13 | -1 |
+-----------+-------------+-----------+-----------+-----------+
Point 3)
第 3 点）
Then we have appointments booked. In this case we fill the slot_date and the client_id:
然后我们就预约了。在这种情况下，我们填写 slot_date 和 client_id：
+-----------+-------------+-----------+-----------+-----------+
| lawyer_id | day_of_week | slot_time | slot_date | client_id |
+-----------+-------------+-----------+-----------+-----------+
| 1 | 1 | 10:30 | 12/03/13 | 10 |
+-----------+-------------+-----------+-----------+-----------+
As an example, with the above booking and assuming it is still 6:30 of the same day (12/03/13), the free available slots that have to be printed are:
例如，对于上述预订并假设它仍然是同一天 (12/03/13) 的 6:30，必须打印的免费可用插槽是：
8:00 - default slot
8:30 - default slot
9:00 - default slot
16:00 - Specific slot inserted in point 2 for 12/03/13
The problem:
问题：
I have to return the next available date and the related free times (default ones, specific ones minus locked ones and booked ones). I can't just say "return times from Monday, 10/10/13".
我必须返回下一个可用日期和相关的空闲时间（默认的、特定的减去锁定的和预订的）。我不能只说“从 13 年 10 月 10 日星期一开始的返回时间”。
In a search results page, I'll list all lawyers and the availability time table for each. So that means each lawyer will have a different time table every time a search is made.
在搜索结果页面中，我将列出所有律师以及每个. 因此，这意味着每次进行搜索时，每位律师都会有不同的时间表。
I can't simply say "SELECT time FROM [bunch of joins] WHERE date = today".
我不能简单地说"SELECT time FROM [bunch of joins] WHERE date = today"。
I came with this query which ignores slots that are locked (status = -1) or booked (client_id not null), but of course it won't return the free times for the closest day with available times (or from today):
我带来了这个查询，它忽略了锁定（状态 = -1）或预订（client_id 不为空）的插槽，但当然它不会返回最近一天的空闲时间和可用时间（或从今天开始）：
SELECT p.day_of_week, p.slot_date, p.slot_time
FROM laywer_appointments p
WHERE p.client_id IS NULL AND p.status = 0
AND p.slot_time NOT IN (
SELECT s.slot_time FROM laywer_appointments s
WHERE (s.slot_date IS NOT NULL AND s.client_id IS NOT NULL
OR s.status = -1) AND s.day_of_week = p.day_of_week
)
GROUP BY p.day_of_week, p.slot_date, p.slot_time
ORDER BY p.day_of_week ASC, p.slot_time ASC;
Another problem:if today is day_of_week = 5, but the next available day_of_week for a given lawyer is 2, how can I query that?
另一个问题：如果今天是 day_of_week = 5，但是给定律师的下一个可用 day_of_week 是 2，我该如何查询？
How to return the next closest and available day_of_week and aggregate to just return times from this day, not all days?
如何返回下一个最接近和可用的 day_of_week 并聚合以仅返回这一天的时间，而不是所有天？
One possible solution
一种可能的解决方案
One thing I came with was to create 3 tables instead of one:
我带来的一件事是创建 3 个表而不是一个：
- default_slots: 3 columns: lawyer_id, day_of_week, time
- slots: laywer_id, day_of_week, time, date, status
- appointments: all info regarding a booked appointment
- default_slots：3 列：lawyer_id、day_of_week、时间
- 槽位：laywer_id、day_of_week、时间、日期、状态
- 约会：关于预约的所有信息
Then I'll store ALL free time slots for every day of the actual date up to an year in the slots table for every lawyer.(taken time slots from default_slots).
然后，我将在每个律师的时隙表中存储实际日期中每天最多一年的所有空闲时间段。（从 default_slots 获取时隙）。
+-----------+-------------+-----------+-----------+-----------+
| lawyer_id | day_of_week | slot_time | slot_date | status |
+-----------+-------------+-----------+-----------+-----------+
| 1 | 1 | 16:00 | 12/03/13 | 0 |
| 1 | 1 | 16:00 | 12/03/13 | 0 |
| 1 | 2 | 08:00 | 13/03/13 | 0 |
| 1 | 2 | 09:00 | 13/03/13 | 0 |
... next week
| 1 | 1 | 16:00 | 19/03/13 | 0 |
| 1 | 1 | 16:00 | 19/03/13 | 0 |
| 1 | 2 | 08:00 | 20/03/13 | 0 |
| 1 | 2 | 09:00 | 20/03/13 | 0 |
... up to an year
| 1 | 1 | 16:00 | 20/03/14 | 0 |
| 1 | 1 | 16:00 | 20/03/14 | 0 |
| 1 | 2 | 08:00 | 21/03/14 | 0 |
| 1 | 2 | 09:00 | 21/03/14 | 0 |
+-----------+-------------+-----------+-----------+-----------+
I'll also have some cron jobs that run every week that adds another week of free slot records in the table slots and also remove past records to reduce table size and unused data.
我还会有一些每周运行的 cron 作业，它们会在表槽中再添加一周的空闲槽记录，并删除过去的记录以减少表大小和未使用的数据。
A lawyer will also be able to lock a time directly into slots, as well add specific times (point 2).
律师还可以将时间直接锁定到插槽中，以及添加特定时间（第 2 点）。
For the listing, it will be a matter of getting the slots for a date equal or greater than today with free times, since every time of every date will have a row.
对于列表，需要在空闲时间获取等于或大于今天的日期的插槽，因为每个日期的每个时间都会有一行。
Implications on this solution: 1) Day one we will have 2500 lawyers (2nd month around 6000). Assuming 8 possible slots/daily X 20 days of work/month X 12 months = 1920 slot records per lawyer.
对此解决方案的影响： 1) 第一天我们将有 2500 名律师（第二个月大约 6000 名）。假设 8 个可能的时段/每天 X 20 天的工作/月 X 12 个月 =每位律师 1920 个时段记录。
2500 laywers x 1920 records = 4,8 million records on day one.(~12M the second month)
2500 位员工 x 1920 条记录 = 第一天的 480 万条记录。（第二个月~12M）
Those records will be UPDATED, INSERTED and DELETED all the time. The slots table has some indexes, so I can't imagine write operations being made constantly on a table with 12M+ records and some indexes.Indexes being updated every second doesn't see smart to me.
这些记录将一直更新、插入和删除。slot 表有一些索引，所以我无法想象在一个包含 1200 万条记录和一些索引的表上不断进行写操作。每秒更新的索引对我来说并不明智。
I really can't come with a reasonable and scalable solution. My solution with one table only could work, but I can't think in a way of querying that at all. And the denormalized slots table will be huge, while needing constant write operations.
我真的无法提供合理且可扩展的解决方案。我只有一张表的解决方案可以工作，但我根本无法以查询的方式思考。并且非规范化槽表将是巨大的，同时需要不断的写操作。
Any tips?
有小费吗？
采纳答案by MWD
I have done something similar to what your trying to do so I understand how complicated it is :)
我做了一些类似于你尝试做的事情，所以我明白它有多复杂:)
This was done in MSSQL so you will need to convert it to MySql.
这是在 MSSQL 中完成的，因此您需要将其转换为 MySql。
These are the tables we ended up with:
这些是我们最终得到的表：
TimeSlots:
时隙：
We store both the default timesslots & modified timeslots for each staff member in this table (We have a column called "SlotType" in this table. SlotType 1 = DEFAULT TIMESLOTS & SlotType 2 = MODIFIED TIMESLOTS). If you look at "Tue 30/04/13" in the picture above you will see that we modified the timeslots for that day to only display a 9am appointment for this particular staff member.
我们在这个表中存储了每个员工的默认时间段和修改的时间段（我们在这个表中有一个名为“SlotType”的列。SlotType 1 = DEFAULT TIMESLOTS & SlotType 2 = MODIFIED TIMESLOTS）。如果您查看上图中的“2013 年 4 月 30 日星期二”，您会看到我们修改了当天的时间段，仅显示该特定工作人员的上午 9 点约会。
ClosedDays:
休息日：
This is a list of closed days - for example a staff member my not work on his birthday & Christmas day.
这是关闭日期的列表 - 例如，一名工作人员在他的生日和圣诞节那天不工作。
Appointments:
约会：
This is a list of appointments that have been booked (or waiting for booking confirmation).
这是已预订（或等待预订确认）的约会列表。
SQL Query to get available appointments:
获取可用约会的 SQL 查询：
To check to appointments we then used the following SQL in our stored procedure. It checks one staff members appointments for the date specified. The final stored procedure that we are using loops though each staff member on the page for each day of the week to get all the appointments. Using this query to get 10 staff members appointments for the next 7 days = a total of 70 query's & takes about 300ms with a million records in each table. We are loading the appointments via ajax so 300ms is acceptable for our use & will prob change it to get each staff members appointments separately via ajax (so 7 query's at a time) to improve performance even more in the future.
为了检查约会，我们在存储过程中使用了以下 SQL。它检查指定日期的一名工作人员的任命。我们使用的最后一个存储过程通过页面上的每个工作人员在一周中的每一天循环获取所有约会。使用此查询获取接下来 7 天的 10 名员工预约 = 总共 70 次查询，每个表中有 100 万条记录，耗时约 300 毫秒。我们正在通过 ajax 加载约会，因此 300 毫秒对于我们的使用是可以接受的，并且可能会更改它以通过 ajax 分别获取每个员工的约会（一次 7 个查询），以在未来进一步提高性能。
DECLARE @MyDate date, @MyDayName nvarchar(10);
IF @StartDate IS NULL
SET @StartDate = GETDATE();
SET @MyDate = CAST(@StartDate AS date);
SET @MyDayName = DATENAME(dw, @MyDate );
--NOTES:
--@SlotType = 1 (DEFAULT TIMESLOTS), 2 (MODIFIED TIMESLOTS)
--***CHECK TO SEE IF DOCTOR IS CLOSED TODAY***
IF NOT EXISTS (SELECT [ClosedDays].[ID] FROM [ClosedDays] WHERE [ClosedDays].[StaffID] = @StaffID AND [ClosedDays].[BusinessID] = @BusinessID AND [ClosedDays].[Active] = 1 AND @MyDate BETWEEN [ClosedDays].[StartDate] AND [ClosedDays].[EndDate])
BEGIN
--***THE DOCTOR IS NOT CLOSED TODAY SO GET THE AVAILABLE TIMESLOTS***
--***CHECK TO SEE IF DOCTOR IS HAS MODIED TIMESLOTS TODAY***
IF NOT EXISTS (SELECT [TimeSlots].[ID], @MyDate AS SlotDate FROM [TimeSlots] WHERE [TimeSlots].[StaffID] = @StaffID AND [TimeSlots].[BusinessID] = @BusinessID AND [TimeSlots].[Active] = 1 AND [TimeSlots].[SlotType] = 2 AND [TimeSlots].[SlotDay] = @MyDayName AND @MyDate BETWEEN [TimeSlots].[StartDate] AND [TimeSlots].[EndDate] AND [TimeSlots].[ID] NOT IN (SELECT [Appointments].[TimeSlotID] FROM [Appointments]) )
BEGIN
--***THE DOCTOR HAS NO MODIFIED TIMESLOTS FOR TODAY USE THE DEFAULT ONES***
SELECT [TimeSlots].[ID] AS SlotID, [TimeSlots].[StaffID], [TimeSlots].[BusinessID], CONVERT(nvarchar(10), @MyDate, 103) AS SlotDate, [TimeSlots].[SlotDay], LTRIM(RIGHT(CONVERT(nvarchar(10), [TimeSlots].[SlotTime], 100), 7))AS SlotTime FROM [TimeSlots]
WHERE [TimeSlots].[StaffID] = @StaffID AND [TimeSlots].[BusinessID] = @BusinessID AND [TimeSlots].[Active] = 1 AND [TimeSlots].[SlotType] = 1 AND [TimeSlots].[SlotDay] = @MyDayName AND @MyDate BETWEEN [TimeSlots].[StartDate] AND [TimeSlots].[EndDate] AND NOT EXISTS (SELECT [Appointments].[TimeSlotID] FROM [Appointments] WHERE [Appointments].[TimeSlotID] = [TimeSlots].[ID])
END
ELSE
BEGIN
--***THE DOCTOR HAS MODIFIED TODAYS TIMESLOTS SO USE THE MODIFIED TIMESLOTS***
SELECT [TimeSlots].[ID] AS SlotID, [TimeSlots].[StaffID], [TimeSlots].[BusinessID], CONVERT(nvarchar(10), @MyDate, 103) AS SlotDate, [TimeSlots].[SlotDay], LTRIM(RIGHT(CONVERT(nvarchar(10), [TimeSlots].[SlotTime], 100), 7))AS SlotTime FROM [TimeSlots]
WHERE [TimeSlots].[StaffID] = @StaffID AND [TimeSlots].[BusinessID] = @BusinessID AND [TimeSlots].[Active] = 1 AND [TimeSlots].[SlotType] = 2 AND [TimeSlots].[SlotDay] = @MyDayName AND @MyDate BETWEEN [TimeSlots].[StartDate] AND [TimeSlots].[EndDate] AND NOT EXISTS (SELECT [Appointments].[TimeSlotID] FROM [Appointments] WHERE [Appointments].[TimeSlotID] = [TimeSlots].[ID])
END
END
ELSE
BEGIN
--***NO APPOINTMENTS WERE FOUND***
--***DUMMY QUERY TO RETURN NO RECORDS***
SELECT [TimeSlots].[ID] AS SlotID, [TimeSlots].[StaffID], [TimeSlots].[BusinessID], CONVERT(nvarchar(10), @MyDate, 103) AS SlotDate, [TimeSlots].[SlotDay], LTRIM(RIGHT(CONVERT(nvarchar(10), [TimeSlots].[SlotTime], 100), 7))AS SlotTime FROM [TimeSlots]
WHERE [TimeSlots].[ID] = -0
END
Hope this makes sense & if anyone else has some idea's on how to optimize this more please let me know!
希望这是有道理的，如果其他人对如何优化这个有一些想法，请告诉我！
回答by O. Jones
You're right that you'll have a big table. But it's not clear that your application will fail as a result. MySQL (and all DBMS software) is made to allow the accessing of large tables quickly.
你是对的，你会有一张大桌子。但不清楚您的应用程序是否会因此失败。MySQL（以及所有 DBMS 软件）旨在允许快速访问大型表。
Good dedicated MySQL server hardware (which has a 64-bit OS, two or four fast processors, plenty of RAM, and excellent file I/O -- SAS-interfaced fast disks) and properly configured server software will handle this workload.
良好的专用 MySQL 服务器硬件（具有 64 位操作系统、两个或四个快速处理器、充足的 RAM 和出色的文件 I/O——SAS 接口的快速磁盘）和正确配置的服务器软件将处理此工作负载。
You may wish to merge slot_time and slot_date into a single DATETIME or TIMESTAMP field, that can be indexed for ease of searching. If you choose to use TIMESTAMP data items you'll get some nice timezone-handling benefits if you do things right.
您可能希望将 slot_time 和 slot_date 合并到单个 DATETIME 或 TIMESTAMP 字段中，这些字段可以编入索引以便于搜索。如果您选择使用 TIMESTAMP 数据项，如果您做对了，您将获得一些不错的时区处理优势。
You may want to work out how to partition your big table using a scheme that lets you take a month's worth, or even a week's worth, of data offline when that month or week is past.
您可能想弄清楚如何使用一种方案对大表进行分区，该方案允许您在一个月或一周过去后离线获取一个月甚至一周的数据。
With 2,500 lawyers using your system, you're going to want to get this right. Why not spend some money on a decent database administrator? They cost less per hour than most lawyers. Sheeri Cabral wrote up a good summary of how to find one. http://www.sheeri.org/how-to-find-a-dba/
有 2,500 名律师在使用您的系统，您一定会希望做到这一点。为什么不花钱请一个像样的数据库管理员呢？他们每小时的费用低于大多数律师。Sheeri Cabral 写了一篇关于如何找到一个很好的总结。http://www.sheeri.org/how-to-find-a-dba/
