Implementing a Booking and Reservation System on a Website
A booking system is managing time slots that cannot be sold twice. A clinic, barber shop, coworking space, equipment rental, restaurant — everywhere the same logic: a resource (specialist, table, meeting room, car) is available at a specific time, and that slot must be occupied by exactly one booking. Technically, the task is similar to inventory tracking, but with a time dimension instead of quantity.
Conceptual model
Four entities:
Resource — what is being booked (specialist, office, inventory item) Schedule — working schedule of the resource (business hours, days off, exceptions) Slot — time availability window (can be generated from schedule or set explicitly) Booking — reservation of a slot for a specific customer
Data schema
CREATE TABLE bookable_resources (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
type VARCHAR(50) NOT NULL, -- staff | room | equipment | table
capacity INTEGER NOT NULL DEFAULT 1, -- for group bookings
is_active BOOLEAN NOT NULL DEFAULT true,
meta JSONB NOT NULL DEFAULT '{}'
);
CREATE TABLE resource_schedules (
id BIGSERIAL PRIMARY KEY,
resource_id BIGINT NOT NULL REFERENCES bookable_resources(id),
day_of_week SMALLINT, -- 0=Sun, 1=Mon ... 6=Sat; NULL = specific date
date DATE, -- for exceptions to weekly schedule
is_working BOOLEAN NOT NULL DEFAULT true,
opens_at TIME NOT NULL,
closes_at TIME NOT NULL,
slot_duration INTEGER NOT NULL DEFAULT 60 -- minutes
);
CREATE TABLE bookings (
id BIGSERIAL PRIMARY KEY,
resource_id BIGINT NOT NULL REFERENCES bookable_resources(id),
service_id BIGINT REFERENCES services(id),
user_id BIGINT REFERENCES users(id),
client_name VARCHAR(255) NOT NULL,
client_phone VARCHAR(50),
client_email VARCHAR(255),
starts_at TIMESTAMP NOT NULL,
ends_at TIMESTAMP NOT NULL,
status VARCHAR(50) NOT NULL DEFAULT 'confirmed',
-- pending | confirmed | cancelled | no_show | completed
notes TEXT,
cancel_reason TEXT,
reminder_sent BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
-- Prevent overlaps at the database level
EXCLUDE USING gist (
resource_id WITH =,
tsrange(starts_at, ends_at, '[)') WITH &&
) WHERE (status NOT IN ('cancelled', 'no_show'))
);
EXCLUDE USING gist — PostgreSQL constraint for time range intersection. Requires the btree_gist extension. This is the only reliable way to prevent double-booking at the database level.
CREATE EXTENSION IF NOT EXISTS btree_gist;
Generating available slots
Slots are not stored in the database — they are calculated from the schedule minus existing bookings:
class SlotGenerator
{
public function getAvailableSlots(
BookableResource $resource,
int $serviceDurationMinutes,
Carbon $date
): Collection
{
$schedule = $this->getScheduleForDate($resource, $date);
if (!$schedule || !$schedule->is_working) {
return collect();
}
$slots = collect();
$current = $date->copy()->setTimeFromTimeString($schedule->opens_at);
$closes = $date->copy()->setTimeFromTimeString($schedule->closes_at);
$duration = CarbonInterval::minutes($serviceDurationMinutes);
while ($current->copy()->add($duration)->lte($closes)) {
$slots->push($current->copy());
$current->addMinutes($schedule->slot_duration);
}
// Remove slots that overlap with existing bookings
$existingBookings = Booking::where('resource_id', $resource->id)
->whereDate('starts_at', $date)
->whereNotIn('status', ['cancelled', 'no_show'])
->get();
return $slots->filter(function (Carbon $slot) use ($existingBookings, $duration) {
$slotEnd = $slot->copy()->add($duration);
return $existingBookings->every(function (Booking $booking) use ($slot, $slotEnd) {
return $slotEnd->lte($booking->starts_at) || $slot->gte($booking->ends_at);
});
})->values();
}
}
Atomic booking creation
The EXCLUDE USING gist constraint catches concurrent inserts, but you need to handle PostgreSQL exceptions:
class BookingService
{
public function create(array $data): Booking
{
try {
return DB::transaction(function () use ($data) {
$booking = Booking::create([
'resource_id' => $data['resource_id'],
'starts_at' => $data['starts_at'],
'ends_at' => Carbon::parse($data['starts_at'])
->addMinutes($data['duration']),
'client_name' => $data['client_name'],
'client_phone' => $data['client_phone'],
'client_email' => $data['client_email'],
'service_id' => $data['service_id'] ?? null,
'status' => 'confirmed',
]);
BookingConfirmed::dispatch($booking);
return $booking;
});
} catch (QueryException $e) {
// Error code 23P01 — exclusion constraint violation in PostgreSQL
if (str_contains($e->getMessage(), '23P01')) {
throw new SlotAlreadyBookedException($data['starts_at']);
}
throw $e;
}
}
}
Schedule management
Schedule is hierarchical: weekly template is overridden by specific dates (holidays, time off, special hours):
private function getScheduleForDate(BookableResource $resource, Carbon $date): ?ResourceSchedule
{
// First look for a specific date (exception)
$specific = $resource->schedules()
->whereDate('date', $date)
->first();
if ($specific) {
return $specific;
}
// Then — day of week template
return $resource->schedules()
->where('day_of_week', $date->dayOfWeek)
->whereNull('date')
->first();
}
Reminders
Automatic reminders reduce the no-show rate:
// Runs every hour
class SendBookingReminders
{
public function handle(): void
{
$upcoming = Booking::where('status', 'confirmed')
->where('reminder_sent', false)
->whereBetween('starts_at', [
now()->addHours(23),
now()->addHours(25),
])
->get();
foreach ($upcoming as $booking) {
SendBookingReminder::dispatch($booking);
$booking->update(['reminder_sent' => true]);
}
}
}
Reminder — 24 hours before via SMS and email. Can add a second reminder 2 hours before. Channel depends on infrastructure: SMS via SMSC.ru, Twilio; email via Mailgun, SES, SMTP.
Cancellation and rescheduling
Cancellation policy is configurable: free 24+ hours before, penalty or prohibition less than 2 hours before. On cancellation — automatic notification to specialist and customer.
Rescheduling is cancellation + new booking. Important: when rescheduling, the old slot is freed atomically with creating a new one in a single transaction.
Booking widget on the website
Three-step process:
- Service selection — list with duration and price
- Time selection — calendar with available slots (loaded via AJAX)
- Contact information — name, phone, email, notes
Slots are loaded on demand when a date is selected:
GET /api/bookings/availability?resource_id=1&service_id=3&date=2025-04-15
Response — array of available timestamps. Frontend renders slot buttons.
Administrative calendar
For administrator/specialist — schedule view in weekly calendar format. Implemented via FullCalendar (JS library) with custom event source:
GET /api/admin/bookings/calendar?resource_id=1&start=2025-04-14&end=2025-04-21
Returns events in FullCalendar format, including blocks and breaks.
Implementation timeline
- Basic model: resources + schedule + booking + conflict constraint: 4–6 days
- Slot generation + API for widget: 2–3 days
- Booking widget (frontend): 3–4 days
- Reminders + cancellation notifications: 1–2 days
- Administrative calendar: 2–3 days
- Online payment on booking (prepayment or deposit): +3–4 days
Complete system: 2–4 weeks.