Tutorial de diseño de base de datos para Google Calendar

• Usando como ejemplo la funcionalidad de eventos de un clon de Google Calendar, muestra un flujo de diseño en el que no se empieza creando tablas, sino completando primero un modelo lógico y luego trasladándolo a un esquema SQL
• El método central consiste en identificar primero anclas (anchors) como User, DayEvent, TimeEvent y Timezone, y luego validar los requisitos y la cardinalidad mediante atributos y enlaces 1:N·M:N
• Los eventos se dividen en eventos de día completo y eventos basados en hora; los eventos basados en hora almacenan la hora local de inicio y fin, junto con su Timezone correspondiente, para contemplar incluso posibles cambios de zona horaria
• Los eventos recurrentes se representan con frecuencia, intervalo, modo de recurrencia mensual, condición de finalización y enlaces a días de la semana; para el renderizado en pantalla y los cambios de instancias individuales, se usan DaySlot y TimeSlot como anclas separadas
• El diseño físico final parte de “una tabla por ancla”, pero no convierte DayOfTheWeek en tabla; queda organizado en un total de 8 tablas SQL, incluidas 6 tablas de anclas y 2 tablas de enlaces M:N

---

Enfoque de diseño: construir primero el modelo lógico

• El objetivo es mostrar un proceso de diseño de tablas de base de datos cercano a un proyecto real, tomando como caso la funcionalidad de calendario de un clon de Google Calendar
• El flujo completo consiste en crear primero un modelo lógico completo que describa los datos de calendario que deben almacenarse y luego trasladar ese modelo a una estructura física de tablas
• La primera parte trata un modelo lógico no atado a una base de datos específica
  • Modela los requisitos de negocio de forma independiente de MySQL, Postgres, servidores relacionales tradicionales, NoSQL o bases de datos en la nube
• La parte final muestra una forma de convertir el modelo lógico en tablas físicas
  • En sistemas cuyos requisitos no son extremadamente exigentes, puede considerarse un enfoque de diseño válido

Alcance del problema: funcionalidad central de eventos de Google Calendar

• El sistema objetivo es un sistema multiusuario, y los datos relacionados con usuarios se modelan solo de forma mínima
• Los eventos pueden tener atributos como título, descripción y ubicación, pero la parte más compleja es el tiempo y la fecha
• Los eventos a modelar incluyen las siguientes diferencias
  • Eventos de día completo y eventos basados en hora
  • Eventos recurrentes y no recurrentes
  • Eventos de día completo que abarcan varios días
  • Eventos basados en hora con zona horaria asociada, cuyos horarios de inicio y fin pueden estar en fechas distintas o en zonas horarias distintas
• Los eventos recurrentes incluyen las siguientes formas
  • Repetición diaria o cada N días
  • Repetición semanal en días específicos de la semana, con intervalos de 2 semanas o más también posibles
  • Repetición mensual en una fecha específica o en el día de la semana de una semana específica
  • Repetición anual
  • Repetición para siempre, hasta una fecha específica o por una cantidad específica de veces
• Una instancia específica de un evento recurrente puede moverse a otra fecha u hora, o eliminarse
• Debe ser posible cambiar la serie recurrente en sí incluso después de que algunos eventos ya hayan pasado

Expresar requisitos con anclas, atributos y enlaces

• El modelado empieza primero por encontrar anclas (anchor)
  • Un ancla es una entidad que puede contarse y agregarse a la base de datos, como User o Event
  • Si se puede decir de forma natural “There are 200 Users in our database” o “When this button is clicked, a new DayEvent is created”, puede considerarse un ancla
• Los atributos almacenan información real sobre un ancla
  • User tiene un atributo de correo electrónico
  • DayEvent tiene nombre, fecha de inicio y fecha de fin
  • Incluso los eventos de una sola fecha almacenan la fecha de inicio y la fecha de fin con el mismo valor, para tratarlos con la misma estructura que los eventos de varios días
• Cuando dos anclas se relacionan, se usa un enlace en lugar de un atributo
  • “Qué User creó un DayEvent” no es un atributo lógico que inserta directamente un ID de User dentro de los atributos de DayEvent, sino un enlace entre User y DayEvent
  • Los enlaces usan cardinalidades 1:N, M:N y 1:1, y su significado bidireccional se valida con dos oraciones
