Tutorial de design de banco de dados para o Google Calendar

• Usando a funcionalidade de eventos de um clone do Google Calendar como exemplo, mostra um fluxo de design em que se completa primeiro o modelo lógico, sem começar pelas tabelas, e só depois se passa para o esquema SQL
• O método central é identificar primeiro âncoras (anchors) como User, DayEvent, TimeEvent e Timezone, e validar requisitos e cardinalidade por meio de atributos e links 1:N/M:N
• Eventos se dividem em eventos de dia inteiro e eventos baseados em horário; eventos baseados em horário armazenam a hora local de início e fim e o respectivo Timezone de cada um, cobrindo até a possibilidade de mudanças de fuso horário
• Eventos recorrentes são representados por frequência, intervalo, modo de recorrência mensal, condição de término e ligação com dias da semana; para renderização da tela e alterações em instâncias individuais, DaySlot e TimeSlot são mantidos como âncoras separadas
• O design físico final usa como base “uma tabela por âncora”, mas não cria uma tabela para DayOfTheWeek; assim, fica organizado em um total de 8 tabelas SQL, incluindo 6 tabelas de âncoras e 2 tabelas de links M:N

---

Abordagem de design: criar primeiro o modelo lógico

• O objetivo é mostrar, tendo como alvo a funcionalidade de calendário de um clone do Google Calendar, um processo de design de tabelas de banco de dados próximo ao de um projeto real
• O fluxo geral consiste em criar primeiro um modelo lógico completo que descreva os dados de calendário a serem armazenados, e depois transferir esse modelo para uma estrutura física de tabelas
• A primeira parte trata de um modelo lógico que não fica preso a um banco de dados específico
  • Modela os requisitos de negócio de forma independente de MySQL, Postgres, servidores relacionais tradicionais, NoSQL e bancos de dados em nuvem
• A parte final mostra uma abordagem para converter o modelo lógico em tabelas físicas
  • Em sistemas cujos requisitos não sejam muito exigentes, isso pode ser visto como uma abordagem de design válida

Escopo do problema: funcionalidades centrais de agenda do Google Calendar

• O sistema-alvo é um sistema multiusuário, e os dados relacionados a usuários são modelados apenas no mínimo necessário
• Eventos podem ter atributos como título, descrição e localização, mas a parte mais complexa é hora e data
• Os eventos a modelar incluem as seguintes diferenças
  • Eventos de dia inteiro e eventos baseados em horário
  • Eventos recorrentes e não recorrentes
  • Eventos de dia inteiro que abrangem vários dias
  • Eventos baseados em horário que têm fuso horário associado e cujos horários de início e fim podem estar em datas diferentes ou em fusos horários diferentes
• Eventos recorrentes incluem os seguintes formatos
  • Repetir todos os dias ou a cada N dias
  • Repetir toda semana em determinados dias da semana, também permitindo intervalos de 2 semanas ou mais
  • Repetir todo mês em uma data específica ou em um dia da semana de uma determinada semana do mês
  • Repetir todo ano
  • Repetir para sempre, até uma data específica ou por um número específico de vezes
• Uma instância específica de um evento recorrente pode ser movida para outra data/hora ou excluída
• Deve ser possível alterar a própria agenda recorrente mesmo depois que alguns eventos já tiverem passado

Expressando requisitos com âncoras, atributos e links

• A modelagem começa primeiro pela identificação de âncoras (anchors)
  • Uma âncora é algo que pode ser contado e adicionado ao banco de dados, como User ou Event
  • Se for possível falar naturalmente algo como “There are 200 Users in our database” ou “When this button is clicked, a new DayEvent is created”, isso pode ser visto como uma âncora
• Atributos armazenam informações reais sobre uma âncora
  • User tem um atributo de e-mail
  • DayEvent tem nome, data de início e data de término
  • Mesmo eventos de uma única data armazenam a data de início e a data de término com o mesmo valor, para serem tratados com a mesma estrutura de eventos de vários dias
