Room and equipment resource booking in a TMS

Featured image with a periwinkle pastel background and a soft blob pattern, showing the post title "Room and Equipment Resource Booking in a TMS"

Room and equipment resource booking in a TMS

Resource booking in a training management system means reserving rooms, equipment and kits for specific time windows, and refusing any request that would use the same thing twice. Four rules cover most of the work: a half-open time range per booking, a database-enforced no-overlap rule for single items, a counting check for pooled equipment, and expiring holds for tentative requests.

The booking-software guides we reviewed are vendor pages that stay at the feature level. This guide covers the mechanism: schema, queries and failure cases, with PostgreSQL code that was run on PostgreSQL 16.15 for this post and results reported as they came out.

Key takeaways

  • Sort every bookable thing into one of four types, because each needs a different conflict rule.
  • An exclusion constraint stops double booking for single items, even when two planners save at the same moment.
  • A constraint cannot count, so pooled equipment needs a peak-use query run under a lock.
  • Holds are real bookings with an expiry, swept inside the booking transaction.
  • A room’s seat count is not its legal capacity. Book to the smaller number.

What does resource booking need to track in a TMS?

Track four kinds of bookable thing, because each has a different conflict rule: rooms and single items (one user at a time), pools (a count of identical items), kits (a bundle that travels together) and consumables (stock, not time). One rule for all four is the usual cause of overbooking and phantom shortages.

Type Example What you store Conflict rule
Room or single unit Room A, one projector Bookings with a time range No two active bookings overlap. Enforce with an exclusion constraint.
Pool 10 identical laptops Total quantity, bookings with quantity Peak simultaneous use never exceeds the total. Enforce with a counting check under a lock.
Kit Lab kit: projector plus laptop cart Kit and its member items Kit and every member are free for the window. Book all rows in one statement.
Consumable Printed workbooks, lab supplies Stock on hand, reservation per session Stock covers confirmed demand. No time range.

Pool units that are interchangeable, such as identical loaner laptops. Track a unit individually when it differs in ways that matter, such as a certified instrument. Give consumables a stock ledger of movements (received, reserved, used) rather than a time range, which would only create false conflicts. Our TMS data model guide covers how rooms and equipment link to sessions, so this post starts from a resource table.

How do you stop double booking with an exclusion constraint?

Put the rule in the database. An exclusion constraint on the resource and the time range rejects any second active booking that overlaps the first, whichever screen, import or API wrote it. PostgreSQL needs the btree_gist extension to combine an equality test on the resource with an overlap test on the range.

Store each booking as a half-open range, start included and end excluded, so 09:00 to 12:00 does not collide with 12:00 to 13:00. PostgreSQL’s range types documentation says the two-argument constructor builds this shape, and && tests overlap. Use timestamptz and convert to a venue time zone only for display.

The bare constraint is in our scheduling logic guide. This version adds a status, buffers and a hold expiry, and applies only to active rows through a WHERE predicate, which the CREATE TABLE documentation describes as a constraint on a subset of the table.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE FUNCTION add_minutes(t timestamptz, m int) RETURNS timestamptz
  LANGUAGE sql IMMUTABLE AS $$ SELECT t + m * interval '1 minute' $$;

CREATE TABLE resource (
  resource_id int PRIMARY KEY,
  name        text NOT NULL,
  kind        text NOT NULL CHECK (kind IN ('room','unit','kit'))
);

CREATE TABLE booking (
  booking_id      bigserial PRIMARY KEY,
  resource_id     int NOT NULL REFERENCES resource,
  session_id      int NOT NULL,
  starts_at       timestamptz NOT NULL,
  ends_at         timestamptz NOT NULL,
  setup_min       int NOT NULL DEFAULT 0 CHECK (setup_min >= 0),
  teardown_min    int NOT NULL DEFAULT 0 CHECK (teardown_min >= 0),
  status          text NOT NULL DEFAULT 'confirmed'
                  CHECK (status IN ('hold','confirmed','released')),
  hold_expires_at timestamptz,
  blocked         tstzrange GENERATED ALWAYS AS (tstzrange(
                    add_minutes(starts_at, -setup_min),
                    add_minutes(ends_at, teardown_min))) STORED,
  CHECK (ends_at > starts_at),
  CHECK (status <> 'hold' OR hold_expires_at IS NOT NULL),
  EXCLUDE USING gist (resource_id WITH =, blocked WITH &&)
    WHERE (status IN ('hold','confirmed'))
);