• Entre User y DayEvent hay un enlace 1:N
  • Un User puede crear varios DayEvent
  • Un DayEvent es creado por un solo User

Eventos basados en hora y manejo de Timezone

• Para los eventos basados en hora se agregan las anclas Timezone y TimeEvent
• El modelo de Timezone se minimiza para fines del tutorial y solo tiene como atributo un nombre legible por humanos
  • Un valor de ejemplo es Europe/Kyiv
  • Las definiciones reales de zonas horarias requieren offset UTC, existencia de horario de verano, inicio y fin del DST, offset UTC cuando aplica DST, definiciones históricas y estado activo o retirado, entre otros, pero eso queda como una tarea aparte
• TimeEvent tiene nombre, fecha y hora local de inicio, y fecha y hora local de fin
  • Valores de ejemplo: 2024-01-14 12:30, 2024-01-14 13:15
• El tiempo no se almacena solo en UTC, sino como hora local ingresada por el usuario
  • Por ejemplo, una partida de billar en horario de Cologne el 6 de septiembre de 2058 de 09:30 a 11:00 debe almacenarse tal como la ingresó el usuario, porque hoy no se puede conocer el offset UTC de ese momento
  • Si más adelante cambian las leyes locales, debe ser posible ajustarlo en consecuencia
• TimeEvent tiene enlaces 1:N con Timezone para la zona horaria de inicio y la zona horaria de fin, respectivamente
  • Como en el ejemplo de un boleto de avión, un vuelo de Amsterdam a London puede tener la hora de salida y la de llegada en zonas horarias distintas
  • En la mayoría de los eventos basados en hora, las zonas horarias de inicio y fin son iguales, pero para cubrir el caso general siempre se especifican ambas

Modelado de eventos recurrentes de día completo

• Los eventos recurrentes de día completo se representan agregando atributos y enlaces relacionados con recurrencia a DayEvent
• La frecuencia de repetición se modela como un atributo de tipo either/or/or
  • Los valores posibles son daily, weekly, monthly, annually
  • Si no se define un valor, se considera un evento no recurrente
• El intervalo de repetición es un atributo entero que solo tiene sentido “cuando se trata de un evento recurrente”
  • Un valor de ejemplo es 2
  • Se usa con el significado de cada 2 días, cada 2 semanas, cada 2 meses, etc.
• La repetición mensual tiene un atributo de selección aparte
  • same_day, para repetir en la misma fecha
  • same_weekday, para repetir en el día de la semana de la misma semana que la fecha original
• Los días específicos de la semana de una repetición semanal no se manejan como un atributo de arreglo de strings, sino que se modelan con el ancla DayOfTheWeek y un enlace M:N
  • Ejemplos de ID de DayOfTheWeek: Mon, Tue, Wed, Thu, Fri, Sat, Sun
  • Un DayEvent puede ocurrir en varios DayOfTheWeek, y un DayOfTheWeek puede incluir varios DayEvent
• La condición de finalización de la recurrencia también se modela como atributo
  • forever
  • until_date
  • N_repetitions
  • Si es until_date, se agrega un atributo de fecha de finalización
  • Si es N_repetitions, se agrega un atributo de cantidad de repeticiones

Slots para renderizado y cambios de instancias individuales

• Si se guarda un evento recurrente solo como un evento original, la consulta para encontrar los eventos que deben mostrarse en una vista semanal específica puede volverse compleja
• Para el renderizado en pantalla y la modificación individual de eventos recurrentes se introduce el concepto de Slot
  • DaySlot representa una instancia de evento de día completo en una fecha específica
  • TimeSlot representa una instancia de evento basado en hora en una fecha y hora específicas
• DaySlot tiene fecha y estado de omisión
  • La fecha de un slot específico puede cambiarse
  • Si se cancela una reunión de una semana concreta, ese DaySlot puede marcarse como skipped
• DayEvent y DaySlot tienen un enlace 1:N
  • Un DayEvent puede generar varios DaySlot
  • Un DaySlot corresponde a un solo DayEvent
  • Incluso para un DayEvent no recurrente siempre se crea un DaySlot, para simplificar el código de renderizado