• Quando duas âncoras se relacionam, usa-se um link, não um atributo
  • “Qual User criou um DayEvent” não é um atributo lógico que coloca diretamente o ID do User dentro de DayEvent, mas sim um link entre User e DayEvent
  • Links usam cardinalidade 1:N, M:N ou 1:1, e seu significado bidirecional é validado com duas frases
• Há um link 1:N entre User e DayEvent
  • Um User pode criar vários DayEvents
  • Um DayEvent é criado por um único User

Eventos baseados em horário e tratamento de Timezone

• Para eventos baseados em horário, adicionam-se as âncoras Timezone e TimeEvent
• O modelo de Timezone é minimizado para fins do tutorial, mantendo apenas um nome legível por humanos como atributo
  • Um valor de exemplo é Europe/Kyiv
  • Definições reais de fuso horário exigem offset UTC, existência de horário de verão, início e fim do DST, offset UTC quando DST se aplica, definições históricas, status ativo/descontinuado etc., mas isso fica como uma tarefa separada
• TimeEvent tem nome, data/hora local de início e data/hora local de término
  • Valores de exemplo são 2024-01-14 12:30, 2024-01-14 13:15
• O horário não é armazenado apenas em UTC, mas sim na hora local informada pelo usuário
  • Por exemplo, uma partida de bilhar em 6 de setembro de 2058, das 09:30 às 11:00 no horário de Cologne, deve ser armazenada exatamente como o usuário informou, pois hoje não é possível saber o offset UTC desse momento
  • Se a legislação local mudar posteriormente, deve ser possível ajustar de acordo
• TimeEvent tem links 1:N com Timezone, separadamente para o fuso horário de início e o fuso horário de término
  • Como no exemplo de uma passagem aérea, um voo de Amsterdam para London pode ter os horários de partida e chegada em fusos horários diferentes
  • A maioria dos eventos baseados em horário tem o mesmo fuso no início e no fim, mas ambos são sempre especificados para cobrir o caso geral

Modelagem de eventos recorrentes de dia inteiro

• Eventos recorrentes de dia inteiro são representados adicionando atributos e links relacionados à recorrência em DayEvent
• A frequência de recorrência é mantida como um atributo de tipo either/or/or
  • Os valores possíveis são daily, weekly, monthly, annually
  • Se nenhum valor estiver definido, considera-se um evento não recorrente
• O intervalo de recorrência é um atributo inteiro que só faz sentido “quando é um evento recorrente”
  • Um valor de exemplo é 2
  • É usado com o significado de a cada 2 dias, a cada 2 semanas, a cada 2 meses etc.
• Para recorrência mensal há um atributo de escolha separado
  • same_day, que repete na mesma data
  • same_weekday, que repete no dia da semana da mesma semana do mês da data original
• Os dias da semana específicos de uma recorrência semanal não são tratados como um atributo de array de strings, mas modelados com a âncora DayOfTheWeek e um link M:N
  • Exemplos de IDs de DayOfTheWeek são Mon, Tue, Wed, Thu, Fri, Sat, Sun
  • Um DayEvent pode ocorrer em vários DayOfTheWeek, e um DayOfTheWeek pode incluir vários DayEvents
• A condição de término da recorrência também fica como atributo
  • forever
  • until_date
  • N_repetitions
  • No caso de until_date, adiciona-se um atributo de data de término
  • No caso de N_repetitions, adiciona-se um atributo de número de repetições

Slot para renderização e alteração de instâncias individuais

• Se um evento recorrente for armazenado apenas como um evento original, a consulta para encontrar eventos a exibir em uma tela semanal específica pode ficar complexa
• Para renderização da tela e alterações individuais em eventos recorrentes, introduz-se o conceito de Slot
  • DaySlot representa uma instância de evento de dia inteiro em uma data específica
  • TimeSlot representa uma instância de evento baseado em horário em uma data e hora específicas
• DaySlot tem data e indicação de pulado
  • A data de um slot específico pode ser alterada
  • Se a reunião de uma determinada semana for cancelada, o respectivo DaySlot pode ser marcado como skipped
• DayEvent e DaySlot têm um link 1:N
  • Um DayEvent pode gerar vários DaySlots
  • Um DaySlot corresponde a um único DayEvent
  • Mesmo para um DayEvent não recorrente, sempre se cria um DaySlot para simplificar o código de renderização
