mysql - textuales - et al apa

¿Cómo estructurar y consultar un sistema de citas basado en intervalos de tiempo donde cada entidad que se puede reservar tiene un horario diferente cada día?

Question

Estoy desarrollando un sistema de reserva de abogados, donde una persona puede reservar una cita en un momento determinado en un día determinado (el día disponible para el próximo abogado).

Digamos que es un ZocDoc para abogados . La misma estructura, con citas basadas en el tiempo: http://goo.gl/djUZb

Estoy usando MySQL y PHP.

El esquema de la tabla:

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

Punto 1)

Cada abogado tiene intervalos de tiempo predeterminados basados ​​en el día de la semana (estado = 0 significa disponible). Al insertar ranuras predeterminadas, no proporciono una fecha, solo day_of_week. Ejemplo de datos:

+-----------+-------------+-----------+-----------+
| 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         |
+-----------+-------------+-----------+-----------+

Punto 2)

Un abogado puede agregar un intervalo de tiempo a un día específico ( incluso si este día es de un día de la semana diferente a sus ranuras predeterminadas ) y también puede bloquear ( estado = -1 ) uno de los espacios predeterminados en un día específico (es decir, está en una reunión o está enfermo):

+-----------+-------------+-----------+-----------+-----------+
| 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        |
+-----------+-------------+-----------+-----------+-----------+

Punto 3)

Luego tenemos citas reservadas. En este caso, completamos el slot_date y el client_id:

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

Como ejemplo, con la reserva anterior y asumiendo que todavía son las 6:30 del mismo día (03/12/13), las franjas horarias disponibles que deben imprimirse son:

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

El problema:

Tengo que devolver la siguiente fecha disponible y los tiempos libres relacionados (por defecto, específicos menos bloqueados y reservados). No puedo decir "tiempos de regreso del lunes, 10/10/13".

En una página de resultados de búsqueda, voy a enumerar todos los abogados y la tabla de tiempo de disponibilidad para cada uno . Entonces eso significa que cada abogado tendrá un horario diferente cada vez que realice una búsqueda.

No puedo decir simplemente "SELECCIONE tiempo DESDE [grupo de uniones] DONDE fecha = hoy" .

Vine con esta consulta que ignora los espacios que están bloqueados (estado = -1) o reservados (client_id no nulo), pero por supuesto no devolverá los tiempos libres para el día más cercano con los horarios disponibles (o desde hoy):

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;

Otro problema: si hoy es day_of_week = 5, pero el próximo day_of_week disponible para un abogado determinado es 2, ¿cómo puedo consultar eso?

¿Cómo devolver el día más cercano y disponible de day_of_week y agregar solo los tiempos de devolución de este día, no todos los días?

Una posible solución

Una cosa que vine fue crear 3 tablas en lugar de una:

• default_slots: 3 columnas: lawyer_id, day_of_week, time
• slots: laywer_id, day_of_week, hora, fecha, estado
• citas: toda la información con respecto a una cita reservada

Luego, almacenaré TODOS los espacios de tiempo libre para cada día de la fecha real hasta un año en la tabla de tragamonedas para cada abogado. (intervalos de tiempo tomados de 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         |
+-----------+-------------+-----------+-----------+-----------+

También tendré algunos trabajos cron que se ejecutan todas las semanas que agregan otra semana de registros de franjas libres en los espacios de tablas y también eliminan registros pasados ​​para reducir el tamaño de la tabla y los datos no utilizados.

Un abogado también podrá bloquear un tiempo directamente en las máquinas tragamonedas, y agregar tiempos específicos (punto 2).

Para el listado, será cuestión de obtener los espacios para una fecha igual o mayor que la actual con los tiempos libres, ya que cada vez de cada fecha tendrá una fila .

Implicaciones sobre esta solución: 1) El primer día tendremos 2500 abogados (2 ° mes, alrededor de 6000). Asumiendo 8 posibles espacios / diarios X 20 días de trabajo / mes X 12 meses = 1920 registros de puestos por abogado.