• En eventos de recurrencia infinita existe el problema del rango de creación de slots
  • Para eventos como cumpleaños que se repiten anualmente para siempre, se pueden crear slots hasta un límite arbitrario como 100 años
  • O también pueden crearse bajo demanda cuando el usuario solicite una página de calendario en un futuro lejano
• Hay que tener cuidado con el cálculo de fechas
  • Cumpleaños del 29 de febrero
  • Eventos mensuales que ocurren el día 31 de cada mes
  • En estos casos hay que decidir si se prohíben, si se mueven un día antes o después, etc.

TimeSlot y zonas horarias

• A los eventos recurrentes basados en hora también se les aplica el mismo enfoque que a los eventos de día completo, usando el ancla TimeSlot
• TimeSlot corresponde a una instancia de fecha y hora específica de un evento específico, y un TimeEvent recurrente se relaciona con varios TimeSlot
• Los atributos de TimeSlot son fecha y hora local de inicio, fecha y hora local de fin, y estado de omisión
• Como un TimeSlot específico también puede moverse a otro día, la información de inicio y fin se mantiene por separado
• TimeSlot también tiene enlaces 1:N con Timezone para la zona horaria de inicio y la zona horaria de fin, respectivamente
• Entre TimeEvent y TimeSlot hay un enlace 1:N, y también se crea un TimeSlot para los TimeEvent no recurrentes

Modelo lógico completo

• Las anclas definidas hasta ahora son 7 en total
  • User
  • Timezone
  • DayEvent
  • TimeEvent
  • DayOfTheWeek
  • DaySlot
  • TimeSlot
• Los atributos consisten en el correo electrónico de User, el nombre de Timezone, el nombre, fecha de inicio, fecha de fin e información de recurrencia de DayEvent, el nombre y horas locales de inicio y fin de TimeEvent, y el momento de ocurrencia y estado skipped de DaySlot y TimeSlot
• Los enlaces se organizan en un total de 10
  • User con DayEvent, y User con TimeEvent
  • Timezone con las zonas horarias de inicio y fin de TimeEvent
  • DayEvent con DayOfTheWeek, y TimeEvent con DayOfTheWeek
  • DayEvent con DaySlot, y TimeEvent con TimeSlot
  • Timezone con las zonas horarias de inicio y fin de TimeSlot

Trasladarlo a tablas SQL

• Para fines educativos, el diseño físico usa la estrategia de una tabla por ancla
• El modelo lógico tiene 7 anclas, 21 atributos y 10 enlaces
• En general, al sumar el número de anclas y el número de enlaces M:N se obtendrían 9 tablas, pero en el diseño final DayOfTheWeek se trata de forma especial y no se crea una tabla física para él
• Las tablas SQL finales son 8 en total
  • users
  • timezones
  • day_events
  • time_events
  • day_slots
  • time_slots
  • day_event_dows
  • time_event_dows
• Los enlaces 1:N se convierten en columnas con carácter de clave foránea en la tabla del lado N
  • day_events.user_id
  • time_events.user_id
  • time_events.start_timezone_id
  • time_events.end_timezone_id
  • day_slots.day_event_id
  • time_slots.time_event_id
  • time_slots.start_timezone_id
  • time_slots.end_timezone_id
• Los enlaces M:N se convierten en tablas separadas
  • day_event_dows conecta DayEvent con días de la semana
  • time_event_dows conecta TimeEvent con días de la semana
• Los tipos físicos se eligen en correspondencia con los tipos lógicos
  • Las cadenas son VARCHAR
  • Las fechas son DATE
  • La fecha y hora local es DATETIME
  • yes/no es TINYINT UNSIGNED
  • Los atributos entrelazados que solo tienen sentido de forma condicional usan tipos que permiten NULL
• NULL y los valores sentinel se tratan como conceptos del esquema físico, no del modelo lógico

Índices y atributos omitidos

• Al esquema SQL de ejemplo le faltan índices que un desarrollador de bases de datos experimentado consideraría necesarios
  • Por ejemplo, day_events.user_id podría necesitar un índice
