Duplicate Bookings at Riverdale Sports Centre
Riverdale Sports Centre takes about 1,200 badminton court bookings a month. Customers phone in or fill in a paper form at the counter, and a part-time clerk types each booking into one shared spreadsheet. Since the centre expanded to three halls, the manager has received a stream of complaints: two customers arrived for the same court at the same time; a booking was accepted for 31 June; a confirmation SMS went to a seven-digit phone number; and one booking was made for a Monday when the halls are closed for maintenance. The clerk insists the spreadsheet is fine because "Excel already checks the dates". The manager wants to add data validation rules to every column and believes this will stop all the errors. The centre has no IT staff and about HK$8,000 to spend on improvements this year.
1. For each of the four complaints, decide whether it is primarily a validation or a verification failure (or neither), name a specific check that would catch it, and explain why the check must run at the point of entry.
Read the worked answer
Sort the complaints by what each kind of check can actually prove.
- Same court, same time: this is neither simple validation nor verification. Each record is internally plausible; only a comparison against all existing records detects the clash. It is an integrity/uniqueness failure. The control is a composite key (court + date + start time) checked against the booking table before saving.
- 31 June: a validation failure. A validity check, or better a date picker that only offers real dates, rejects it. Formatting a cell as a date is not a check - 31 June can still be typed.
- Seven-digit phone number: a validation failure on format/length. An input mask or an 8-digit length check catches it. It would not catch a valid but wrong number, such as 9123 4568 typed instead of 9123 4567 - that needs verification.
- Booking on a closed Monday: a cross-field or reference check against the opening-hours table, not merely "is this a real date". Excel's date validation would happily accept it.
Point of entry matters because the clerk can correct the booking while the customer is still at the counter. A weekly clean-up means phoning customers back, and by then the hall may already have been double-sold.
2. Explain why validation rules alone cannot stop double bookings, and design a control that would - including what must happen when two clerks save at the same moment.
Read the worked answer
Validation is a set of rules applied to one field or one record at a time: right type, right length, right range. A double booking passes every one of those rules, because both records are internally valid. Detecting it needs a comparison across records, which is an integrity constraint, not validation.
A workable control:
- Give every booking a unique booking ID as the primary key.
- Before saving, look up the same court, date and start time. If a match exists, show the existing booking and refuse the save.
- Make the check and the insert one transaction, so two clerks cannot both pass the check and then both insert. A shared spreadsheet cannot do this: two people can open it and the last save wins. Moving the table into a small database with a unique index on (court, date, start_time) lets the database itself enforce the rule even under concurrent entry.
- Run a daily exception report listing any duplicates created before the change, and reconcile it against payment records.
If the centre keeps the spreadsheet, the fallback is organisational: one person enters all bookings, or a check-out/check-in routine. Both are fragile, which is why the database route is the real fix.
3. The centre cannot afford to type all 1,200 monthly bookings twice. Recommend a verification method and justify it on cost and benefit, then state the residual risk.
Read the worked answer
Full double entry would catch most keying errors, but it roughly doubles entry time for 1,200 bookings handled by one part-time clerk. The cost is staff hours and queues at the counter; the benefit is catching errors before the customer leaves.
A better balance:
- Read-back plus confirmation: the clerk reads back court, date, time and phone number, and the system sends an SMS or email the customer can check. This is verification by the person who owns the data, and it also catches wrong-but-valid phone numbers.
- On-screen summary before saving, showing the proposed booking against existing bookings for that court and day.
- Sampling: the manager re-checks 20 randomly selected bookings per week against the paper forms and call logs. If the error rate stays under about 1%, keep sampling rather than double-entering.
Residual risk remains: a customer can confirm a wrong detail, and sampling misses errors between samples. So keep an audit trail of who entered or changed each booking, daily backups, and a written correction procedure. Verification improves accuracy; it does not guarantee correctness.