• Eventos de recorrência infinita têm um problema de faixa de geração de slots
  • Para um evento que se repete para sempre todo ano, como um aniversário, pode-se criar slots até um limite arbitrário, como 100 anos
  • Ou criá-los sob demanda quando o usuário solicitar uma página de calendário em um futuro distante
• Cálculos de data exigem cuidado
  • Aniversário em 29 de fevereiro
  • Evento mensal que ocorre todo dia 31
  • Nesses casos, é preciso decidir se o uso será proibido ou se o evento será movido um dia para frente ou para trás

TimeSlot e fusos horários

• Para eventos recorrentes baseados em horário, aplica-se a mesma abordagem dos eventos de dia inteiro e cria-se a âncora TimeSlot
• TimeSlot corresponde a uma instância de data/hora específica de um evento específico, e um TimeEvent recorrente corresponde a vários TimeSlots
• Os atributos de TimeSlot são data/hora local de início, data/hora local de término e indicação de pulado
• Como um TimeSlot específico também pode ser movido para outro dia, as informações de início e término são mantidas separadamente
• TimeSlot também tem links 1:N com Timezone, separadamente para o fuso horário de início e o fuso horário de término
• Há um link 1:N entre TimeEvent e TimeSlot, e também se cria TimeSlot para TimeEvents não recorrentes

Modelo lógico completo

• As âncoras definidas até aqui são 7 no total
  • User
  • Timezone
  • DayEvent
  • TimeEvent
  • DayOfTheWeek
  • DaySlot
  • TimeSlot
• Os atributos consistem em e-mail de User, nome de Timezone, nome/data de início/data de término/informações de recorrência de DayEvent, nome/horários locais de início e término de TimeEvent, e momento de ocorrência e indicação skipped de DaySlot e TimeSlot
• Os links são organizados em um total de 10
  • User e DayEvent, User e TimeEvent
  • Timezone e os fusos horários de início/fim de TimeEvent
  • DayEvent e DayOfTheWeek, TimeEvent e DayOfTheWeek
  • DayEvent e DaySlot, TimeEvent e TimeSlot
  • Timezone e os fusos horários de início/fim de TimeSlot

Transferindo para tabelas SQL

• Para fins educacionais, o design físico usa a estratégia de uma tabela por âncora
• O modelo lógico tem 7 âncoras, 21 atributos e 10 links
• Em geral, a soma do número de âncoras e do número de links M:N resultaria em 9 tabelas, mas no design final DayOfTheWeek é tratado de modo especial e não se cria uma tabela física para ele
• As tabelas SQL finais são 8 no total
  • users
  • timezones
  • day_events
  • time_events
  • day_slots
  • time_slots
  • day_event_dows
  • time_event_dows
• Links 1:N entram na tabela do lado N como colunas com características de chave estrangeira
  • 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
• Links M:N são transformados em tabelas separadas
  • day_event_dows liga DayEvent a dias da semana
  • time_event_dows liga TimeEvent a dias da semana
• Os tipos físicos são escolhidos de acordo com os tipos lógicos
  • Strings usam VARCHAR
  • Datas usam DATE
  • Data/hora local usa DATETIME
  • yes/no usa TINYINT UNSIGNED
  • Atributos entrelaçados que só fazem sentido condicionalmente usam tipos que permitem NULL
• NULL e valores sentinela são tratados como conceitos do esquema físico, não do modelo lógico

Índices e atributos omitidos

• O esquema SQL de exemplo não inclui índices que seriam necessários do ponto de vista de um desenvolvedor de banco de dados experiente
  • Por exemplo, day_events.user_id pode precisar de um índice
• Quais colunas ou combinações de colunas devem receber índices depende de como a aplicação consulta as tabelas
• Para o tema de índices, pode-se consultar Use The Index, Luke
• Durante a explicação do esquema lógico, alguns atributos são omitidos por seguirem padrões semelhantes
  • Nome de usuário
  • Hash da senha do usuário
  • Localização do evento
  • Lista de convidados