• Qué columnas o combinaciones de columnas deben tener índices depende de cómo la aplicación consulta las tablas
• Para el tema de índices se puede consultar Use The Index, Luke
• Durante la explicación del esquema lógico se omiten algunos atributos porque siguen patrones similares
  • Nombre de usuario
  • Hash de contraseña del usuario
  • Ubicación del evento
  • Lista de personas invitadas
• Los elementos de datos omitidos pueden agregarse a la tabla del modelo lógico del mismo modo y luego reflejarse en el esquema SQL

Procedimiento completo

• Partir de un texto de formato libre que describa el problema de negocio
• Elaborar una lista de anclas
• Elaborar una lista de atributos, escribiendo claramente el significado de cada atributo en forma de pregunta
• Elaborar una lista de enlaces y confirmar con oraciones que la cardinalidad sea correcta
• Si hace falta, crear un esquema gráfico basado en el modelo lógico
• Crear el modelo físico completando nombres de tablas, nombres de columnas y tipos de datos físicos
• Usar la información de los pasos anteriores para escribir el esquema SQL con sentencias CREATE TABLE
• Enviar el esquema al servidor de base de datos, corregir errores tipográficos y volver a enviarlo
• Compartir el modelo lógico con el equipo

[GN⁺]

Opiniones de Hacker News

• Si serializo un evento cualquiera de mi calendario como iCalendar, pesa unos 740 bytes; incluso un calendario extremadamente ocupado, con eventos cada 15 minutos entre las 9 a. m. y las 5 p. m., tendría 11,680 entradas en todo un año, apenas unos 8 MB
  Por eso, más que diseñar un esquema, parece un problema en el que podría ser mucho mejor implementar el calendario simplemente con un escaneo secuencial. Si optimizas un parser de iCalendar para recorrer un rango de eventos volcados con un rendimiento del orden de GB/s, incluso el peor caso anterior se podría escanear en milisegundos de un solo dígito
  Optimizar un parser es un problema mucho más simple que se resuelve una vez, en comparación con cambiar o extender un mal modelo de datos después de que se haya propagado a muchos usuarios; y, de todos modos, lo primero que probablemente tendría que hacer un nuevo modelo de datos es importar/exportar iCalendar, así que parece un compromiso bastante bueno

  • ¿Entonces no habría que volver a crear una búsqueda por rangos como BETWEEN ... AND ... de SQL? Lo mismo aplica para encontrar eventos de los usuarios 1, 2 y 3
    En una aplicación real, probablemente ya habría una base de datos relacional con cosas como cuentas de usuario, y de pronto habría que escanear eventos en un directorio y luego vincular esos resultados con registros de la base de datos
    Puede ser correcto para cierta aplicación, pero hay tareas específicas que las bases de datos hacen realmente bien, y por eso son una buena opción. Con los índices adecuados, también podrían lograr un rendimiento similar o mejor. A menos que crees una estructura de directorios inteligente para los eventos, lo cual en la práctica es como un índice de una sola dimensión; en una base de datos puedes crear índices sobre varias dimensiones y combinaciones de dimensiones
    En definitiva, es cierto: es cuestión de compromisos
  • iCalendar fue diseñado como formato de intercambio, no como formato de almacenamiento para acceso rápido a datos de calendario
    Su edad se nota en el formato: se ve que fue diseñado antes de que XML/JSON se pusieran “de moda”
    Referencia: https://en.wikipedia.org/wiki/ICalendar
  • Aunque se pueda hacer sin un DBMS, los datos siguen necesitando un buen esquema, y ese esquema no debería ser un montón de registros iCalendar. Por ejemplo, solo tienes direcciones de correo cambiantes y repetidas en lugar de IDs de usuario estables
  • Ese enfoque también podría dar una solución más limpia al problema de los slots de tiempo infinitos que menciona el artículo

• Es un artículo sobre cómo manejar eventos recurrentes en aplicaciones y, personalmente, me abrió los ojos. Recomiendo mucho leerlo: https://github.com/bmoeskau/Extensible/blob/master/recurrenc...

  • Es interesante porque los consejos del artículo original y los de este texto se contradicen
    El original dice que siempre guardes la zona horaria en cada fecha, mientras que el artículo enlazado dice que conviertas todo a UTC. En este punto estoy de acuerdo con el original
    El original dice que crees una fila de base de datos por cada evento, mientras que el artículo enlazado dice que no lo hagas. En este punto estoy de acuerdo con el artículo enlazado

