Reservation logic
How reservation systems prevent double-booking
A reliable reservation system asks the same availability question twice: once when it shows the user which resources are free, and again when it saves the reservation. The second check matters because the answer shown on the screen can become outdated at any moment.
This guide shows how both checks use one overlap rule. A tool such as PHPRunner can apply that rule in the resource-selection logic and again in a server-side event that protects the save.
Quick answer: Check availability twice. Use the overlap query first to show resources that appear free for the requested period. Run the same query again on the server immediately before saving, because another user may have reserved the resource since the screen was loaded. In busy systems, protect the final check and write against simultaneous requests as well.
One rule finds every kind of overlap
There is no need to write a separate test for every possible collision.
A new reservation can begin during an existing one, finish during it, sit completely inside it, or surround it. All four cases satisfy the same condition:
new start < existing end and new end > existing start
The inverse is often easier to visualize. Two periods do not overlap only when the new reservation ends on or before the existing start, or begins on or after the existing end. Everything between those two safe positions is a conflict.
First decide what the boundary means
The comparison operators depend on whether the ending boundary is available to the next customer.
A meeting room reserved from 10:00 to 12:00 may be available for another meeting beginning at 12:00. This is a half-open interval: the start is included, but the end marks the first available moment.
Other reservations require a gap. A rental property may need cleaning time; equipment may need inspection; a technician may need travel time. Model that requirement as a buffer around the reservation instead of hiding it in the interface. The overlap query should compare the effective blocked period, including the buffer.
Date-only bookings need the same decision. A checkout date may be available to the next booking, while a reserved event date may remain occupied through the end of that day.
Availability is calculated for the requested period
A permanent Available field cannot describe a calendar.
A vehicle booked next Tuesday may still be available today. To find choices for a new request, start with the resource list and exclude every resource that has an active reservation overlapping the requested start and end.
Reservation status belongs in that query. A cancelled or rejected request normally should not block a resource, while confirmed reservations—and sometimes pending requests—should. This is a business rule, not a display preference.
An availability screen cannot guarantee the later save
Another user can reserve the same resource after the screen was loaded.
Imagine two users searching at 10:00. Both see the same room as available. The first saves a reservation; the second submits a few seconds later. If the application trusts the earlier search result, both reservations may be accepted.
Recheck the overlap rule on the server immediately before inserting or updating the reservation. In a busy system, the check and write may also need to run inside a transaction that prevents competing reservations for the same resource from passing simultaneously. The exact locking or constraint strategy depends on the database and booking model, but the browser result alone must never be the final authority.
Check 1: return the resources available for the selected period
The first query improves the form by removing choices that already conflict.
This PHPRunner Database API helper assumes two tables named Resources and Reservations. Add it to a custom PHP file and call it from the server-side code that supplies the resource choices after the user selects StartTime and EndTime.
function findAvailableResources($requestedStart, $requestedEnd)
{
$sql = DB::PrepareSQL(
"SELECT r.ResourceID, r.ResourceName
FROM Resources r
WHERE NOT EXISTS (
SELECT 1
FROM Reservations b
WHERE b.ResourceID = r.ResourceID
AND b.Status IN ('Pending', 'Confirmed')
AND b.StartTime < ':1'
AND b.EndTime > ':2'
)
ORDER BY r.ResourceName",
$requestedEnd,
$requestedStart
);
return DB::Query($sql);
}DB::PrepareSQL() inserts the selected values safely. The returned query result contains only resources with no active reservation overlapping the requested period. Use fetchAssoc() to read the rows when building the lookup response.
Check 2: reject a conflict immediately before saving
The second query protects the data when the earlier availability result is stale.
Open Events → Reservations → Add page → Before record added and add the following code. PHPRunner provides the submitted field values through the case-sensitive $values array.
$sql = DB::PrepareSQL(
"SELECT COUNT(*) AS conflicts
FROM Reservations
WHERE ResourceID = :1
AND Status IN ('Pending', 'Confirmed')
AND StartTime < ':2'
AND EndTime > ':3'",
$values["ResourceID"],
$values["EndTime"],
$values["StartTime"]
);
$rs = DB::Query($sql);
if ((int)$rs->value("conflicts") > 0) {
$message = "This resource was just reserved for that period. "
. "Please choose another resource or time.";
return false;
}
return true;Returning false from Before record added cancels the insert and displays the message. Apply the same rule on the Edit page in Before record updated, excluding the reservation currently being edited from the conflict query.
This example closes the ordinary gap between displaying availability and saving. A high-concurrency system may still require database-specific transaction, locking, or constraint logic so two checks cannot pass simultaneously before either insert becomes visible.
Final recommendation
Define the time model before designing the booking screen.
Decide whether end times are reusable, which statuses block availability, and whether preparation or travel buffers apply. Then use one overlap rule for both the availability search and the final server-side validation.