Multi-Resource Booking System Design: Routing, Conflicts, and Availability Merging

Single-resource booking is a solved problem. You have one calendar, one set of availability rules, and one conflict to prevent. Multi-resource booking is a different class of problem. When a booking requires two staff members, a room, and a piece of equipment — each with independent availability — the system must compute the intersection of multiple availability sets and prevent conflicts across all of them simultaneously.

This is where most off-the-shelf scheduling tools stop working cleanly, and where custom systems earn their cost.

What Makes Multi-Resource Booking System Design Different

In a single-resource system, availability computation is: given this resource, what slots are open? In a multi-resource system, availability computation is: given this set of resources, what slots are open for all of them simultaneously?

The second question requires merging availability across resources and returning only the intersection. This sounds straightforward until you account for:

• Resources with different working hours in different time zones
• Resources with individual blocked periods that do not overlap
• Bookings that require one resource from a pool (any available staff member) vs. a specific named resource
• Buffer time requirements that differ per resource type
• Partial availability — a slot where some but not all required resources are free

Each of these adds complexity to the availability computation and to the conflict prevention logic at write time.

Data Model for Multi-Resource Booking

The data model needs to represent the relationship between bookings and resources explicitly. A booking does not just belong to a single resource — it requires a set of resources, each of which is blocked for the booking's duration.

A normalized model:

resources
  id, name, resource_type, timezone, ...

availability_rules
  id, resource_id, day_of_week, start_time, end_time, ...

blocked_periods
  id, resource_id, start_at, end_at, reason, ...

bookings
  id, service_id, customer_id, start_at, end_at, status, ...

booking_resources
  booking_id, resource_id

The booking_resources join table records which resources are committed to each booking.

This enables:

• Querying all bookings for a specific resource (to compute availability)
• Preventing double-booking at the resource level (conflict check against booking_resources)
• Supporting bookings that require different resource combinations per service type

Availability Merging: Computing the Intersection

To compute available slots for a multi-resource booking, you need to find time windows where all required resources are simultaneously free.

The algorithm:

1. For each required resource, compute its individual free windows within the query range (availability rules minus blocked periods minus existing bookings).
2. Compute the intersection of all individual free window sets.
3. Within the intersection, generate slots of the requested duration that fit.

In practice, this is a set intersection problem on time intervals. Efficient implementations represent free windows as sorted lists of (start, end) pairs and compute the intersection with a merge-step algorithm — iterate through all lists simultaneously, advancing whichever interval ends first.

For small resource counts (2–5), a naive nested approach works. For large resource pools, optimize with indexed availability lookups.

The "Any Available" vs. "Specific Resource" Distinction

Many booking scenarios require one resource from a pool (the next available staff member) rather than a specific named resource. These require different computation:

• Specific resource: compute availability for the named resource, find open slots.
• Any from pool: compute availability for each resource in the pool, union the results, and when a booking is placed, assign it to a specific resource within the pool (earliest available, round-robin, or by workload).

The assignment decision at booking time can introduce conflicts if two concurrent bookings both "see" the same pool member as available. This requires the same concurrency control at write time — locking the assigned resource record, not just the pool — as single-resource booking conflict prevention.

Conflict Prevention Across Multiple Resources

Preventing conflicts in a multi-resource booking is more complex than in the single-resource case because you need to atomically check and lock multiple resources. A booking that requires a staff member and a room must check that both are free and lock both before confirming.

In PostgreSQL, this means including all required resource records in the SELECT FOR UPDATE query:

BEGIN;
SELECT id FROM resources
  WHERE id = ANY($resource_ids)
  ORDER BY id  -- consistent ordering prevents deadlocks
  FOR UPDATE;
-- Check availability for each locked resource
-- Write booking_resources entries if all are free
COMMIT;

The ORDER BY id is critical. Locking multiple rows in a consistent order prevents deadlocks between concurrent transactions that might otherwise each acquire one lock and wait for the other.

If any resource is not available within the transaction, roll back and return a conflict response. Do not confirm a partial booking.

Routing Logic: Assigning Resources Automatically

When a service type requires "any staff member with certification X and any room of type Y," the system needs routing logic to translate abstract requirements into specific resource assignments at booking time.

Routing strategies:

• First available: Assign the earliest-available qualified resource. Simple and predictable.
• Round-robin: Distribute bookings evenly across qualified resources. Requires tracking assignment counts.
• Least-loaded: Assign to the resource with the fewest upcoming bookings. Better for workload balance but more expensive to compute.
• Preference-based: Honor customer preferences (e.g., "same staff member as last time") before falling back to automatic assignment.

Store the routing rules per service type, not hardcoded in application logic. This makes it possible to change routing behavior through configuration rather than deployment.

Handling Partial Availability

A frequent edge case: a 3-hour booking requires a room for all 3 hours and a staff member for only the first hour. The staff member's availability constraint applies only to the first hour; the room's constraint applies to all three.

Model this as time-bounded resource requirements on the booking:

booking_resource_requirements
  booking_id, resource_id, required_from, required_until

Availability computation and conflict prevention operate on the per-resource time window rather than the full booking duration. This requires more complex queries but correctly represents the actual resource usage.

Testing Multi-Resource Booking Logic

The concurrency and routing logic in a multi-resource system requires explicit testing:

• Two concurrent bookings for the same resource combination should produce exactly one success
• Bookings that partially overlap (one resource in common, different times) should not conflict
• Pool routing should not assign the same resource to two concurrent bookings
• Cancelling a booking should release all of its reserved resources immediately

These tests are worth writing as integration tests against a real database, not unit tests against mocked dependencies — the behavior you are testing depends on database-level locking behavior.

When multi-resource scheduling is central to your product, the design decisions you make early determine how much of the system you will need to rewrite as requirements evolve. Clixo designs and builds custom booking systems for teams that need this done right the first time.