Library Booking System: Choosing a Data Structure Under Load
A TAFE library runs a room-booking system for 40 study rooms. Students book slots of 30, 60, or 90 minutes. Peak demand is 11 am to 1 pm, when about 300 requests arrive per hour. The current prototype stores bookings in a flat list and checks for clashes by looping over every booking for that room. Staff report the page takes 6-9 seconds to confirm a booking at peak. The system must prevent double-booking, let students cancel up to 15 minutes before a slot, and produce a weekly utilisation report per room. The team can change the storage approach but must keep the existing user interface. A developer suggests moving to a relational database with a unique constraint on room plus start time.
1. Analyse the cause of the slow confirmations and evaluate the developer's proposed database change.
Read the worked answer
Cause: The flat list plus full scan means each clash check grows with the number of bookings per room. At peak, 300 requests per hour each scan a growing list, so response time rises with load. The 6-9 second delay is a scaling symptom, not a hardware fault.
Proposed change: A relational database with a unique constraint on room plus start time gives two benefits. Lookups can use an index rather than a full scan, and the constraint enforces no-double-booking at the data layer. That is stronger than an application check, which can race when two requests arrive together.
Gap: A unique constraint on start time alone does not stop overlaps. A 60-minute booking at 11:00 and a 30-minute booking at 11:30 have different start times but overlap. The design needs either slot-aligned storage, where every booking occupies fixed 30-minute units, or an overlap check inside a transaction.
Verdict: Direction is sound; the constraint as described is insufficient for the overlap requirement.
2. The weekly utilisation report must count only rooms actually used. Explain how the cancellation rule affects the report and recommend how to record cancellations.
Read the worked answer
If cancelled bookings stay in the same table as active ones, a naive count of bookings overstates utilisation. A room cancelled 15 minutes before its slot was not used, so counting it as booked misleads staff planning room numbers.
Recommendation: Record status rather than deleting rows. Each booking carries a status such as active, cancelled, or completed, plus a cancellation timestamp. The report counts completed and active-past bookings as used, and reports cancellations separately. Deleting rows would lose the evidence needed to see whether a room is frequently cancelled, which is useful for decisions about room allocation.
Edge case: A cancellation made after the 15-minute cutoff should be recorded but still counted as used, because the room was held and could not be rebooked. The report should therefore compare the cancellation time to the slot start, not simply trust the status. This keeps the utilisation figure defensible and preserves an audit trail.