Design a calendar system

[000]

Designing a calendar system requires careful planning, particularly for the database schema, to ensure flexibility, performance, and scalability. Below is an outline for the database schema of a calendar system:

### **Key Entities**
1. **Users**: Represents the people who will use the calendar system.
2. **Events**: Represents the events scheduled in the calendar.
3. **Calendars**: Represents user-specific or shared calendars.
4. **Invitations/Attendees**: Represents the attendees invited to specific events.
5. **Reminders**: Notifications or alerts related to events.
6. **Recurring Events**: Handling events that repeat over a time period.

### **Database Schema**

#### 1. **Users Table**
The table stores details of each user in the system.

```sql
CREATE TABLE Users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    timezone VARCHAR(50) DEFAULT 'UTC',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

- `user_id`: Unique identifier for each user.
- `email`: For sending notifications or invites.
- `timezone`: Helps with time zone conversions.
 
#### 2. **Calendars Table**
This table stores details for each calendar. A user may have multiple calendars (work, personal, etc.).

```sql
CREATE TABLE Calendars (
    calendar_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    is_shared BOOLEAN DEFAULT FALSE,
    color VARCHAR(7) DEFAULT '#FFFFFF',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
```

- `calendar_id`: Unique ID for each calendar.
- `user_id`: Owner of the calendar.
- `is_shared`: Determines if the calendar is shared.
 
#### 3. **Events Table**
This table captures details about specific events.

```sql
CREATE TABLE Events (
    event_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    calendar_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    location VARCHAR(255),
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    is_all_day BOOLEAN DEFAULT FALSE,
    recurrence_rule VARCHAR(255),  -- For recurring events, iCal format
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (calendar_id) REFERENCES Calendars(calendar_id)
);
```

- `start_time` & `end_time`: Define when the event occurs.
- `is_all_day`: Flags if an event is an all-day event.
- `recurrence_rule`: To define repeating events (e.g., daily, weekly, using iCal format).

#### 4. **Attendees Table**
This table stores the relationship between events and users who are invited.

```sql
CREATE TABLE Attendees (
    attendee_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    event_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    status ENUM('invited', 'accepted', 'declined', 'tentative') DEFAULT 'invited',
    FOREIGN KEY (event_id) REFERENCES Events(event_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
```

- `status`: Tracks the RSVP status of each attendee.

#### 5. **Reminders Table**
This table stores reminder details for events.

```sql
CREATE TABLE Reminders (
    reminder_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    event_id BIGINT NOT NULL,
    method ENUM('email', 'popup') DEFAULT 'email',
    time_offset INT NOT NULL, -- Minutes before the event to send a reminder
    FOREIGN KEY (event_id) REFERENCES Events(event_id)
);
```

- `time_offset`: Defines how many minutes before the event the reminder should be triggered.

#### 6. **Recurring Event Exceptions Table**
This table handles exceptions to recurring events (e.g., event moved to a different day).

```sql
CREATE TABLE RecurringEventExceptions (
    exception_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    event_id BIGINT NOT NULL,
    original_date DATE NOT NULL,
    new_date DATE,
    canceled BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (event_id) REFERENCES Events(event_id)
);
```

- `original_date`: The original occurrence date.
- `new_date`: The updated occurrence date (if the event is rescheduled).
- `canceled`: Flags whether the event instance was canceled.

---

### **Key Considerations**
- **Time Zones**: Storing `start_time` and `end_time` in UTC ensures consistency, and you can convert them to the user’s local timezone using the `timezone` field in the `Users` table.
- **Recurrence Rules**: Storing recurrence rules using a standard format like iCal's RRULE enables flexibility in defining complex repeating events.
- **Indexing**: Index `start_time`, `end_time`, and `user_id` fields for fast queries related to fetching events and calendars.
- **Concurrency**: Implement optimistic locking on events (e.g., versioning) to prevent conflicting edits when multiple users modify shared calendars.
 
---

This schema is designed to be flexible, scalable, and capable of handling common calendar system features such as event scheduling, recurring events, reminders, and invitations. For large-scale usage, partitioning events by date or using sharding for users might also be necessary to improve performance.