+- 包子吧 - 我的帖子我做主！ (https://baozi8.com)
+-- 版块： 求职大典 (https://baozi8.com/forumdisplay.php?fid=1)
+--- 版块： 面经分享 (https://baozi8.com/forumdisplay.php?fid=3)
+--- 主题： Design a calendar system (/showthread.php?tid=303)
Design a calendar system - 000 - 10-13-2024
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.