Two key things:
- Split the event from its schedule.
- Split the recurring schedule definition from its individual schedule.
By splitting the event from its schedule, instances of a recurring event can share the same event information like its description and location, while others can use an edited version.
events:
id
name
description
location
...
By splitting the recurring schedule from its individual schedule makes querying for events much simpler.
recurring_event_schedules:
id
event_id
schedule: jsonb
start_at: timestamp with time zone
This is the recurring schedule for an event.
Recurring schedules get complicated. You could have multiple tables for each type of recurring period (annual, biannual, daily, monthly, weekly, etc...) or punt and use jsonb to store the spec. The spec might be { "period": "weekly", "days": [1,2,3,4,5] } for every weekday. Reference the iCal spec and good calendaring software for what sorts of repeat events you'd want and other attributes.
jsonb is not a panacea, but this table is only for reference to generate and update events. It is not used for runtime querying. For that there's a second table.
Then generate individual scheduled events from this, out to a configurable limit.
event_schedules:
id
event_id
recurrent_event_schedule_id
schedule: tztsrange
This table is simple. What event, when, and what recurring schedule it was generated from.
Since you're using Postgres, rather than separate start/end fields, use a timestamp range. These are supported by special operators to determine if a time is inside a range, or if ranges overlap, and so on.
Rails will accept Range types, but it does not directly support the operators. For example, EventSchedule.where("duration @> ?::date", Date.new(2014, 2, 12)) finds all events happening on 2014-02-12.
If you want to edit an individual scheduled event, copy its Event, edit it, and link the EventSchedule to that new Event.
If you want to change the recurring event schedule, select the event_schedules by recurrent_event_schedule_id.
If you want to change the event for all scheduled events, edit the Event linked to the RecurringEventSchedule.
In this way you can define complex recurring events and query the simple concrete events.
