[Design] Calendar DB Schema

Read full article for free: https://liverungrow.medium.com/design-calendar-db-schema-855e37b84703?sk=c97dfe9429ce62fd0c9585c638110f10

Requirements?

1. Need to support recurring events?
2. Multiple users
3. Event with multiple attendees?
4. Reminder notifications?

Basic Schema for Calendar Events

• Events Table: Stores information about individual events.
• Users Table: Stores information about users (for multi-user systems).
• Event_Attendees Table: Relates events to users who are attending.
• Event_Reminders Table: Stores reminders for events.
• Event_Recurrence Table: Store recurrence rules for events. We put the reminder table separately so that we can have multiple recurrence rule for an event. Or if it has a complex recurrence pattern like “every 2nd Wednesday of the month” or “every 5th day of every month.”

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,  -- The creator/owner of the event
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    location VARCHAR(255)…