Как структурировать и запрашивать систему назначения на основе временных интервалов, где каждый регистрируемый объект имеет разный ежедневный график?

Я разрабатываю систему бронирования адвокатов, где человек может записаться на прием в определенное время в определенный день (следующий доступный адвокат день).

Скажем, это ZocDoc для юристов. Такая же структура, с назначением по времени: http://goo.gl/djUZb

Я использую MySQL и PHP.

Схема таблицы:

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
);

Точка 1)

Каждый адвокат имеет дефолтные временные интервалы в зависимости от дня недели (статус = 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         |
+-----------+-------------+-----------+-----------+

Точка 2)

Адвокат может добавить временной интервал к определенному дню (даже если этот день отличается от другого дня недели от его слотов по умолчанию) и также может блокировать ( status = -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        |
+-----------+-------------+-----------+-----------+-----------+

Точка 3)

Затем у нас назначены встречи. В этом случае мы заполняем slot_date и client_id:

+-----------+-------------+-----------+-----------+-----------+
| lawyer_id | day_of_week | slot_time | slot_date | client_id |
+-----------+-------------+-----------+-----------+-----------+
| 1         | 1           | 10:30     | 12/03/13  | 10        |
+-----------+-------------+-----------+-----------+-----------+

В качестве примера, при вышеуказанном бронировании и при условии, что он еще 6:30 того же дня (12/03/13), свободные свободные слоты, которые необходимо распечатать, следующие:

8:00 - default slot
8:30 - default slot
9:00 - default slot
16:00 - Specific slot inserted in point 2 for 12/03/13

Проблема:

Мне нужно вернуть следующую доступную дату и соответствующие свободные времена (по умолчанию - конкретные, минус заблокированные и забронированные). Я не могу просто сказать "время возвращения с понедельника, 10/10/13".

На странице результатов поиска я перечислю всех юристов и таблицу времени доступности для каждого. Таким образом, каждый адвокат будет иметь разные таблицы времени каждый раз, когда будет выполнен поиск.

Я не могу просто сказать "SELECT time FROM [связка объединений] WHERE date = today".

Я пришел с этим запросом, который игнорирует слоты, заблокированные (статус = -1) или забронированные (client_id not null), но, конечно, он не вернет свободные времена в течение ближайшего дня с доступными временами (или с сегодняшнего дня ):

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;

Другая проблема:, если сегодня есть day_of_week = 5, но следующий доступный day_of_week для данного адвоката равен 2, как я могу запросить это?

Как вернуть следующий ближайший и доступный day_of_week и aggregate, чтобы просто вернуть время с этого дня, а не все дни?

Одно возможное решение

Одна вещь, с которой я пришел, заключалась в том, чтобы создать 3 таблицы вместо одного:

default_slots: 3 столбца: lawyer_id, day_of_week, время
слоты: laywer_id, day_of_week, время, дата, статус
встречи: вся информация о забронированной встрече

Затем я буду хранить ВСЕ свободные временные интервалы для каждого дня фактической даты до года в таблице слотов для каждого адвоката. (взятые временные интервалы от 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         |
+-----------+-------------+-----------+-----------+-----------+

У меня также будут заданы задания cron, которые запускаются каждую неделю, что добавляет еще одну неделю бесплатных записей в слотах таблицы, а также удаляет прошлые записи, чтобы уменьшить размер таблицы и неиспользуемые данные.

Адвокат также сможет заблокировать время непосредственно в слотах, а также добавить конкретные времена (пункт 2).

Для листинга это будет вопрос получения слотов для даты, равной или большей, чем сегодня, со свободными моментами, так как каждый раз каждой даты будет строка.

Последствия для этого решения: 1) День первый у нас будет 2500 юристов (2-й месяц около 6000). Предполагая 8 возможных слотов/ежедневно X 20 дней работы/месяц X 12 месяцев = Записи в 1920 слотов для каждого адвоката.

2500 laywers x 1920 records = 4,8 миллиона записей в первый день. (~ 12M второй месяц)

Эти записи будут ОБНОВЛЕНА, ВСТАВЛЕНЫ и УДАЛЕНЫ все время. Таблица слотов имеет некоторые индексы, поэтому я не могу представить, что операции записи выполняются постоянно в таблице с записями 12M + и некоторыми индексами. Индексы, обновляемые каждую секунду, не видят меня умным.

Я действительно не могу прийти с разумным и масштабируемым решением. Мое решение только с одной таблицей может работать, но я не могу думать так, чтобы вообще этого не спрашивать. И таблица денормализованных слотов будет огромной, но при этом потребуются постоянные операции записи.

Любые советы?

Ответ 1

Я сделал что-то похожее на то, что вы пытаетесь сделать, поэтому я понимаю, насколько это сложно:)

Это было сделано в MSSQL, поэтому вам нужно будет преобразовать его в MySql.

Вот те таблицы, в которые мы закончили:

Таймслоты:

Мы сохраняем как временные таймслоты по умолчанию, так и измененные временные интервалы для каждого сотрудника в этой таблице (у нас есть столбец с именем "SlotType" в этой таблице. SlotType 1 = DEFAULT TIMESLOTS и SlotType 2 = MODIFIED TIMESLOTS). Если вы посмотрите на "Tue 30/04/13" на рисунке выше, вы увидите, что мы изменили временные интервалы на этот день, чтобы отображать только 9-часовую встречу для этого конкретного сотрудника.

ClosedDays:

Это список закрытых дней - например, сотрудник, которого я не работаю над днем рождения и Рождеством.

Назначения:

Это список назначений, которые были забронированы (или ждут подтверждения бронирования).

Запрос SQL для получения назначений:

Чтобы проверить назначение, мы использовали следующий SQL-код в нашей хранимой процедуре. Он проверяет назначение сотрудников на указанную дату. Окончательная хранимая процедура, в которой мы используем петли, хотя каждый сотрудник на странице для каждого дня недели, чтобы получить все назначения. Используя этот запрос, чтобы получить 10 штатных сотрудников в течение следующих 7 дней = всего 70 запросов и занимает около 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:
[email protected] = 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

Надеюсь, что это имеет смысл, и если у кого-то еще есть идея о том, как оптимизировать это, пожалуйста, дайте мне знать!

Ответ 2

Вы правы, что у вас будет большой стол. Но неясно, что ваше приложение в итоге потерпит неудачу. MySQL (и все программное обеспечение СУБД) сделано для быстрого доступа к большим таблицам.

Хорошее специализированное серверное серверное MySQL (с 64-разрядной ОС, двумя или четырьмя быстрыми процессорами, большим количеством ОЗУ и превосходными файловыми вводами - SAS-сопряженными быстрыми дисками), и правильно настроенное серверное программное обеспечение будет обрабатывать эту рабочую нагрузку,

Возможно, вы захотите объединить slot_time и slot_date в одно поле DATETIME или TIMESTAMP, которое может быть проиндексировано для удобства поиска. Если вы решите использовать элементы данных TIMESTAMP, вы получите отличные преимущества для обработки часовых поясов, если все будет правильно.

Возможно, вам захочется разобраться, как разделить свою большую таблицу, используя схему, позволяющую переносить данные в течение месяца или даже на неделю в автономном режиме, когда этот месяц или неделя не пройден.

С 2500 адвокатами, использующими вашу систему, вы захотите получить это право. Почему бы не потратить немного денег на достойного администратора базы данных? Они стоят меньше часа, чем большинство юристов. Шери Кабрал написал хорошее резюме о том, как его найти. http://www.sheeri.org/how-to-find-a-dba/