Booking conflict resolution in LLD - System Design Exercise

Design: Booking Conflict Resolution System

Design focuses on booking conflict detection and resolution logic, API design, and data model. User interface and payment integration are out of scope.

Functional Requirements

FR1: Allow users to create bookings for resources (e.g., rooms, equipment) with start and end times

FR2: Prevent overlapping bookings for the same resource

FR3: Support concurrent booking requests without conflicts

FR4: Provide immediate feedback if a booking conflicts with existing bookings

FR5: Allow users to view existing bookings for a resource

FR6: Support cancellation and modification of bookings

Non-Functional Requirements

NFR1: Handle up to 1000 concurrent booking requests

NFR2: API response time p99 under 200ms for booking creation

NFR3: System availability 99.9% uptime

NFR4: Data consistency to prevent double bookings

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

❓ Question 6

Key Components

API server to handle booking requests

Database to store bookings and resource info

Locking or transaction mechanism to prevent conflicts

Cache layer for quick availability checks

Notification system for booking confirmations or conflicts

Design Patterns

Optimistic concurrency control

Pessimistic locking

Time interval tree or range index for conflict detection

Eventual consistency with conflict resolution

Retry mechanism for failed booking attempts

Reference Architecture

Client
  |
  v
API Server (REST)
  |
  v
Booking Service --- Cache (Redis) 
  |
  v
Database (PostgreSQL)

Legend:
- Client sends booking requests
- API Server validates and forwards to Booking Service
- Booking Service checks cache and database for conflicts
- Uses transactions to ensure no overlapping bookings
- Cache updated after successful booking

Components

API Server

Node.js/Express or similar

Receive booking requests, validate input, and forward to booking service

Booking Service

Backend service in any language (e.g., Python, Java)

Core logic for conflict detection, booking creation, cancellation, and modification

Cache

Redis

Store recent booking intervals per resource for fast conflict checks

Database

PostgreSQL

Persist booking data with transaction support to ensure consistency

Request Flow

1. Client sends booking request with resource ID, start time, and end time to API Server

2. API Server validates request format and authentication

3. API Server forwards request to Booking Service

4. Booking Service queries Redis cache for existing bookings of the resource in the requested time range

5. If cache miss or uncertain, Booking Service queries PostgreSQL database for overlapping bookings

6. If no conflicts found, Booking Service starts a database transaction

7. Booking Service inserts new booking record and commits transaction

8. Booking Service updates Redis cache with new booking interval

9. Booking Service returns success response to API Server

10. API Server sends confirmation to client

11. If conflict detected, Booking Service returns conflict error to API Server

12. API Server informs client about booking conflict

Database Schema

Entities: - Resource (resource_id PK, name, type, description) - Booking (booking_id PK, resource_id FK, user_id FK, start_time, end_time, status) Relationships: - Resource 1:N Booking (one resource can have many bookings) Constraints: - Booking time intervals must not overlap for the same resource - Use database transaction isolation (e.g., SERIALIZABLE or explicit locking) to enforce this

Scaling Discussion

Bottlenecks

Database write contention when many concurrent bookings target the same resource

Cache consistency issues leading to stale availability data

API server overload with high request volume

Latency increase due to complex conflict detection queries

Solutions

Partition bookings by resource to reduce contention; use row-level locking or optimistic concurrency control

Implement cache invalidation or update strategies after booking changes to keep cache fresh

Scale API servers horizontally behind load balancers

Use efficient range indexing (e.g., GiST indexes in PostgreSQL) to speed up conflict queries

Interview Tips

Time: Spend 10 minutes clarifying requirements and constraints, 20 minutes designing components and data flow, 10 minutes discussing scaling and trade-offs, 5 minutes summarizing

Emphasize importance of preventing overlapping bookings to maintain data integrity

Discuss trade-offs between optimistic and pessimistic concurrency control

Explain how caching improves performance but requires careful consistency management

Highlight use of database transactions and indexing for conflict detection

Mention scaling strategies to handle concurrent booking requests