• La vez que peor me fue en una entrevista fue cuando tuve que crear un sistema de reservas simple que también manejara reservas recurrentes
  Desde mis primeros años en la universidad no me había sentido tan perdido y confundido intentando lograr algo con código. Incluso ahora, cuando tengo que trabajar con fechas/horas, lo primero que siento es miedo; no sé por qué, pero en mi cabeza no encaja bien

  • La pregunta de diseño “diseña un sistema de reservas, pero incluye reservas recurrentes” se parece a “escribe una función que ordene una lista de strings, pero debe respetar las convenciones de orden alfabético para strings UTF-8 arbitrarios y para todos los locales”
    Parece simple a primera vista, pero si intentas que funcione de forma general en el mundo real, se vuelve imposiblemente complejo
  • No tienes por qué castigarte tanto. Me encargaron mejorar el calendario de un CMS para soportar muchas más funcionalidades y casi me devoró. Para entonces ya tenía unos 8 años de experiencia
    Es un problema engañosamente difícil y requiere muy buenas habilidades de modelado de datos

• La capacidad de modelar sistemas está subestimada. En un dominio nuevo, hay que empezar con un análisis completo del problema para capturar tanto la estructura estática del sistema —es decir, el modelo de clases— como su comportamiento dinámico, es decir, los casos de uso
  Si saltas directamente a un modelo estático de base de datos, es fácil perder de vista el comportamiento dinámico. Eso puede estar bien en apps CRUD simples, pero en sistemas más complejos puede ser un gran error

  • Como científico de datos, varias veces me ha costado bastante responder en el momento preguntas de entrevista que parecían simples, del tipo “¿cómo harías el esquema?”
    La última que recuerdo fue cómo haría una tabla de productos con cupones. Al principio pensé que bastarían dos tablas, pero ¿no habría que mantener también el historial? Entonces habría que poner y actualizar fecha/hora por producto y cupón. Ahora también hay que pensar en los índices de las tablas y en si un join para obtener el precio con descuento es una buena forma de hacerlo. La mayoría de los cupones solo pueden usarse una vez por persona, ¿cómo demonios implemento eso?
    Probablemente solo querían una simple tabla de productos + cupones, pero yo terminé profundizando como loco durante un buen rato
  • ¿Qué forma tiene el resultado de este tipo de análisis? Me interesaría ver enlaces de referencia, como un documento de ejemplo o un capítulo de un libro