2500 laywers x 1920 records = 4,8 millones de registros el primer día. (~ 12M el segundo mes)

Esos registros serán ACTUALIZADOS, INSERTADOS y ELIMINADOS todo el tiempo. La tabla de ranuras tiene algunos índices, por lo que no puedo imaginar que las operaciones de escritura se realicen constantemente en una tabla con más de 12 millones de registros y algunos índices. Los índices que se actualizan cada segundo no me parecen inteligentes.

Realmente no puedo ofrecer una solución razonable y escalable. Mi solución con una sola tabla solo podría funcionar, pero no puedo pensar en una manera de consultar eso en absoluto. Y la tabla de tragamonedas desnormalizada será enorme, mientras que necesita operaciones de escritura constantes.

¿Algun consejo?

Answer 1

Tienes razón en que tendrás una gran mesa. Pero no está claro que su aplicación fallará como resultado. MySQL (y todo el software DBMS) está hecho para permitir el acceso de tablas grandes rápidamente.

Un buen servidor dedicado de MySQL (que tiene un sistema operativo de 64 bits, dos o cuatro procesadores rápidos, mucha RAM y una excelente E / S de archivos (discos rápidos interconectados con SAS) y un software de servidor correctamente configurado manejarán esta carga de trabajo.

Es posible que desee fusionar slot_time y slot_date en un solo campo DATETIME o TIMESTAMP, que puede indexarse ​​para facilitar la búsqueda. Si eliges utilizar los elementos de datos TIMESTAMP obtendrás algunos buenos beneficios de manejo de la zona horaria si haces las cosas bien.

Es posible que desee averiguar cómo particionar su gran mesa utilizando un esquema que le permite tomar un mes o incluso una semana de datos sin conexión cuando ese mes o semana haya pasado.

Con 2,500 abogados usando su sistema, querrá hacerlo bien. ¿Por qué no gastar algo de dinero en un administrador de base de datos decente? Cuestan menos por hora que la mayoría de los abogados. Sheeri Cabral escribió un buen resumen de cómo encontrar uno. http://www.sheeri.org/how-to-find-a-dba/

Answer 2

He hecho algo similar a lo que intentas hacer, por lo que entiendo lo complicado que es :)

Esto se hizo en MSSQL, por lo que deberá convertirlo a MySql.

Estas son las tablas con las que terminamos:

Ranuras de tiempo:

Almacenamos los timestlots predeterminados y los intervalos de tiempo modificados para cada miembro del personal en esta tabla (Tenemos una columna llamada "SlotType" en esta tabla. SlotType 1 = TIMESLOTS PREDETERMINADOS & SlotType 2 = TIMESLOTS MODIFICADOS). Si nos fijamos en "Mar 30/04/13" en la imagen de arriba, veremos que modificamos los horarios para ese día para que solo se muestre una cita de 9 am para este miembro del personal en particular.

ClosedDays:

Esta es una lista de días cerrados, por ejemplo, un miembro del personal no trabaja en su cumpleaños y día de Navidad.

Equipo:

Esta es una lista de citas que se han reservado (o esperando la confirmación de la reserva).

Consulta SQL para obtener citas disponibles:

Para verificar las citas, usamos el siguiente SQL en nuestro procedimiento almacenado. Comprueba las citas de un miembro del personal para la fecha especificada. El último procedimiento almacenado que estamos usando recorre cada miembro del personal en la página para cada día de la semana para obtener todas las citas. Usando esta consulta para obtener 10 citas de miembros del personal para los próximos 7 días = un total de 70 consultas y toma alrededor de 300ms con un millón de registros en cada tabla. Estamos cargando las citas a través de ajax, así que 300ms son aceptables para nuestro uso y probablemente lo cambiemos para obtener las citas de cada miembro del personal por separado a través de ajax (por lo tanto 7 consultas a la vez) para mejorar el rendimiento aún más en el futuro.

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

Espero que esto tenga sentido y si alguien más tiene alguna idea sobre cómo optimizar esto más, ¡por favor hágamelo saber!