- Recurring Pattern: Support recurring bookings for up to 3 months ahead (~60 bookings max)
- Frequency: Mostly Mon-Fri working days, with per-service day-of-week exceptions
- Modifications: Users can modify individual bookings or all remaining bookings
- Modification Behavior: Preserve individual changes when bulk-updating remaining bookings
- Usage Pattern: High certainty - only 10-20% of bookings get modified
- Historical Data: Keep all past bookings forever
- Service Rules: Validate future bookings against current service availability (cancel if rules change)
- Notifications: Automated reminders for upcoming bookings
- Conflicts: Block entire recurring series if any date conflicts
- Payment: No payment involved
- Reporting: Minimal reporting needs
- Create ~60 individual booking records when user sets up recurring booking
- Each booking is a separate row in the bookings table
- Linked by
recurring_group_id
- Create one recurring schedule record with configuration
- Separate table for skipped bookings
- Generate actual bookings on-the-fly or materialize as needed
1. High Certainty Usage Pattern
- Only 10-20% modification rate means bulk updates are rare
- Option 2 optimizes for frequent bulk changes that won't happen
- Avoid complexity you don't need
2. Automated Reminders Requirement
- Option 1: Simple query
SELECT * FROM bookings WHERE scheduled_date = tomorrow - Option 2: Must expand schedule + apply skips + apply modifications on every reminder check
3. Service Rule Validation
- Need to cancel future bookings when service availability changes
- Option 1: Query and update existing booking records directly
- Option 2: Must expand schedule, identify affected dates, create cancellation records
4. Conflict Detection
- Must block entire series if any conflict exists
- Option 1: Simple uniqueness/overlap check when creating bookings
- Option 2: Must expand entire 3-month schedule to check all conflicts
5. Historical Records
- Option 1: Bookings naturally stay in table forever (completed status)
- Option 2: Need extra logic to materialize completed bookings or reconstruct from schedules
6. Preserve Individual Changes
- When bulk-updating remaining bookings, must preserve previous individual modifications
- Option 1: Clear and simple - each booking has its own status/time
- Option 2: Complex precedence management between schedule rules, skips, and modifications
"Won't 60 records be too many?"
- No. 60 rows per recurring booking is trivial for any database
- Even with 10,000 users creating recurring bookings: 600K rows
- Easily handled with proper indexes
"What about bulk updates to all remaining bookings?"
- Yes, requires updating 20-40 rows (remaining bookings)
- But this is rare (10-20% of the time)
- Single UPDATE query handles it:
UPDATE bookings SET time = '15:00' WHERE recurring_group_id = X AND scheduled_date >= CURRENT_DATE AND status NOT IN ('skipped', 'cancelled')
CREATE TABLE bookings (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
service_id BIGINT NOT NULL,
scheduled_date DATE NOT NULL,
scheduled_time TIME NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'scheduled',
-- Values: scheduled, completed, cancelled, skipped
recurring_group_id UUID NULL,
-- Links bookings from same recurring series
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- Index for reminder queries (upcoming bookings)
CREATE INDEX idx_bookings_scheduled_date
ON bookings(scheduled_date);
-- Index for user's upcoming bookings
CREATE INDEX idx_bookings_user_upcoming
ON bookings(user_id, scheduled_date)
WHERE status = 'scheduled';
-- Index for bulk operations on recurring series
CREATE INDEX idx_bookings_recurring_group
ON bookings(recurring_group_id)
WHERE recurring_group_id IS NOT NULL;
-- Prevent double-booking (if needed)
CREATE UNIQUE INDEX idx_bookings_no_overlap
ON bookings(user_id, service_id, scheduled_date, scheduled_time)
WHERE status NOT IN ('cancelled', 'skipped');Creating Recurring Bookings:
-- Generate recurring_group_id
WITH recurring_series AS (
SELECT
gen_random_uuid() AS group_id,
generate_series(
'2024-03-01'::date,
'2024-06-01'::date,
'1 day'::interval
)::date AS booking_date
)
INSERT INTO bookings (user_id, service_id, scheduled_date, scheduled_time, recurring_group_id)
SELECT
123, -- user_id
456, -- service_id
booking_date,
'14:00'::time,
group_id
FROM recurring_series
WHERE EXTRACT(ISODOW FROM booking_date) IN (1, 2, 3, 4, 5); -- Mon-FriModifying Individual Booking:
UPDATE bookings
SET status = 'skipped'
WHERE id = 789;Modifying All Remaining (Preserving Individual Changes):
-- Change time for all remaining bookings that haven't been individually modified
UPDATE bookings
SET scheduled_time = '15:00'
WHERE recurring_group_id = 'abc-123'
AND scheduled_date >= CURRENT_DATE
AND status = 'scheduled'; -- Only touch bookings that are still in default stateService Rule Validation:
-- Cancel future bookings when service no longer available on Wednesdays
UPDATE bookings
SET status = 'cancelled'
WHERE service_id = 456
AND scheduled_date >= CURRENT_DATE
AND EXTRACT(ISODOW FROM scheduled_date) = 3 -- Wednesday
AND status = 'scheduled';Option 1 (Create All Bookings Upfront) is the clear winner because:
- Simpler implementation and maintenance
- Better matches actual usage patterns (high certainty, rare changes)
- More reliable for notifications and validation
- Natural historical records
- Database can easily handle the record count
Option 2 would only be better if:
- Bulk updates were very frequent (>50% of the time)
- Database couldn't handle the record volume (not the case at 60 rows)
- You needed to change recurring patterns retroactively (not a requirement)
The only advantage of Option 2 (efficient bulk updates) doesn't outweigh its complexity given your actual requirements.