database design - Eventos recurrentes/repetitivos del calendario: el mejor método de almacenamiento
database-design (11)
Estoy creando un sistema de eventos personalizado, y si tiene un evento que se repite que se parece a esto:
El evento A se repite cada 4 días a partir del 3 de marzo de 2011
o
El evento B se repite cada 2 semanas el martes a partir del 1 de marzo de 2011
¿Cómo puedo almacenar eso en una base de datos de manera que sea fácil de buscar? No quiero problemas de rendimiento si hay una gran cantidad de eventos, y tengo que pasar por todos y cada uno al renderizar el calendario.
Mejora: reemplazar la marca de tiempo con la fecha
Como una pequeña mejora de la respuesta aceptada que posteriormente fue refinada por ahoffner, es posible utilizar un formato de fecha en lugar de una marca de hora. Las ventajas son:
- fechas legibles en la base de datos
- No hay problema con los años> 2038 y la marca de tiempo
- las eliminaciones deben tener cuidado con las marcas de tiempo que se basan en fechas ajustadas estacionalmente, es decir, en el Reino Unido, el 28 de junio comienza una hora antes que el 28 de diciembre, por lo que derivar una marca de tiempo de una fecha puede romper el algoritmo de recursión.
para hacer esto, cambie el DB repeat_start para que se almacene como tipo ''fecha'' y repeat_interval ahora tiene días en lugar de segundos. es decir, 7 para una repetición de 7 días.
cambie la línea sql: WHERE ((1370563200 - repeat_start)% repeat_interval = 0)
a: DONDE (DATEDIFF (''2013-6-7'', event_start)% repeat_interval = 0)
Todo lo demás se mantiene igual. ¡Simples!
Almacenamiento de patrones de repetición "simples"
Para mi calendario basado en PHP / MySQL, quería almacenar información de eventos repetidos / recurrentes tan eficientemente como sea posible. No quería tener un gran número de filas, y quería buscar fácilmente todos los eventos que tendrían lugar en una fecha específica.
El método a continuación es excelente para almacenar información repetitiva que ocurre a intervalos regulares, como cada día, cada n días, cada semana, cada mes cada año, etc. Esto incluye todos los patrones de tipos de martes y jueves, ya que se almacenan por separado, como cada semana a partir de un martes y cada semana a partir de un jueves.
Suponiendo que tengo dos tablas, una llamada events como este:
ID NAME
1 Sample Event
2 Another Event
Y una tabla llamada events_meta como esta:
ID event_id meta_key meta_value
1 1 repeat_start 1299132000
2 1 repeat_interval_1 432000
Con repeat_start es una fecha sin hora como marca de tiempo de Unix, y repeat_interval una cantidad en segundos entre intervalos (432000 es 5 días).
repeat_interval_1 va con repeat_start del ID 1. Entonces, si tengo un evento que se repite todos los martes y todos los jueves, el repeat_interval sería 604800 (7 días), y habría 2 repeat_starts y 2 repeat_intervals. La mesa se vería así:
ID event_id meta_key meta_value
1 1 repeat_start 1298959200 -- This is for the Tuesday repeat
2 1 repeat_interval_1 604800
3 1 repeat_start 1299132000 -- This is for the Thursday repeat
4 1 repeat_interval_3 604800
5 2 repeat_start 1299132000
6 2 repeat_interval_5 1 -- Using 1 as a value gives us an event that only happens once
Luego, si tiene un calendario que recorre todos los días, al capturar los eventos del día en que está, la consulta se vería así:
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( ''repeat_interval_'', EM1.`id` )
WHERE EM1.meta_key = ''repeat_start''
AND (
( CASE ( 1299132000 - EM1.`meta_value` )
WHEN 0
THEN 1
ELSE ( 1299132000 - EM1.`meta_value` )
END
) / EM2.`meta_value`
) = 1
LIMIT 0 , 30
Reemplazando {current_timestamp} con la marca de tiempo de Unix para la fecha actual (menos el tiempo, por lo que los valores de hora, minuto y segundo se establecerán en 0).
¡Espero que esto ayude a alguien más también!
Almacenamiento de "complejos" repitiendo patrones
Este método es más adecuado para almacenar patrones complejos como
Event A repeats every month on the 3rd of the month starting on March 3, 2011
o
Event A repeats Friday of the 2nd week of the month starting on March 11, 2011
Recomiendo combinar esto con el sistema anterior para la mayor flexibilidad. Las tablas para esto deberían ser como:
ID NAME
1 Sample Event
2 Another Event
Y una tabla llamada events_meta como esta:
ID event_id meta_key meta_value
1 1 repeat_start 1299132000 -- March 3rd, 2011
2 1 repeat_year_1 *
3 1 repeat_month_1 *
4 1 repeat_week_im_1 2
5 1 repeat_weekday_1 6
repeat_week_im representa la semana del mes actual, que podría estar entre 1 y 5 potencialmente. repeat_weekday el día de la semana en el día de la semana, 1-7.
Ahora, asumiendo que está recorriendo los días / semanas para crear una vista de mes en su calendario, podría redactar una consulta como esta:
SELECT EV . *
FROM `events` AS EV
JOIN `events_meta` EM1 ON EM1.event_id = EV.id
AND EM1.meta_key = ''repeat_start''
LEFT JOIN `events_meta` EM2 ON EM2.meta_key = CONCAT( ''repeat_year_'', EM1.id )
LEFT JOIN `events_meta` EM3 ON EM3.meta_key = CONCAT( ''repeat_month_'', EM1.id )
LEFT JOIN `events_meta` EM4 ON EM4.meta_key = CONCAT( ''repeat_week_im_'', EM1.id )
LEFT JOIN `events_meta` EM5 ON EM5.meta_key = CONCAT( ''repeat_weekday_'', EM1.id )
WHERE (
EM2.meta_value =2011
OR EM2.meta_value = ''*''
)
AND (
EM3.meta_value =4
OR EM3.meta_value = ''*''
)
AND (
EM4.meta_value =2
OR EM4.meta_value = ''*''
)
AND (
EM5.meta_value =6
OR EM5.meta_value = ''*''
)
AND EM1.meta_value >= {current_timestamp}
LIMIT 0 , 30
Esto combinado con el método anterior podría combinarse para cubrir la mayoría de los patrones de eventos repetitivos / recurrentes. Si me he perdido algo por favor deje un comentario.
¿Por qué no usar un mecanismo similar a los trabajos cron de Apache? http://en.wikipedia.org/wiki/Cron
Para el calendario / programación, usaría valores ligeramente diferentes para "bits" para acomodar eventos de repetición de calendario estándar - en lugar de [día de la semana (0 - 7), mes (1 - 12), día del mes (1 - 31), hora (0 - 23), min (0 - 59)]
- Usaría algo como [Año (repetir cada N años), mes (1 - 12), día del mes (1 - 31), semana del mes (1-5), día de la semana (0 - 7) ]
Espero que esto ayude.
@Rogue Coder
¡Esto es genial!
Simplemente puede usar la operación de módulo (MOD o% en mysql) para simplificar su código al final:
En lugar de:
AND (
( CASE ( 1299132000 - EM1.`meta_value` )
WHEN 0
THEN 1
ELSE ( 1299132000 - EM1.`meta_value` )
END
) / EM2.`meta_value`
) = 1
Hacer:
$current_timestamp = 1299132000 ;
AND ( (''$current_timestamp'' - EM1.`meta_value` ) MOD EM2.`meta_value`) = 1")
Para llevar esto más lejos, uno podría incluir eventos que no se repitan para siempre.
Se podría agregar algo como "repeat_interval_1_end" para indicar la fecha de la última "repeat_interval_1". Sin embargo, esto hace que la consulta sea más complicada y realmente no puedo entender cómo hacer esto ...
¡Tal vez alguien pueda ayudar!
Desarrollé un lenguaje de programación esotérico solo para este caso. Lo mejor de todo es que es un esquema menos y una plataforma independiente. Solo tiene que escribir un programa selector, para su programación, cuya sintaxis está limitada por el conjunto de reglas que se describen aquí:
https://github.com/tusharmath/sheql/wiki/Rules
Las reglas son ampliables y puede agregar cualquier tipo de personalización según el tipo de lógica de repetición que desee realizar, sin preocuparse por las migraciones de esquemas, etc.
Este es un enfoque completamente diferente y puede tener algunas desventajas propias.
Los dos ejemplos que has dado son muy simples; se pueden representar como un intervalo simple (el primero es cuatro días, el segundo es 14 días). Cómo modelar esto dependerá completamente de la complejidad de sus recurrencias. Si lo que tiene arriba es realmente tan simple, entonces almacene una fecha de inicio y el número de días en el intervalo de repetición.
Si, sin embargo, necesitas apoyar cosas como
El evento A se repite todos los meses el 3 de cada mes a partir del 3 de marzo de 2011
O
El evento A se repite el segundo viernes del mes a partir del 11 de marzo de 2011
Entonces ese es un patrón mucho más complejo.
Para todos aquellos que estén interesados en esto, ahora solo pueden copiar y pegar para comenzar en cuestión de minutos. Tomé el consejo en los comentarios lo mejor que pude. Déjame saber si me falta algo.
"VERSIÓN COMPLEJA":
eventos
+----------+----------------+ | ID | NAME | +----------+----------------+ | 1 | Sample event 1 | | 2 | Second event | | 3 | Third event | +----------+----------------+
eventos_meta
+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+ | ID | event_id | repeat_start | repeat_interval | repeat_year | repeat_month | repeat_day | repeat_week | repeat_weekday | +----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+ | 1 | 1 | 2014-07-04 | 7 | NULL | NULL | NULL | NULL | NULL | | 2 | 2 | 2014-06-26 | NULL | 2014 | * | * | 2 | 5 | | 3 | 3 | 2014-07-04 | NULL | * | * | * | * | 5 | +----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+
Código SQL:
CREATE TABLE IF NOT EXISTS `events` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `events`
--
INSERT INTO `events` (`ID`, `NAME`) VALUES
(1, ''Sample event''),
(2, ''Another event''),
(3, ''Third event...'');
CREATE TABLE IF NOT EXISTS `events_meta` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`event_id` int(11) NOT NULL,
`repeat_start` date NOT NULL,
`repeat_interval` varchar(255) NOT NULL,
`repeat_year` varchar(255) NOT NULL,
`repeat_month` varchar(255) NOT NULL,
`repeat_day` varchar(255) NOT NULL,
`repeat_week` varchar(255) NOT NULL,
`repeat_weekday` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `events_meta`
--
INSERT INTO `events_meta` (`ID`, `event_id`, `repeat_start`, `repeat_interval`, `repeat_year`, `repeat_month`, `repeat_day`, `repeat_week`, `repeat_weekday`) VALUES
(1, 1, ''2014-07-04'', ''7'', ''NULL'', ''NULL'', ''NULL'', ''NULL'', ''NULL''),
(2, 2, ''2014-06-26'', ''NULL'', ''2014'', ''*'', ''*'', ''2'', ''5''),
(3, 3, ''2014-07-04'', ''NULL'', ''*'', ''*'', ''*'', ''*'', ''1'');
También disponible como exportación a MySQL (para fácil acceso)
Código de ejemplo PHP index.php:
<?php
require ''connect.php'';
$now = strtotime("yesterday");
$pushToFirst = -11;
for($i = $pushToFirst; $i < $pushToFirst+30; $i++)
{
$now = strtotime("+".$i." day");
$year = date("Y", $now);
$month = date("m", $now);
$day = date("d", $now);
$nowString = $year . "-" . $month . "-" . $day;
$week = (int) ((date(''d'', $now) - 1) / 7) + 1;
$weekday = date("N", $now);
echo $nowString . "<br />";
echo $week . " " . $weekday . "<br />";
$sql = "SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE ( DATEDIFF( ''$nowString'', repeat_start ) % repeat_interval = 0 )
OR (
(repeat_year = $year OR repeat_year = ''*'' )
AND
(repeat_month = $month OR repeat_month = ''*'' )
AND
(repeat_day = $day OR repeat_day = ''*'' )
AND
(repeat_week = $week OR repeat_week = ''*'' )
AND
(repeat_weekday = $weekday OR repeat_weekday = ''*'' )
AND repeat_start <= DATE(''$nowString'')
)";
foreach ($dbConnect->query($sql) as $row) {
print $row[''ID''] . "/t";
print $row[''NAME''] . "<br />";
}
echo "<br /><br /><br />";
}
?>
Código de ejemplo PHP connect.php:
<?
// ----------------------------------------------------------------------------------------------------
// Connecting to database
// ----------------------------------------------------------------------------------------------------
// Database variables
$username = "";
$password = "";
$hostname = "";
$database = "";
// Try to connect to database and set charset to UTF8
try {
$dbConnect = new PDO("mysql:host=$hostname;dbname=$database;charset=utf8", $username, $password);
$dbConnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo ''ERROR: '' . $e->getMessage();
}
// ----------------------------------------------------------------------------------------------------
// / Connecting to database
// ----------------------------------------------------------------------------------------------------
?>
También el código php está disponible aquí (para una mejor legibilidad):
index.php
y
connect.php
Ahora configurar esto debería tomarte unos minutos. No horas. :)
Seguiría esta guía: https://github.com/bmoeskau/Extensible/blob/master/recurrence-overview.md
También asegúrese de usar el formato iCal para no reinventar la rueda y recuerde la Regla # 0: ¡NO almacene instancias de eventos recurrentes individuales como filas en su base de datos!
Si bien la respuesta actualmente aceptada fue de gran ayuda para mí, quería compartir algunas modificaciones útiles que simplifican las consultas y también aumentan el rendimiento.
"Simple" Repetir Eventos
Para manejar eventos que se repiten a intervalos regulares, tales como:
Repeat every other day
o
Repeat every week on Tuesday
Debes crear dos tablas, una llamada events como este:
ID NAME
1 Sample Event
2 Another Event
Y una tabla llamada events_meta como esta:
ID event_id repeat_start repeat_interval
1 1 1369008000 604800 -- Repeats every Monday after May 20th 2013
1 1 1369008000 604800 -- Also repeats every Friday after May 20th 2013
Con repeat_start es una fecha de marca de tiempo de Unix sin tiempo (1369008000 corresponde al 20 de mayo de 2013), y repeat_interval una cantidad en segundos entre intervalos (604800 es 7 días).
Al recorrer cada día en el calendario, puede obtener eventos de repetición con esta simple consulta:
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE (( 1299736800 - repeat_start) % repeat_interval = 0 )
Simplemente sustituya en la marca de tiempo de Unix (1299736800) para cada fecha en su calendario.
Tenga en cuenta el uso del módulo (signo%). Este símbolo es como una división normal, pero devuelve el '''' resto '''' en lugar del cociente, y como tal es 0 siempre que la fecha actual sea un múltiplo exacto de la repetición de intervalo desde la repetición de inicio.
Comparación de rendimiento
Esto es significativamente más rápido que la respuesta basada en "meta_keys" sugerida anteriormente, que fue la siguiente:
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( ''repeat_interval_'', EM1.`id` )
WHERE EM1.meta_key = ''repeat_start''
AND (
( CASE ( 1299132000 - EM1.`meta_value` )
WHEN 0
THEN 1
ELSE ( 1299132000 - EM1.`meta_value` )
END
) / EM2.`meta_value`
) = 1
Si ejecuta EXPLAIN esta consulta, notará que requirió el uso de un búfer de unión:
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
| 1 | SIMPLE | EM1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 1 | SIMPLE | EV | eq_ref | PRIMARY | PRIMARY | 4 | bcs.EM1.event_id | 1 | |
| 1 | SIMPLE | EM2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
La solución con 1 unión anterior no requiere dicho búfer.
Patrones "complejos"
Puede agregar compatibilidad con tipos más complejos para admitir estos tipos de reglas de repetición:
Event A repeats every month on the 3rd of the month starting on March 3, 2011
o
Event A repeats second Friday of the month starting on March 11, 2011
Su tabla de eventos puede verse exactamente igual:
ID NAME
1 Sample Event
2 Another Event
Luego, para agregar soporte para estas reglas complejas, agregue columnas a events_meta así:
ID event_id repeat_start repeat_interval repeat_year repeat_month repeat_day repeat_week repeat_weekday
1 1 1369008000 604800 NULL NULL NULL NULL NULL -- Repeats every Monday after May 20, 2013
1 1 1368144000 604800 NULL NULL NULL NULL NULL -- Repeats every Friday after May 10, 2013
2 2 1369008000 NULL 2013 * * 2 5 -- Repeats on Friday of the 2nd week in every month
Tenga en cuenta que simplemente debe especificar un valor de repeat_interval o un conjunto de datos repeat_year , repeat_month , repeat_day , repeat_week y repeat_weekday .
Esto hace que la selección de ambos tipos a la vez sea muy simple. Simplemente recorra cada día y complete los valores correctos, (1370563200 para el 7 de junio de 2013, y luego el año, mes, día, número de semana y día de la semana, de la siguiente manera):
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE (( 1370563200 - repeat_start) % repeat_interval = 0 )
OR (
(repeat_year = 2013 OR repeat_year = ''*'' )
AND
(repeat_month = 6 OR repeat_month = ''*'' )
AND
(repeat_day = 7 OR repeat_day = ''*'' )
AND
(repeat_week = 2 OR repeat_week = ''*'' )
AND
(repeat_weekday = 5 OR repeat_weekday = ''*'' )
AND repeat_start <= 1370563200
)
Esto devuelve todos los eventos que se repiten el viernes de la 2ª semana, así como cualquier evento que se repita todos los viernes, por lo que devuelve tanto el ID de evento 1 como el 2:
ID NAME
1 Sample Event
2 Another Event
* Nota de referencia en el SQL anterior. Usé los índices predeterminados del día de la semana de PHP Date , así que "5" para el viernes
Espero que esto ayude a otros tanto como la respuesta original me ayudó!
Si bien las soluciones propuestas funcionan, estaba tratando de implementarlas con Full Calendar y requeriría más de 90 llamadas de base de datos para cada vista (ya que carga el mes actual, el anterior y el próximo), por lo que no estaba muy entusiasmado.
Encontré una biblioteca de recursión https://github.com/tplaner/When donde simplemente almacenas las reglas en la base de datos y una consulta para extraer todas las reglas relevantes.
Espero que esto ayude a alguien más, ya que pasé tantas horas tratando de encontrar una buena solución.
Edición: Esta biblioteca es para PHP
Suena muy parecido a los eventos MySQL que se almacenan en las tablas del sistema. Puede mirar la estructura y averiguar qué columnas no son necesarias:
EVENT_CATALOG: NULL
EVENT_SCHEMA: myschema
EVENT_NAME: e_store_ts
DEFINER: jon@ghidora
EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 5
INTERVAL_FIELD: SECOND
SQL_MODE: NULL
STARTS: 0000-00-00 00:00:00
ENDS: 0000-00-00 00:00:00
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2006-02-09 22:36:06
LAST_ALTERED: 2006-02-09 22:36:06
LAST_EXECUTED: NULL
EVENT_COMMENT: