Design Google Calendar - Database Model

18 May 20246 min read

Features Included

User Authentication: Secure login and registration.
Event Management: Creating, updating, and deleting events.
Recurring Events: Support for events that repeat at regular intervals.
Invitations: Users can invite others to events and manage RSVPs.
Reminders: Users can set reminders for events.
Multiple Calendars: Users can create multiple calendars and categorize events.
Sharing and Permissions: Share calendars with other users and set permissions.
Time Zone Support: Events can be scheduled in different time zones.
Notifications: Email or SMS notifications for events and reminders.

Entity/Table with Detailed Info about Every Value

Users Table:

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

Calendars Table:

CREATE TABLE Calendars (
    calendar_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    timezone VARCHAR(50) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

Events Table:

CREATE TABLE Events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    calendar_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    location VARCHAR(255),
    is_recurring BOOLEAN DEFAULT FALSE,
    recurrence_rule VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (calendar_id) REFERENCES Calendars(calendar_id)
);

-- OR

CREATE TABLE Events (
    event_id INTEGER PRIMARY KEY AUTOINCREMENT,
    calendar_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    location VARCHAR(255),
    is_recurring BOOLEAN DEFAULT FALSE,
    recurrence_rule VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (calendar_id) REFERENCES Calendars(calendar_id)
);

-- Trigger to update the 'updated_at' column on row update
CREATE TRIGGER update_events_updated_at
AFTER UPDATE ON Events
FOR EACH ROW
BEGIN
    UPDATE Events SET updated_at = CURRENT_TIMESTAMP WHERE event_id = OLD.event_id;
END;

Invitations Table:

CREATE TABLE Invitations (
    invitation_id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    invitee_id INT NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES Events(event_id),
    FOREIGN KEY (invitee_id) REFERENCES Users(user_id)
);

Reminders Table:

CREATE TABLE Reminders (
    reminder_id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    remind_at DATETIME NOT NULL,
    method VARCHAR(50) NOT NULL,
    FOREIGN KEY (event_id) REFERENCES Events(event_id)
);

Permissions Table:

CREATE TABLE Permissions (
    permission_id INT AUTO_INCREMENT PRIMARY KEY,
    calendar_id INT NOT NULL,
    user_id INT NOT NULL,
    access_level VARCHAR(50) NOT NULL,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (calendar_id) REFERENCES Calendars(calendar_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

ER-Diagram

Hierarchical-Diagram

Details about 1-NF, 2-NF, etc., of Each Table in Tabular Format with Explanation Details

Table	1-NF	2-NF	3-NF	Explanation
Users	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by `user_id`. Non-key attributes depend solely on the primary key. No transitive dependencies.
Calendars	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by `calendar_id`. Non-key attributes depend solely on the primary key. No transitive dependencies.
Events	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by `event_id`. Non-key attributes depend solely on the primary key. No transitive dependencies.
Invitations	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by `invitation_id`. Non-key attributes depend solely on the primary key. No transitive dependencies.
Reminders	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by `reminder_id`. Non-key attributes depend solely on the primary key. No transitive dependencies.
Permissions	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by `permission_id`. Non-key attributes depend solely on the primary key. No transitive dependencies.

All Queries Required for Applications

Retrieve events for a specific calendar:

SELECT * FROM Events
WHERE calendar_id = :calendar_id
AND start_time >= :start_time
AND end_time <= :end_time;

Retrieve invitations for a specific user:

SELECT e.*, i.status
FROM Events e
JOIN Invitations i ON e.event_id = i.event_id
WHERE i.invitee_id = :user_id;

Retrieve reminders for a specific event:

SELECT * FROM Reminders
WHERE event_id = :event_id;

Retrieve calendars shared with a specific user:

SELECT c.*, p.access_level
FROM Calendars c
JOIN Permissions p ON c.calendar_id = p.calendar_id
WHERE p.user_id = :user_id;

Search for events based on keywords:

SELECT * FROM Events
WHERE calendar_id = :calendar_id
AND (title LIKE :keyword OR description LIKE :keyword);

All Required Indexing or Background Jobs

Indexing:
- Index on user_id in the Users table for fast user lookup:
```
CREATE INDEX idx_user_id ON Users (user_id);
```
- Index on calendar_id in the Calendars table for efficient calendar retrieval:
```
CREATE INDEX idx_calendar_id ON Calendars (calendar_id);
```
- Index on event_id in the Events table for optimizing event queries:
```
CREATE INDEX idx_event_id ON Events (event_id);
```
- Index on invitee_id in the Invitations table for fast invitation retrieval:
```
CREATE INDEX idx_invitee_id ON Invitations (invitee_id);
```
- Index on event_id in the Reminders table for efficient reminder retrieval:
```
CREATE INDEX idx_event_id ON Reminders (event_id);
```
- Index on user_id in the Permissions table for fast permission checks:
```
CREATE INDEX idx_user_id ON Permissions (user_id);
```
Background Jobs:
- Regularly update reminders to ensure timely notifications.
- Periodic maintenance tasks to analyze and optimize query performance.
- Background tasks to manage recurring events and generate instances.
- Cache frequently accessed data such as popular calendars and event details.

Query Plan Optimization

Use Database Profiler: Regularly profile queries to identify slow-performing queries and optimize accordingly.
Caching: Implement caching for frequently accessed data to reduce database load and improve performance.
Denormalization: Consider denormalizing data for frequently accessed information, such as precomputed event summaries.
Partitioning: Partition large tables based on criteria such as date ranges to improve query performance.
Load Balancing: Distribute the database load using load balancing techniques to handle high traffic efficiently.

By incorporating these features and optimizations, the Google Calendar-like application can handle dynamic event management, multi-user support, and efficient query processing, ensuring optimal performance even with a large user base.

Github Url - https://github.com/subhahu123/awesome-database-modelling-design/tree/main/google-calendar-database-modelling