• Elementos de dados omitidos podem ser adicionados à tabela do modelo lógico da mesma forma e depois refletidos no esquema SQL

Procedimento completo

• Começar por um texto livre que descreva o problema de negócio
• Elaborar uma lista de âncoras
• Elaborar uma lista de atributos, escrevendo claramente em forma de pergunta o significado de cada atributo
• Elaborar uma lista de links e confirmar com frases se a cardinalidade está correta
• Se necessário, criar um esquema gráfico com base no modelo lógico
• Criar o modelo físico preenchendo nomes de tabelas, nomes de colunas e tipos de dados físicos
• Usar as informações das etapas anteriores para escrever o esquema SQL com instruções CREATE TABLE
• Enviar o esquema ao servidor de banco de dados, corrigir erros de digitação e enviar novamente
• Compartilhar o modelo lógico com a equipe

[GN⁺]

Comentários do Hacker News

• Se eu serializar um evento aleatório do meu calendário como iCalendar, ele fica com cerca de 740 bytes; mesmo um calendário extremamente cheio, com eventos a cada 15 minutos entre 9h e 17h, teria 11.680 itens em um ano inteiro, apenas cerca de 8 MB
  Então, em vez de projetar um esquema, parece ser um problema em que talvez seja muito melhor implementar o calendário simplesmente com varredura sequencial. Se você otimizar um parser de iCalendar para percorrer intervalos de eventos despejados com throughput na casa de GB/s, até o pior caso acima poderia ser escaneado em milissegundos de um dígito
  Otimizar um parser é um problema muito mais simples, que você resolve uma vez, do que mudar ou estender um modelo de dados ruim depois que ele já se espalhou para muitos usuários; e, de todo modo, a primeira tarefa do novo modelo de dados provavelmente seria importar/exportar iCalendar, então parece uma troca bastante boa

  • Mas aí você não teria que recriar buscas por intervalo como BETWEEN ... AND ... do SQL? O mesmo vale para encontrar eventos dos usuários 1, 2 e 3
    Em uma aplicação real, você provavelmente já teria coisas como contas de usuário em um banco de dados relacional, mas de repente precisaria escanear eventos em um diretório e depois associar esses resultados a registros do banco de dados
    Pode fazer sentido em uma aplicação específica, mas há certas tarefas que bancos de dados fazem realmente bem, e é por isso que eles são uma boa escolha. Com os índices adequados, também seria possível obter throughput no mesmo nível ou melhor. A menos que você crie uma estrutura de diretórios inteligente para eventos — o que, na prática, é como um índice unidimensional —, enquanto em um banco de dados dá para criar índices sobre várias dimensões e combinações de dimensões
    No fim, isso está certo, e é uma questão de trade-off
  • iCalendar foi projetado como um formato de intercâmbio, não como um formato de armazenamento para acesso rápido a dados de calendário
    A idade aparece no formato: dá para ver que ele foi criado antes de XML/JSON “pegarem”
    Referência: https://en.wikipedia.org/wiki/ICalendar
  • Mesmo que dê para fazer sem um DBMS, os dados ainda precisam de um bom esquema, e esse esquema não deveria ser um conjunto de registros iCalendar. Por exemplo, em vez de IDs de usuário estáveis, há apenas endereços de e-mail mutáveis e repetidos
  • Essa abordagem também parece poder oferecer uma solução mais limpa para o problema dos slots de tempo infinitos mencionado no texto

• É um texto sobre como lidar com eventos recorrentes em aplicações e, pessoalmente, me abriu os olhos. Recomendo muito a leitura: https://github.com/bmoeskau/Extensible/blob/master/recurrenc...

  • É interessante porque os conselhos do texto original e os desse artigo são opostos
    O original diz para sempre armazenar o fuso horário em cada data, enquanto o artigo linkado diz para converter tudo para UTC. Nessa parte, concordo com o original
    O original diz para criar uma linha no banco de dados para cada evento, enquanto o artigo linkado diz para não fazer isso. Nessa parte, concordo com o artigo linkado

