【DB設計/テーブル設計】リカーリングイベント(定期開催/周期開催)をDBで表現する📝

リカーリングイベント(定期開催/周期開催)をDBで表現する📝

リカーリングイベント（例: 毎週水曜開催）をデータベースで表現する場合、柔軟性と効率性を考慮した設計が必要です。

1. 基本的なテーブル構造

リカーリングイベントを表現するため、以下のようなテーブルを設計します。

イベントテーブル (events)

イベント自体の基本情報を保持。

• event_id (PK): イベントの一意なID
• name: イベント名
• description: イベントの説明
• created_at: 作成日時
• その他: 必要に応じてカテゴリ、所有者IDなど

リカーリングルールテーブル (recurrence_rules)

リカーリングのルールを定義。

• rule_id (PK): ルールの一意なID
• event_id (FK): 関連するイベントのID
• frequency: 繰り返し頻度（例: DAILY, WEEKLY, MONTHLY）
• interval: 繰り返し間隔（例: 1 = 毎週、2 = 2週間ごと）
• by_day: 繰り返しの曜日（例: WE = 水曜日。複数指定可）
• start_date: 繰り返しの開始日
• end_date: 繰り返しの終了日（NULLなら無期限）
• count: 繰り返し回数（オプション、例: 10回で終了）

イベントインスタンステーブル (event_instances)（オプション）

リカーリングイベントの具体的なインスタンス（個々の開催日）を保持。必要に応じて使用。

• instance_id (PK): インスタンスの一意なID
• event_id (FK): 関連するイベントのID
• rule_id (FK): 関連するリカーリングルールのID
• start_datetime: インスタンスの開始日時
• end_datetime: インスタンスの終了日時
• is_cancelled: キャンセルフラグ（例外処理用）

2. 設計のポイント

(1) リカーリングルールの表現

• iCalendar (RFC 5545) にインスパイアされた構造が一般的。frequencyやby_dayを使って「毎週水曜」などのルールを表現。
• 例: 「毎週水曜」は以下のように定義。

  INSERT INTO recurrence_rules (event_id, frequency, interval, by_day, start_date, end_date)
  VALUES (1, 'WEEKLY', 1, 'WE', '2025-01-01', NULL);
  

• 柔軟性を高めるため、by_dayはカンマ区切りや配列（DBが対応している場合）で複数曜日をサポート。

(2) インスタンステーブルの必要性

• 不要な場合: ルールベースで動的にインスタンスを計算する（例: カレンダー表示時にルールから日付を生成）。ストレージを節約。
• 必要な場合: 以下のようなケースではインスタンステーブルを作成。
  • 特定の日だけキャンセルや時間変更などの例外を扱う。
  • パフォーマンスを最適化（ルール計算を毎回行わない）。
  • 履歴管理やログが必要。

(3) 例外処理

• 特定の日だけキャンセルや変更がある場合、以下のように対応。
  • インスタンステーブルにis_cancelledやoverride_start_datetimeを追加。
  • 別テーブル（recurrence_exceptions）で例外を管理。

    CREATE TABLE recurrence_exceptions (
        exception_id SERIAL PRIMARY KEY,
        rule_id INT REFERENCES recurrence_rules(rule_id),
        exception_date DATE,
        is_cancelled BOOLEAN,
        new_start_datetime TIMESTAMP,
        new_end_datetime TIMESTAMP
    );
    

(4) パフォーマンス考慮

• 頻繁にアクセスされる場合、インデックスを適切に設定（例: event_id, rule_id, start_date）。
• インスタンステーブルを使用する場合、大量のデータになる可能性があるため、パーティショニングやアーカイブ戦略を検討。

3. 具体例: 毎週水曜のイベント

「毎週水曜の午後2時から1時間のイベント」を表現する場合：

1. eventsテーブルにイベントを登録。

   INSERT INTO events (name, description) VALUES ('Weekly Meeting', 'Team sync-up');
   

2. recurrence_rulesにルールを登録。

   INSERT INTO recurrence_rules (event_id, frequency, interval, by_day, start_date, start_time)
   VALUES (1, 'WEEKLY', 1, 'WE', '2025-01-01', '14:00:00');
   

3. 必要ならevent_instancesに具体的な日付を生成（例: 2025-01-01, 2025-01-08など）。

4. 代替アプローチ

• JSONBや配列: PostgreSQLなどでは、リカーリングルールをJSONBや配列で保存し、柔軟性を向上。

  CREATE TABLE recurrence_rules (
      rule_id SERIAL PRIMARY KEY,
      event_id INT REFERENCES events(event_id),
      rule JSONB -- 例: {"frequency": "WEEKLY", "by_day": ["WE"], "interval": 1}
  );
  

• 外部ライブラリ: アプリケーション側でiCalendarライブラリ（例: rrule in JavaScript）を使用し、DBには最小限の情報のみ保存。

5. 考慮事項

• タイムゾーン: start_datetimeやend_datetimeはTIMESTAMP WITH TIME ZONEを使用。
• スケーラビリティ: 大量のイベントや長期間の繰り返しを扱う場合、生成するインスタンス数を制限。
• ユーザー体験: カレンダーアプリのように、ルール変更時に「このイベントのみ」「以降すべて」などの選択肢をサポート。