Database Storage Solutions for Calendar Recurring Events: From Simple Patterns to Complex Rules

When building calendar event systems, handling recurring events is a common technical challenge. Effective storage solutions need to balance data integrity, query performance, and scalability. This paper systematically analyzes database implementation strategies for recurring events based on two mainstream methods.

Data Structure Design for Simple Recurrence Patterns

For regular recurring events such as "every 4 days" or "every Tuesday," a streamlined storage structure can be employed. The core concept involves decomposing recurrence rules into start time and interval periods.

The basic table structure is designed as follows:

-- Main events table
CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

-- Event metadata table
CREATE TABLE events_meta (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_id INT NOT NULL,
    repeat_start BIGINT NOT NULL,      -- Start timestamp (without time)
    repeat_interval BIGINT NOT NULL,   -- Interval in seconds
    FOREIGN KEY (event_id) REFERENCES events(id)
);

The advantages of this design include:

• High Storage Efficiency: Each recurrence rule requires only one record
• Simple Queries: Modulo operations determine date matches
• Strong Extensibility: Supports multiple rule combinations

Efficient Query Algorithm Implementation

Based on this structure, querying recurring events for specific dates becomes exceptionally concise:

SELECT e.*
FROM events e
JOIN events_meta em ON em.event_id = e.id
WHERE (target_timestamp - repeat_start) % repeat_interval = 0
  AND target_timestamp >= repeat_start;

Algorithm principle:

1. Calculate the difference between target date and start date
2. Use modulo operation to check if difference is multiple of interval
3. Ensure target date is not earlier than start date

Performance comparisons show this single-join approach significantly outperforms earlier multi-join solutions, avoiding join buffer usage.

Extension Scheme for Complex Recurrence Rules

For irregular patterns like "second Friday of each month" or "March 3rd every year," more detailed rule descriptions are needed. Additional dimension fields can be added to the metadata table:

ALTER TABLE events_meta ADD (
    repeat_year VARCHAR(4) DEFAULT '*',
    repeat_month TINYINT DEFAULT NULL,
    repeat_day TINYINT DEFAULT NULL,
    repeat_week TINYINT DEFAULT NULL,      -- Week number in month (1-5)
    repeat_weekday TINYINT DEFAULT NULL    -- Day of week (1-7)
);

Queries must consider both pattern types:

SELECT e.*
FROM events e
JOIN events_meta em ON em.event_id = e.id
WHERE 
    -- Simple interval pattern
    (repeat_interval IS NOT NULL 
     AND (target_timestamp - repeat_start) % repeat_interval = 0
     AND target_timestamp >= repeat_start)
    OR
    -- Complex rule pattern
    (repeat_interval IS NULL
     AND (repeat_year = '*' OR repeat_year = YEAR(target_date))
     AND (repeat_month = '*' OR repeat_month = MONTH(target_date))
     AND (repeat_day = '*' OR repeat_day = DAY(target_date))
     AND (repeat_week = '*' OR repeat_week = calculate_week_number(target_date))
     AND (repeat_weekday = '*' OR repeat_weekday = WEEKDAY(target_date))
     AND repeat_start <= target_timestamp);

Practical Recommendations and Optimization Strategies

In practical applications, the following optimization measures are recommended:

1. Indexing Strategy: Create composite indexes on event_id, repeat_start, and repeat_interval fields
2. Data Partitioning: Partition event tables by time ranges to improve historical data query efficiency
3. Caching Mechanism: Cache frequently queried date results to reduce database load
4. Batch Processing: Query multiple days' events simultaneously during calendar rendering

Example: Creating optimized indexes

CREATE INDEX idx_event_query ON events_meta 
(event_id, repeat_start, repeat_interval);
CREATE INDEX idx_complex_rules ON events_meta 
(repeat_year, repeat_month, repeat_week, repeat_weekday);

Edge Case Handling

Special attention should be paid to the following edge cases during implementation:

• Timezone Handling: All timestamps should be stored in UTC with frontend conversion
• Leap Years/Months: Complex rules must consider calendar peculiarities
• Rule Termination: Support for setting recurrence end conditions
• Exceptional Dates: Handling non-existent dates like "31st of each month"

Through these solutions, efficient calendar systems supporting both simple recurrence patterns and complex rules can be constructed. This layered design allows flexible system expansion based on actual requirements while maintaining excellent query performance.