• A vez em que mais fracassei em uma entrevista foi quando tive que criar um sistema simples de reservas e ainda lidar com reservas recorrentes
  Desde os primeiros anos da faculdade eu não ficava tão perdido e confuso tentando fazer algo funcionar em código. Até hoje, se preciso trabalhar com data/hora, já fico com medo; não sei por quê, mas isso simplesmente não encaixa direito na minha cabeça

  • Uma pergunta de design como “projete um sistema de reservas, incluindo reservas recorrentes” é parecida com “escreva uma função para ordenar uma lista de strings, mas respeitando as convenções de ordem alfabética para strings UTF-8 arbitrárias e todos os locales”
    Parece simples à primeira vista, mas, quando você tenta fazer funcionar de forma geral no mundo real, vira um problema complexo a ponto de ser quase impossível
  • Não precisa se culpar tanto. Fui encarregado de melhorar o calendário de um CMS para dar suporte a muito mais recursos, e isso quase me engoliu. Na época, eu já tinha uns 8 anos de carreira
    É um problema enganosamente difícil e exige ótimas habilidades de modelagem de dados

• A capacidade de modelar sistemas é subestimada. Em um novo domínio, é preciso começar com uma análise completa do problema para capturar tanto a estrutura estática do sistema, ou seja, o modelo de classes, quanto o comportamento dinâmico, isto é, os casos de uso
  Se você pula direto para o modelo estático do banco de dados, é fácil perder o comportamento dinâmico. Em apps CRUD simples isso pode ser aceitável, mas em sistemas mais complexos pode ser um grande erro

  • Como cientista de dados, já tive algumas dificuldades na hora, em entrevistas, com perguntas aparentemente simples do tipo “como você criaria o esquema?”
    A última de que me lembro era como eu criaria uma tabela de produtos com cupons. No começo pensei que duas tabelas bastariam, mas será que não é preciso manter histórico? Então seria necessário colocar datas/horários por produto e por cupom e atualizá-los. Agora também preciso pensar nos índices das tabelas e se um join para obter o preço com desconto é uma boa abordagem. A maioria dos cupons só pode ser usada uma vez por pessoa; como diabos eu implementaria isso?
    Provavelmente queriam apenas uma tabela de produtos + cupons simples, mas acabei mergulhando sozinho nisso por um bom tempo, feito maluco
  • Como é o resultado dessa análise? Ficaria curioso se houvesse algum link de referência, como um documento de exemplo ou um capítulo de livro