On a test database, a 09:00 to 12:00 booking and a 12:00 to 13:00 booking in the same room were both accepted. A 12:30 to 14:00 booking in that room failed with SQLSTATE 23P01 (exclusion_violation), while the same hours in another room succeeded.

In the race case, one session inserted a booking and held its transaction open for three seconds. A second session inserted an overlapping booking for the same room. The second insert waited until the first committed, then failed with 23P01. Catch that error, say the room was just taken, and rerun the availability search.

How do you add setup and teardown buffers?

Store setup and teardown minutes on each booking and exclude on the widened range: setup before the start, teardown after the end. Learners still see the real session times; the blocked range is what other bookings must avoid. A generated column keeps the two in step.

PostgreSQL 16 refused a generated column built on to_timestamp(extract(epoch ...)) with “generation expression is not immutable”, and the CREATE TABLE documentation requires immutable functions there. The wrapper above is declared immutable, which is safe only because it adds whole minutes, never days or months.

In the test, a 09:00 to 11:00 booking with 15 minutes of setup and teardown blocked 08:45 to 11:15. A follow-on session at 11:15 with the same buffers was rejected; one at 11:30 was accepted, because the first teardown and the second setup run in sequence. If two crews work in parallel, use the larger buffer instead of the sum.

Pro tip: Copy buffer minutes onto the booking. Defaults may live on the room or course, but write the values onto each booking when it is created. If someone later raises a room default from 15 to 30 minutes, old bookings keep the buffers they were accepted with, so none silently becomes a conflict.

How do you book equipment from a pool with a quantity?

An exclusion constraint cannot count, so check the peak simultaneous use inside the requested window against the pool total, under a lock so two requests cannot both pass. Do not sum overlapping bookings: that overcounts when two earlier bookings overlap the request but not each other.

Take a pool of 10 laptops (illustrative numbers). Session A holds 6 from 09:00 to 11:00 and session B holds 6 from 11:00 to 13:00. A request for 4 from 10:00 to 12:00 overlaps both, so the naive sum is 12 and the request looks impossible. Peak use never exceeds 6, so 4 more fit. A request for 5 does not.

CREATE TABLE equipment_pool (
  pool_id   int PRIMARY KEY,
  name      text NOT NULL,
  total_qty int NOT NULL CHECK (total_qty >= 0)
);

CREATE TABLE pool_booking (
  pool_booking_id bigserial PRIMARY KEY,
  pool_id         int NOT NULL REFERENCES equipment_pool,
  session_id      int NOT NULL,
  qty             int NOT NULL CHECK (qty > 0),
  blocked         tstzrange NOT NULL,
  status          text NOT NULL DEFAULT 'confirmed'
                  CHECK (status IN ('hold','confirmed','released')),
  hold_expires_at timestamptz,
  CHECK (status <> 'hold' OR hold_expires_at IS NOT NULL)
);

-- Use only rises when a booking starts, so test the window start
-- plus every start that falls inside the window.
CREATE FUNCTION pool_peak_use(p_pool int, p_window tstzrange) RETURNS int
LANGUAGE sql STABLE AS $$
  WITH live AS (
    SELECT qty, blocked FROM pool_booking
    WHERE pool_id = p_pool AND blocked && p_window
      AND (status = 'confirmed' OR (status = 'hold' AND hold_expires_at > now()))
  ), instants AS (
    SELECT lower(p_window) AS t
    UNION SELECT lower(blocked) FROM live WHERE lower(blocked) > lower(p_window)
  )
  SELECT coalesce(max(used), 0)::int FROM (
    SELECT i.t, (SELECT coalesce(sum(l.qty), 0) FROM live l WHERE l.blocked @> i.t) AS used
    FROM instants i) x
$$;

