Features Included

1. User Authentication: Secure login and registration.

2. Event Management: Creating, updating, and deleting events.

3. Recurring Events: Support for events that repeat at regular intervals.

4. Invitations: Users can invite others to events and manage RSVPs.

5. Reminders: Users can set reminders for events.

6. Multiple Calendars: Users can create multiple calendars and categorize events.

7. Sharing and Permissions: Share calendars with other users and set permissions.

8. Time Zone Support: Events can be scheduled in different time zones.

9. Notifications: Email or SMS notifications for events and reminders.

Entity/Table with Detailed Info about Every Value

1. 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
    );
   

2. 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)
    );
   

3. 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;
   

4. 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)
    );
   

5. 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)
    );
   

6. 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

All Queries Required for Applications

1. Retrieve events for a specific calendar:

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

2. 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;
   

3. Retrieve reminders for a specific event:

    SELECT * FROM Reminders
    WHERE event_id = :event_id;
   

4. 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;
   

5. 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

1. 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);
     

2. 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

1. Use Database Profiler: Regularly profile queries to identify slow-performing queries and optimize accordingly.

2. Caching: Implement caching for frequently accessed data to reduce database load and improve performance.

3. Denormalization: Consider denormalizing data for frequently accessed information, such as precomputed event summaries.

4. Partitioning: Partition large tables based on criteria such as date ranges to improve query performance.

5. 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