Netloft Rehearsal Rooms · Booking system

The booking database, table by table

Six tables hold every booking Netloft takes: who booked, which room, when, what they added and what they paid. Drawn for Joss Tully, who takes the system over in December.

Drawn by
Anneke Visser
Schema
version 14
Checked
Thu 12 Nov 2026
Size
6 tables, 5 links
one-to-many one-to-many one-to-many one-to-many one-to-many bookings and extras: many-to-many, joined through booking_extras one-to-many one-to-many one-to-many one-to-many one-to-many bookings and extras: many-to-many, joined through booking_extras

A note on each table

customers
One row per person who books, not per band. email is unique, so a returning customer is matched on it and never entered twice.
rooms
Five rows today. hourly_rate_cents is the current price. A room is never deleted: is_active hides it from the booking page and keeps its history.
bookings
The center of the diagram. Each row points at one customer and one room. status is held, confirmed, canceled or completed. total_cents is fixed when the booking is made, so a later price change doesn't rewrite the past.
payments
A booking can have several: a deposit, the balance, sometimes a refund as a negative amount. Add up a booking's rows to see what was really paid. provider_ref is the payment provider's ID for the charge. No card details are kept here.
extras
What can be added to a session: a cymbal set, a vocal mic, a recording of the night. price_cents is today's price for one.
booking_extras
The join table between bookings and extras. Its primary key is the pair booking_id and extra_id, so each extra appears once per booking, with a quantity. unit_price_cents copies the price on the day it was booked.

One booking, row by row

Marek's band books Room 3 for Saturday 5 December 2026, 6 pm to 9 pm, with a cymbal set and two vocal mics.

customers
id 212, Marek Dvorak
rooms
id 3, hourly_rate_cents 3000
bookings
id 4071, customer_id 212, room_id 3, status confirmed, total_cents 10500
booking_extras
(4071, 2) cymbal set, 1 at 800
(4071, 5) vocal mic, 2 at 350
payments
deposit, 2500, paid 21 November
balance, 8000, paid 5 December

3 hours × 3000 is 9000. Extras are 800 + 700. That makes 10500, or $105.00, and the two payments add up to the same.

What the lines can't show

  • No double bookings. The database refuses a booking that overlaps another in the same room, unless that one is canceled.
  • Money is whole cents. 10500 means $105.00, with no decimals anywhere.
  • Times are stored in UTC and shown in the studio's local time.
  • Nothing is deleted. Rooms and extras are switched off, and bookings are canceled.

Sample page. The names and numbers are made up.