CREATE FUNCTION book_pool(p_pool int, p_session int, p_qty int, p_window tstzrange)
RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE total int; new_id bigint;
BEGIN
  -- the lock serializes writers per pool
  SELECT total_qty INTO total FROM equipment_pool WHERE pool_id = p_pool FOR UPDATE;
  IF pool_peak_use(p_pool, p_window) + p_qty > total THEN
    RAISE EXCEPTION 'pool % has fewer than % free for that window', p_pool, p_qty;
  END IF;
  INSERT INTO pool_booking(pool_id, session_id, qty, blocked)
    VALUES (p_pool, p_session, p_qty, p_window) RETURNING pool_booking_id INTO new_id;
  RETURN new_id;
END $$;

On that example, pool_peak_use returned 6, the request for 5 was rejected and the request for 4 was accepted. The lock is the part people skip. As the PostgreSQL locking documentation describes it, FOR UPDATE blocks other transactions that try to update or lock the same row until the current one ends, so requests for one pool queue up. Two sessions each asked for 6 of 10 laptops for the same window. The locked function accepted one and rejected the other. With FOR UPDATE removed, both succeeded: 12 laptops booked from a pool of 10.

How do you book a kit that travels as one unit?

Give the kit its own resource row, list its members, and book the kit and every member in one statement. Each row hits the same exclusion constraint, so if any member is busy the whole statement fails and nothing is written. Once the kit is booked, its members cannot be booked alone.

CREATE TABLE kit_member (
  kit_id    int NOT NULL REFERENCES resource,
  member_id int NOT NULL REFERENCES resource,
  PRIMARY KEY (kit_id, member_id)
);

CREATE FUNCTION book_kit(p_kit int, p_session int, p_start timestamptz, p_end timestamptz,
                         p_setup int DEFAULT 0, p_teardown int DEFAULT 0) RETURNS int
LANGUAGE sql AS $$
  WITH ins AS (
    INSERT INTO booking(resource_id, session_id, starts_at, ends_at, setup_min, teardown_min)
    SELECT r.id, p_session, p_start, p_end, p_setup, p_teardown
    FROM (SELECT p_kit AS id UNION ALL
          SELECT member_id FROM kit_member WHERE kit_id = p_kit) r
    RETURNING 1)
  SELECT count(*)::int FROM ins
$$;

With a projector booked alone from 14:00 to 16:00, booking a kit containing it from 15:00 to 17:00 failed and left zero rows. A kit booking from 17:00 to 19:00 with 15-minute buffers inserted three rows (the kit and two members), and the projector alone was then rejected inside that window. Kits that move between sites need longer buffers to cover transit, which this code leaves to you.

How should holds and releases work for tentative bookings?

A hold is a real booking with status hold and an expiry time. It blocks the slot like a confirmed booking until someone confirms it or it is released. The constraint only sees the status column, so an expired hold keeps blocking until something marks it released.

Status Blocks the slot? Leaves this status when
hold Yes A planner confirms it, or the hold expires and is released
confirmed Yes The booking is cancelled, which sets it to released
released No Never; the row stays as history

In the test, a hold that had expired ten minutes earlier still rejected an overlapping booking, so a nightly job alone is not enough. Sweeping inside the booking transaction worked, and the stale hold became released:

BEGIN;
UPDATE booking SET status = 'released'
 WHERE resource_id = 3 AND status = 'hold' AND hold_expires_at <= now()
   AND blocked && tstzrange('2026-12-01 10:00+00', '2026-12-01 11:00+00');
INSERT INTO booking(resource_id, session_id, starts_at, ends_at)
  VALUES (3, 201, '2026-12-01 10:00+00', '2026-12-01 11:00+00');
COMMIT;

-- Confirming: affects 0 rows if the hold was already released
UPDATE booking SET status = 'confirmed', hold_expires_at = NULL
 WHERE booking_id = 42 AND status = 'hold';

Confirming the swept hold afterward changed zero rows, so the planner’s screen must handle “your hold was released”. Confirming a live hold changed one row. The confirm above ignores the expiry time, so a hold past expiry can still be confirmed if nobody took the slot. Add AND hold_expires_at > now() for a hard cutoff.