• Me parece un dominio bien elegido para mostrar modelado y un buen artículo introductorio
  El término “anchor” se siente un poco raro, pero la explicación es tan concreta y con los pies en la tierra, como un ancla real, que parece funcionar bastante bien
  La idea de definir atributos a partir de preguntas es sólida. Demasiadas veces saltamos directo al mínimo de nombres de columnas/atributos sin definir qué preguntas queremos responder, y así no eliminamos la ambigüedad que está en la cabeza del cliente. La definición de atributos basada en preguntas es una buena forma de ganar claridad rápidamente

  • El término “anchor” viene heredado de Anchor Modeling(https://en.wikipedia.org/wiki/Anchor_modeling). “Entity” se usa mucho, pero no quería usarlo porque puede traer cargas y suposiciones innecesarias
    Además, el término está tremendamente sobrecargado. En ciencias de la computación, prácticamente todo es un objeto o una entidad
    Que la idea de definir atributos mediante preguntas se perciba como sólida es una confirmación importante de la validez de este enfoque

• Las zonas horarias te hacen retorcer la cabeza, sobre todo cerca de los momentos de cambio
  Supongamos que una zona horaria salta una hora hacia adelante con el horario de verano y luego retrocede una hora al volver al horario estándar
  Cuando la hora salta una hora hacia adelante, un evento de 1 hora puede verse en pantalla como si abarcara 2 horas, y esa segunda hora puede ser inalcanzable o no existir
  Cuando la hora retrocede una hora, un evento de 1 hora puede verse como de 2 horas o como de 0 horas
  Las zonas horarias son una construcción humana, así que no hay que hardcodear valores. Algún día cambian

  • Si además consideras los desplazamientos al llevar el control del tiempo, se pone más interesante. La primera vez que me pasó fue llevando el control del tiempo en un barco que estuvo varios días en el mar
    Puedes cruzar repetidamente zonas horarias en ambos sentidos, también puedes cruzar la línea internacional de cambio de fecha, e incluso es posible que la fecha/hora local de inicio quede después de la fecha/hora local de término
  • ¿Eso quiere decir que deberíamos hacer calendarios sin zonas horarias y todo en UTC? Estoy de acuerdo en que las zonas horarias son algo que inventamos, pero las creamos para una mejor organización regional. Trabajo con clientes de todo el mundo y todos usan zonas horarias. Personalmente, me parece una exigencia bastante absurda
    Más bien convendría concentrarse en eliminar el concepto de horario de verano en las pocas regiones que todavía lo usan. Es lo que más confusión causa entre zonas horarias, especialmente al planificar eventos posteriores al próximo cambio

• No quiero guardar dos fechas para un evento. Es más fácil guardar la hora de inicio y la duración del evento
  Así se simplifica la lógica de actualización del evento. La hora de término siempre se puede calcular a partir de la hora de inicio y la duración

  • Para ser honesto, se pueden guardar las tres cosas: inicio, término y duración, y usar la que haga falta según el propósito. Solo necesitas un único punto de entrada/actualización que sincronice las representaciones alternativas
  • Ese enfoque puede causar problemas de rendimiento en la base de datos. Tendrías que calcular la duración sobre la marcha en cada consulta
  • ¿No pasa lo mismo al revés? Si tienes inicio y término, simplemente puedes calcular la duración

• Hace tiempo trabajé en una aplicación de calendario con eventos recurrentes. Después de mucha investigación, decidimos usar RRules para representarlos, y quedé muy satisfecho. Ese trabajo inicial lo hice cuando estaba en una agencia
  Más tarde me incorporé a esa empresa como empleado de tiempo completo, y me llevé una sorpresa al ver que un contratista de otra compañía había eliminado RRules y lo había cambiado por un enfoque en el que se creaban y borraban instancias de eventos sobre la marcha. Tenía muy poca tolerancia a fallas, así que un script que también hacía otras cosas y a veces fallaba terminaba no creando eventos nuevos. Entonces a un evento mensual recurrente le faltaba algún mes
  Me frustró muchísimo que alguien hubiera quitado algo que se había construido con tanto análisis e investigación sin dedicarle un esfuerzo comparable. Bastaban unas semanas en esa empresa para darse cuenta de que el CEO esperaba que el equipo de ingeniería siguiera sacando, a su antojo, funciones que nadie usaba, y por la incertidumbre del mercado laboral, lamentablemente me quedé 2 años
  Para colmo, más tarde busqué y vi en Glassdoor reseñas falsas obviamente escritas por el CEO, todas con el mismo estilo y sin una sola palabra negativa, lo cual fue realmente amargo. Yo y varios conocidos que trabajaron ahí lo detestábamos, aunque al menos me consuela que en esa empresa escribí algunos de mis mejores ensayos. El CTO tampoco tenía remedio

• Una vez implementé un backend de calendario y control de recursos para una plataforma low-code
  El control era muy personalizable y permitía elegir no solo varias vistas, como diaria, mensual y anual, sino también vistas de recursos. Podías reservar recursos con grupos personalizados y agruparlos por plugin, por ID de recurso, etc. En la fuente de datos definías “plugins”, y también las columnas de inicio/término, la columna de título y la columna de recurso. Los recursos podían venir de una clave foránea o de una relación 1:1, o de una relación 1:N de una fuente de datos “hija”, o de la misma fuente de datos/tabla
  También implementamos varias series de citas, como mensual, semanal, en ciertos días de la semana o diaria, y permitimos elegir qué valores de columnas copiar. También manejábamos conflictos entre citas, que podían considerarse conflicto solo cuando se reservaba el mismo recurso. Además se podían configurar buffers antes y después de una cita, durante los cuales no se podía poner otra cita
  A veces era desafiante por las zonas horarias de Europa y cosas como el horario de verano/invierno, pero fue un trabajo bastante entretenido

• Lo único que quiero en Google Calendar es un registro de cambios del propio calendario. Por favor, ojalá agreguen eso a la base de datos