Booking systems look simple from the outside. A customer picks an item, chooses dates and pays. Under the hood, though, rental software has to solve one of the classic problems in database design: making sure the same physical thing is never promised to two people at the same time.
In this article we will design the core data model for an equipment rental system, look at how to prevent double bookings at the database level, and cover a few patterns that make the system easier to scale. The examples use PostgreSQL, but the ideas apply to most relational databases.
Start With the Domain
Before writing any SQL, it helps to list the real-world things the system needs to represent:
Products: a type of equipment, such as "6m scissor lift" or "20kVA generator"
Assets: individual physical units of a product, each with a serial number
Customers: people or companies renting equipment
Bookings: a reservation for one or more items over a time period
Booking items: the specific assets attached to a booking
Maintenance windows: periods when an asset is unavailable
Locations: branches or depots where assets live
The most common beginner mistake is to merge products and assets. If you only store "we have 5 generators," you cannot track which generator went to which customer, which one is due for service, or which one came back damaged.
The Core Tables
Here is a simplified schema:
CREATE TABLE product (
id serial PRIMARY KEY,
name text NOT NULL,
category text NOT NULL,
daily_rate numeric(10,2) NOT NULL
);
CREATE TABLE asset (
id serial PRIMARY KEY,
product_id int NOT NULL REFERENCES product(id),
serial_no text UNIQUE NOT NULL,
location_id int NOT NULL,
status text NOT NULL DEFAULT 'active'
);
CREATE TABLE booking (
id serial PRIMARY KEY,
customer_id int NOT NULL,
period tstzrange NOT NULL,
status text NOT NULL DEFAULT 'confirmed'
);
CREATE TABLE booking_item (
booking_id int NOT NULL REFERENCES booking(id),
asset_id int NOT NULL REFERENCES asset(id),
period tstzrange NOT NULL,
PRIMARY KEY (booking_id, asset_id)
);
Notice that the rental period is stored as a tstzrange, a timestamp range type, rather than two separate columns. That choice unlocks one of PostgreSQL's most useful features for booking systems.
Preventing Double Bookings in the Database
Many applications check availability in code: query for overlapping bookings, and if none exist, insert the new one. The problem is concurrency. Two requests can run the check at the same moment, both see the asset as free, and both insert. Now you have a double booking.
The robust solution is to let the database enforce the rule. PostgreSQL's range types support exclusion constraints, which guarantee that no two rows overlap on a given column:
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE booking_item
ADD CONSTRAINT no_overlapping_assets
EXCLUDE USING gist (
asset_id WITH =,
period WITH &&
);
This says: for the same asset_id, no two period ranges may overlap (&& is the overlap operator). If two transactions try to book the same asset for overlapping times, one will fail with a constraint violation, no matter how the application code is written.
If you are weighing database options for this kind of workload, this comparison explaining what PostgreSQL is and why it can be better than MySQL gives useful background. Native range types and exclusion constraints are a big reason PostgreSQL is popular for scheduling systems.
Handling Buffers Between Rentals
Real rental yards need time between bookings to clean, inspect and transport equipment. One approach is to store the operational period, including buffer time, in booking_item.period, while showing the customer-facing dates separately on the booking. That way the exclusion constraint automatically protects the buffer as well.
Maintenance as a First-Class Concept
Maintenance windows should block availability just like bookings. A neat pattern is to store them in their own table and query availability across both:
CREATE TABLE maintenance (
id serial PRIMARY KEY,
asset_id int NOT NULL REFERENCES asset(id),
period tstzrange NOT NULL,
reason text
);
An availability query then becomes "find assets of product X at location Y where no booking item or maintenance row overlaps the requested period."
SELECT a.id
FROM asset a
WHERE a.product_id = $1
AND a.location_id = $2
AND a.status = 'active'
AND NOT EXISTS (
SELECT 1 FROM booking_item bi
WHERE bi.asset_id = a.id AND bi.period && $3
)
AND NOT EXISTS (
SELECT 1 FROM maintenance m
WHERE m.asset_id = a.id AND m.period && $3
);
A GiST index on period in both tables keeps this fast even with large histories.
Product-Level vs Asset-Level Booking
Customers usually book a product ("two scissor lifts"), not a specific serial number. There are two common strategies:
Assign assets at booking time. The system picks available assets immediately. Simple and safe, but less flexible if plans change.
Assign assets at dispatch. The booking reserves capacity at product level, and specific units are assigned when the equipment leaves the yard. More flexible, but you need a capacity check that counts available units per period.
Many systems use a hybrid: reserve capacity at booking, then lock specific assets a day or two before dispatch.
Time Zones and Edge Cases
Booking systems are full of edge cases that bite later:
Store timestamps with time zones (timestamptz) and convert for display.
Decide on inclusive or exclusive bounds. PostgreSQL defaults to [), meaning start is included and end is excluded, which neatly allows back-to-back bookings.
Handle daylight saving changes if you bill hourly.
Soft-delete cancelled bookings rather than removing rows, so history and audit trails stay intact. Exclude cancelled rows from the constraint using a partial WHERE clause.
Build or Buy?
Designing this schema is a great learning project, and for niche requirements a custom build can make sense. But a production rental platform also needs pricing rules, deposits, contracts, invoicing, maintenance scheduling, reporting, user permissions and integrations. That is a lot of surface area to build and maintain.
For many rental businesses, adopting a ready-made rental management system and integrating it with their website and accounting tools is faster and cheaper than writing everything from scratch. Developers can then focus on custom integrations instead of reinventing availability logic.
Wrapping Up
The heart of any rental system is a simple promise: one asset, one customer, one time slot. Modelling assets separately from products, storing periods as ranges and letting the database enforce non-overlap with exclusion constraints gives you a foundation that stays correct under real-world concurrency. Everything else, from pricing to reporting, is much easier to build on top of a data model you can trust.
