database-design - Eventos recurrentes / repetitivos del calendario:el mejor método de almacenamiento




(10)

¿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.

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.


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: [email protected]
      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:

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.



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:

  1. fechas legibles en la base de datos
  2. No hay problema con los años> 2038 y la marca de tiempo
  3. 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!


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. :)


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.


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ó!






database-design calendar