• Acho que é um domínio bem escolhido para demonstrar modelagem e um bom texto introdutório
  O termo “anchor” soa um pouco estranho, mas a explicação é bem concreta e pé no chão, como uma âncora de verdade, então parece funcionar bem
  O conceito de definir atributos por meio de perguntas é sólido. Muitas vezes pulamos direto para os nomes mínimos de colunas/atributos sem definir a quais perguntas estamos tentando responder, e isso não remove a ambiguidade que está na cabeça do cliente. Definição de atributos baseada em perguntas é uma boa maneira de chegar rapidamente à clareza

  • O termo “anchor” vem de Anchor Modeling (https://en.wikipedia.org/wiki/Anchor_modeling). “Entity” é amplamente usado, mas eu não queria usá-lo porque pode trazer bagagem e pressupostos desnecessários
    Além disso, esse termo é extremamente sobrecarregado. Em ciência da computação, praticamente tudo é objeto ou entidade
    A reação de que o conceito de definir atributos por meio de perguntas é sólido serve como uma confirmação importante da validade dessa abordagem

• Fusos horários dão um nó na cabeça, especialmente perto dos momentos de transição
  Imagine que um fuso horário avance uma hora ao entrar no horário de verão e recue uma hora ao voltar para o horário padrão
  Quando o relógio pula uma hora para a frente, um evento de 1 hora pode parecer ocupar 2 horas na tela, e essa segunda hora pode ser inalcançável ou nem existir
  Quando o relógio recua uma hora, um evento de 1 hora pode parecer ter 2 horas ou 0 hora
  Fusos horários são construções humanas, então não se deve deixar valores hardcoded. Um dia eles mudam

  • Fica ainda mais interessante quando você acompanha o horário levando deslocamentos em conta. A primeira vez que passei por isso foi acompanhando o tempo em um navio que ficou vários dias no mar
    É possível cruzar repetidamente fusos horários nos dois sentidos, cruzar a linha internacional de data e até ter uma data/hora de início local posterior à data/hora de término local
  • Isso quer dizer que devemos fazer calendários totalmente em UTC, sem fusos horários? Concordo que fusos horários são algo que nós criamos, mas eles foram criados para uma melhor organização local. Trabalho com clientes no mundo todo, e todo mundo usa fusos horários. Pessoalmente, isso me parece uma exigência bem absurda
    Seria melhor focar em eliminar o conceito de horário de verão nas poucas regiões que ainda o usam. Ele é o que mais causa confusão entre fusos horários, especialmente ao planejar eventos depois da próxima transição

• Não quero armazenar duas datas para um evento. É mais fácil armazenar o horário de início e a duração do evento
  Assim, a lógica de atualização do evento fica simples. O horário de término pode ser calculado a qualquer momento com base no horário de início e na duração

  • Para ser sincero, dá para armazenar os três: início, término e duração, e usar o que for necessário conforme o objetivo. Basta ter um único ponto de entrada/atualização que mantenha as representações alternativas sincronizadas
  • Essa abordagem pode ser um problema para a performance do banco de dados. Seria preciso calcular a duração em tempo real a cada consulta
  • O inverso não é a mesma coisa? Se você tem início e término, pode simplesmente calcular a duração

• Antigamente trabalhei em uma aplicação de calendário com eventos recorrentes. Depois de muita pesquisa, decidimos usar RRules para representar isso, e fiquei muito satisfeito. Esse trabalho inicial foi feito quando eu estava em uma agência
  Mais tarde, quando entrei na empresa como funcionário em tempo integral, fiquei chocado ao ver que um contratado de outra empresa tinha removido RRules e mudado tudo para criar e apagar instâncias de eventos em tempo real. Quase não havia tolerância a falhas, então um script que também fazia outras tarefas e às vezes falhava acabava deixando de criar novos eventos. Com isso, eventos recorrentes mensais ficavam com meses faltando
  Foi muito frustrante ver alguém remover algo feito com tanta reflexão e pesquisa sem dedicar o mesmo esforço. Depois de poucas semanas naquela empresa, já dava para perceber que o CEO esperava que a equipe de engenharia continuasse entregando recursos que ninguém usava, do jeito que ele queria, e, infelizmente, por causa da incerteza do mercado de trabalho, acabei ficando 2 anos
  Além disso, quando pesquisei depois, foi bem amargo ver no Glassdoor várias avaliações falsas, obviamente escritas pelo CEO, todas com o mesmo estilo e sem uma única palavra negativa. Eu e várias pessoas que trabalharam lá o detestávamos, mas pelo menos me consola o fato de eu ter escrito alguns dos meus melhores ensaios naquela empresa. O CTO também não tinha jeito

• Em certa época, implementei um backend de calendário e controle de recursos para uma plataforma low-code
  O controle era altamente customizável e permitia escolher várias visualizações, como diária, mensal e anual, além de uma visualização de recursos. Era possível reservar recursos com grupos customizados e agrupá-los por plugin, por ID de recurso etc. A fonte de dados definia “plugins”, e também eram especificadas as colunas de início/término, a coluna de título e a coluna de recurso. Os recursos podiam vir de uma chave estrangeira ou de uma relação 1:1, ou ainda de uma relação 1:N de uma fonte de dados “filha”, ou da mesma fonte de dados/tabela
  Também implementei vários tipos de séries de compromissos, como mensais, semanais, em determinados dias da semana e diárias, e dava para escolher quais valores de coluna copiar. Também tratei conflitos de compromissos, que podiam ser considerados conflitos apenas ao reservar o mesmo recurso. Também era possível configurar buffers antes e depois de um compromisso, nos quais outros compromissos não podiam ser inseridos
  Às vezes foi desafiador por causa dos fusos horários da Europa e do horário de verão/inverno, mas foi um trabalho bem divertido

• A única coisa que quero no Google Calendar é um log de alterações do próprio calendário. Por favor, adicionem isso ao banco de dados