We have an internal PHP web app that's used for scheduling. We often have multiple users trying to schedule appointments for the same time slot, but we have a hard limit on how many appointments we can have per available time.
We've used PHP to verify that there are available slots before booking, but there's still enough time between PHP checking the table and the insert that overbooking can still happen.
I believe the solution is a MySQL trigger that checks the table before the insert. The problem is that I need MySQL to be able to count the number of records that have the same "schedule_id" and "schedule_user_date" as the record about to be inserted (this will be how many appointments already exist for that time slot).
I have to somehow let the trigger know what the maximum time slot is, which is where I'm stuck, since this can change from client to client.
If you have other suggestions other than a MySQL trigger, I'd like to hear about those as well.
