存储用户自定义重复日程/定时任务的数据库结构设计咨询

Hey there, let's work through this recurring schedule database design problem—this is a super common use case, but getting the structure right makes handling all those repeat rules way easier. Below is a flexible, scalable table structure that covers every scenario you mentioned, plus explanations of how each field works and examples for different repeat patterns.

Here's the core table structure tailored to your needs, optimized for readability and flexibility:

CREATE TABLE schedule (
    schedule_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL, -- Foreign key linking to your users table
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_datetime DATETIME NOT NULL, -- The first occurrence's start time
    duration_minutes INT NOT NULL, -- Duration of each event (avoids redundant end time storage)
    repeat_type ENUM('DAILY', 'WEEKLY', 'MONTHLY', 'YEARLY') NOT NULL,
    repeat_interval INT DEFAULT 1, -- e.g., every 2 days, every 3 weeks
    week_days JSON, -- Stores target weekdays (1=Mon, 7=Sun; e.g., [1,3] for Mon/Wed)
    month_dates JSON, -- Stores target dates in a month (e.g., [5,20] for 5th & 20th)
    year_months JSON, -- Stores target months (1=Jan, 12=Dec; e.g., [3,9] for Mar/Sep)
    repeat_end_condition ENUM('NEVER', 'COUNT', 'UNTIL') DEFAULT 'NEVER',
    repeat_end_count INT, -- Max repetitions (only used if condition is COUNT)
    repeat_end_until DATETIME, -- Stop repeating after this date (only used if condition is UNTIL)
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Field Breakdown #

Let’s break down why each column is critical:

• schedule_id: Unique identifier for each schedule entry.
• user_id: Links the schedule to its owner—essential for multi-user applications.
• title/description: Basic context for the event (e.g., "Team Sync" or "Monthly Invoice Reminder").
• start_datetime: The baseline time of the first occurrence—used to calculate all future repetitions.
• duration_minutes: Stores how long each event lasts (instead of per-occurrence end times) to keep consistency across repeats.
• repeat_type: Defines the core repeat pattern, so your app knows which parameter fields to prioritize.
• repeat_interval: Controls the frequency of the repeat (e.g., 2 for every 2 days, 3 for every 3 weeks).
• week_days/month_dates/year_months: Conditional fields—only use the one matching your repeat_type. JSON lets you store multiple values (like two days a week) cleanly.
• repeat_end_condition: Lets users specify when the schedule stops (never, after X times, or by a certain date).
• is_active: Toggle to disable a schedule without deleting it (useful for pausing recurring events temporarily).
• created_at/updated_at: Standard audit fields to track when schedules are added or modified.

Example Usage for Your Specific Repeat Scenarios #

Let’s map your use cases directly to this structure:

1. Daily Repeat (Every day) #

INSERT INTO schedule (user_id, title, start_datetime, duration_minutes, repeat_type, repeat_interval)
VALUES (1, 'Morning Yoga', '2024-01-01 07:30:00', 45, 'DAILY', 1);

2. Weekly Twice (Monday & Wednesday) #

INSERT INTO schedule (user_id, title, start_datetime, duration_minutes, repeat_type, repeat_interval, week_days)
VALUES (1, 'Standup Meeting', '2024-01-01 10:00:00', 30, 'WEEKLY', 1, '[1,3]');

3. Custom Weekly (Every 2 weeks, Friday) #

INSERT INTO schedule (user_id, title, start_datetime, duration_minutes, repeat_type, repeat_interval, week_days)
VALUES (1, 'Client Check-In', '2024-01-05 14:00:00', 60, 'WEEKLY', 2, '[5]');

4. Monthly Twice (5th & 20th of every month) #

INSERT INTO schedule (user_id, title, start_datetime, duration_minutes, repeat_type, repeat_interval, month_dates)
VALUES (1, 'Bill Payment Reminder', '2024-01-05 09:00:00', 15, 'MONTHLY', 1, '[5,20]');

5. Monthly Custom (15th of every 3 months) #

INSERT INTO schedule (user_id, title, start_datetime, duration_minutes, repeat_type, repeat_interval, month_dates)
VALUES (1, 'Quarterly Review', '2024-01-15 13:00:00', 120, 'MONTHLY', 3, '[15]');

6. Yearly Custom (March & September every year) #

INSERT INTO schedule (user_id, title, start_datetime, duration_minutes, repeat_type, repeat_interval, year_months, repeat_end_condition, repeat_end_until)
VALUES (1, 'Annual Audit Prep', '2024-03-01 08:00:00', 240, 'YEARLY', 1, '[3,9]', 'UNTIL', '2028-12-31 23:59:59');

Key Implementation Tips #

• Consistent Numbering: Stick to a standard for weekdays (e.g., 1=Monday to 7=Sunday) and months (1=January to 12=December) to avoid logic bugs.
• JSON Handling: Most modern databases support JSON natively—use built-in JSON functions to filter schedules (e.g., find all weekly events that include Wednesday).
• Future Occurrence Calculation: Use libraries like python-dateutil (Python) or moment-recur (JavaScript) to generate future event times based on the schedule parameters—this saves you from writing complex date logic from scratch.
• Edge Case Handling: Account for months with fewer than 31 days (e.g., if a user picks the 31st, adjust to the last day of months like February or April).

内容的提问来源于stack exchange，提问作者Metabolic