Designing a Google Calendar Clone: Step‑by‑Step Database Modeling Tutorial

Database Design Tutorial for a Google Calendar Clone

This tutorial demonstrates how to design a database for a Google Calendar clone, starting with a logical model and ending with physical SQL tables.

Logical Model Overview

We identify the main entities: User, Timezone, DayEvent (all‑day event), TimeEvent, DayOfTheWeek, DaySlot, and TimeSlot. Each entity is described by questions that define its attributes, such as user email, event name, start and end dates, recurrence rules, and whether a slot is skipped.

Key Attributes

User email (string)

Timezone display name (string)

DayEvent name, begin_date, end_date, repetition frequency, step, monthly rule, repeat limit, etc.

TimeEvent name, begin_local_time, end_local_time

DaySlot date and is_skipped flag

TimeSlot begin_local_time, end_local_time, is_skipped flag

Relationships

User creates many DayEvents and TimeEvents (1:N).

DayEvent links to DayOfTheWeek for weekly recurrence (M:N).

TimeEvent links to DayOfTheWeek for weekly recurrence (M:N).

DayEvent generates many DaySlots (1:N); TimeEvent generates many TimeSlots (1:N).

Timezone is used for start and end times of TimeEvents and TimeSlots (1:N).

Physical Model and SQL

Using the “one table per anchor” strategy we create nine tables: users, timezones, day_events, time_events, day_slots, time_slots, day_event_dows, time_event_dows, and days_of_the_week (virtual). The following SQL creates the tables.

CREATE TABLE users (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(64) NOT NULL
);
CREATE TABLE timezones (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL
);
CREATE TABLE day_events (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INTEGER NOT NULL,
  name VARCHAR(128) NOT NULL,
  begin_date DATE NOT NULL,
  end_date DATE NOT NULL,
  repeated VARCHAR(24) NULL,
  repetition_step INTEGER NULL,
  repeated_monthly_on VARCHAR(24) NULL,
  repeated_until VARCHAR(24) NULL,
  repeated_until_date DATE NULL,
  repeated_reps INTEGER NULL
);
CREATE TABLE time_events (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INTEGER NOT NULL,
  start_timezone_id INTEGER NULL,
  end_timezone_id INTEGER NULL,
  name VARCHAR(128) NOT NULL,
  begin_local_time DATETIME NOT NULL,
  end_local_time DATETIME NOT NULL
);
CREATE TABLE day_slots (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  day_event_id INTEGER NOT NULL,
  the_date DATE NOT NULL,
  is_skipped TINYINT UNSIGNED NOT NULL DEFAULT 0
);
CREATE TABLE time_slots (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  time_event_id INTEGER NOT NULL,
  begin_local_time DATETIME NOT NULL,
  end_local_time DATETIME NOT NULL,
  start_timezone_id INTEGER NOT NULL,
  end_timezone_id INTEGER NOT NULL,
  is_skipped TINYINT UNSIGNED NOT NULL DEFAULT 0
);
CREATE TABLE day_event_dows (
  day_event_id INTEGER NOT NULL,
  day_of_week VARCHAR(3) NOT NULL,
  PRIMARY KEY (day_event_id, day_of_week),
  KEY (day_of_week)
);
CREATE TABLE time_event_dows (
  time_event_id INTEGER NOT NULL,
  day_of_week VARCHAR(3) NOT NULL,
  PRIMARY KEY (time_event_id, day_of_week),
  KEY (day_of_week)
);

Indexes on foreign‑key columns (e.g., day_events.user_id) should be added based on query patterns.