Availability reads should ignore expired holds without waiting for a sweep:

SELECT r.resource_id, r.name
FROM resource r
WHERE r.kind = 'room'
  AND NOT EXISTS (
    SELECT 1 FROM booking b
    WHERE b.resource_id = r.resource_id
      AND b.blocked && tstzrange('2026-11-02 12:00+00', '2026-11-02 13:00+00')
      AND (b.status = 'confirmed' OR (b.status = 'hold' AND b.hold_expires_at > now())))
ORDER BY r.resource_id;

Pro tip: Log every release. Write an event row saying which hold, which resource and when. A planner who finds a vanished booking can then see “released at 14:05, hold expired” instead of suspecting a bug, and you learn how many holds go unused.

Why can a room’s seat count exceed its legal capacity?

A seat count says how many chairs fit in a layout. The legal limit is set by the building or fire authority, depends on how the space is used, and can be lower, so book against the smaller number. This is engineering context, not legal advice: have your facilities lead confirm local limits.

As one example of how limits are derived, Washington State’s fire code (WAC 51-54A-1004, effective March 2024) computes occupant load by dividing floor area by a table factor, such as 20 net square feet per person for classroom areas and 15 for assembly areas with tables and chairs. The fire code official can approve a different number. Other jurisdictions differ, so use your own authority’s figure.

Model three numbers per room: seats per layout (classroom, cabaret, theater), the posted limit, and your commercial cap per session. Effective capacity is LEAST(layout_seats, posted_limit, commercial_cap). With illustrative layouts of 36, 24 and 60 seats, a posted limit of 40 and a cap of 45, a test query returned 36, 24 and 40. Check it when a session is placed and whenever its layout changes.

Which resource booking rules belong in the database?

Put in the database the rules that must never break: no overlap for a single item, valid ranges, valid statuses and non-negative quantities. Keep in the application anything with exceptions: preference warnings, approvals, opening hours, who may book what, and notifications.

A maintenance window can be another booking row against the same resource, so the same constraint protects it. Not covered by the tests: recurring series, daylight saving transitions, other PostgreSQL versions, other databases and load.

What should you do next?

List your resources and sort each into room or unit, pool, kit or consumable. Then copy the schema into a scratch database and replay six checks: touching ranges, a true overlap, a buffer clash, a pool request that fits only by peak use, a kit with one busy member, and an expired hold.

Put the same cases to any platform you are evaluating. SimpliTrain, Mundrisoft’s training management product, describes one scheduling workflow for ILT sessions and lists room and resource management among its features on its homepage. The two pages we checked do not say how equipment quantities or tentative holds work, so ask for those specifically in a demo.

Frequently asked questions

What is resource booking in a training management system?

It is the part of a TMS that reserves rooms, equipment and kits for session times and rejects conflicting requests. A good one handles single items, pooled quantities, kits, buffers and tentative holds, and enforces the rules in the database so no screen or import can bypass them.

How do you prevent double booking in a database?

Use a constraint, not only an application check. In PostgreSQL, an exclusion constraint on the resource and a time range rejects overlapping active bookings even when two users save at once. Other databases need an equivalent, such as a row lock on the resource, so verify it with a concurrent test.

Do you need btree_gist for a booking constraint in PostgreSQL?

Yes, when you combine an equality test on a plain column, such as the room, with a range overlap in one GiST exclusion constraint. In the test, creating that constraint without the extension failed because integer has no default operator class for gist. Run CREATE EXTENSION btree_gist first.

What happens to a booking when its session moves?

Update the booking’s times in the same transaction as the session. The constraint checks the new range and the generated blocked range is recalculated. In the test, moving a booking into an occupied slot failed with an exclusion violation, and moving it to a free slot succeeded.

How long should a hold last?

There is no standard. Base it on your sales cycle: long enough for a client to get a purchase order approved, short enough that planners cannot park rooms indefinitely. Review how many holds expire unused and shorten the window if most do. Notify the owner before release.

Alert: mundrisoftcareers.com is not affiliated with Mundrisoft Solutions. Beware of fake job offers and trust only our official recruitment channels.