MySQL - Handle limited number of items available

daymobrew

I have been asked to write a PHP booking form script for 5 events. Each event has a different number of tickets available. A person can request a ticket for between 1 and 5 events.

I was wondering what is a good way to avoid overbooking.

I was thinking of two tables, one with the booking info (name, email, events booked) and another with a single row with the number of tickets left for each event. I would plan to use "Lock Tables" to ensure they kept in sync.

Pseudo code:

lock table tickets_left_table READ, booking_table WRITE
Select * from tickets_left_table
Foreach event requested
  if tickets_left deduct 1 from that event in tickets_left_table
end_foreach
Update tickets_left_table with new values (eg Update event_1=event_1-1 etc)
Add entry (name, email, event_1=1 etc) to booking_table)
Unlock tables
Send an email with list of successfully booked events.

Does that sound reasonable? Is there anything obvious that I should look out for?
Is a read lock sufficient on tickets_left_table?

seamus

Your main issue here is that you may have (for example) 4 tickets left and five people with windows open to book the event. The fifth person will try to book a particular event and will be told rather harshly that "sorry, we're all booked out" even though his screen told him that there were four tickets left when he clicked "submit".

I would do it with two tables:

One table contains the event information - date, location, description, maximum number of tickets, etc.

The second contains bookings for each event. I would view bookings as "confirmed" and "unconfirmed". A "confirmed" booking is one where someone has put their name down, an "unconfirmed" one is where someone has requested tickets but not yet booked them. These bookings put a value in a expiry time column (say five minutes from now) so that if the time has passed, the reservation has expired.

Think about how ticketmaster does it - you say, "I want 3 tickets for this event", the system goes off to check, and if 3 are available, they are "reserved" for you for a set period of time. So you don't have to worry about giving the end user misleading info.


Pseudocode would be:

  select eventid, max_tickets from event where eventid = $eventid
  extract event_details

 lock table booking for read, write

  select sum(tickets_requested) from booking where eventid = $eventid AND (confirmed = 1 OR expiry_time > now)
 extract bookings_details

 if (event.max_tickets - booking.tickets_requested) >= number_of_tickets_requested
 then
   insert unconfirmed booking into booking table
   proceed to confirmation screen
else 
   return error message "insufficient tickets available"

unlock tables

daymobrew

seamus wrote: »

Your main issue here is that you may have (for example) 4 tickets left and five people with windows open to book the event. The fifth person will try to book a particular event and will be told rather harshly that "sorry, we're all booked out" even though his screen told him that there were four tickets left when he clicked "submit".

The system does not have to be that sophisticated - the client actually requested that users can request a ticket for all events and are only given tickets for available events.
When the user completes the (one step) form they do not know which events, if any, have tickets available!

seamus

Grand so.

I still wouldn't necessarily go down the route of storing the number of tickets left, I would still probably go my route. Primarily because you're duplicating information to some degree - the number of tickets left is the same as the total available - the total booked.

If you're only incrementing one value (i.e. the number of tickets booked), then there's less scope for things to go wrong if, for example, a booking fails to get inserted into the bookings table for whatever reason, but the number of tickets available still gets decremented.

daymobrew

seamus wrote: »

I still wouldn't necessarily go down the route of storing the number of tickets left, I would still probably go my route. Primarily because you're duplicating information to some degree - the number of tickets left is the same as the total available - the total booked.

I suppose I can hard code the total available numbers in the script.

daymobrew

Now that I think about it, if I use a separate table with tickets available I can determine how many tickets are available for each event with just one 'select'.

If I don't have this info I (think that I) have to make multiple 'select/count' calls, right?

event_1_tickets_available = select count(event_1) where event_1=1
event_2_tickets_available = select count(event_2) where event_2=1
etc

The site is quite low traffic - the events have between 150 and 800 tickets available.

seamus

If it's low traffic, then worrying about multiple select calls isn't that big a deal

In any case, if it's five events, then you can pull out all of the info in one select using group by so

SELECT count(bookingid), eventid from booking group by event id

This will give you a result like.
count(bookingid)|Eventid
20|1
53|2

Then you loop through the result and determine how many tickets are left for each event.

I guess it depends on whether you're concerned about just getting this done or whether it's something reusable in the future. If this is definitely a one-off, then hardcoding the maximum tickets isn't that big a deal.

If you think you'll get a call this time next year looking to do the same, but this time with 12 events, then you need a more flexible script and database setup.

It's in the latter case that I'd go ahead and store event information - including the maxmimum number of tickets - in a separate table.

daymobrew

seamus wrote: »

I guess it depends on whether you're concerned about just getting this done or whether it's something reusable in the future. If this is definitely a one-off, then hardcoding the maximum tickets isn't that big a deal.

It's a one-off. Thanks for the 'select/group by' code - I am quite the noob when it comes to SQL.

daymobrew

seamus wrote: »

In any case, if it's five events, then you can pull out all of the info in one select using group by so

SELECT count(bookingid), eventid from booking group by event id

This will give you a result like.
count(bookingid)|Eventid
20|1
53|2

How does this query change if I have a column for each event?
bookingid, event_1, event_2, event_3 ?

seamus

It would just be "select sum(event_1), sum(event_2)...."

Which will give one row like the below:

sum(event_1)|sum(event_2)|sum(event_3)
51|38|89


But it should be noted that using one column per event is theoretically bad design, because it means you have to alter the table structure to add more events

But for a one-off hack, you'd be forgiven

daymobrew

seamus wrote: »

But it should be noted that using one column per event is theoretically bad design, because it means you have to alter the table structure to add more events

But for a one-off hack, you'd be forgiven

Noted!
What would the right design be for the scenario where a person can book multiple events?
The caveat is that a person can only book 1 ticket for each of the events.

I am thinking that I will make the bookingid the unique, auto increment index.
For the email I was thinking of making it unique too (I like to put some of the limitations in the db table as well as the code).

seamus

daymobrew wrote: »

What would the right design be for the scenario where a person can book multiple events?

There's years of study in that question
http://en.wikipedia.org/wiki/Database_Normalisation

I don't expect you to read that, but it may be useful to have a run through it to see if it makes sense.

The easiest way to think about it is that within the database, a table describes an object and each row is an instance of that object.

So in your case, you would have two objects - an event and a booking.

So the event table describes the event - what it's called, the date, how many seats in total, etc.

The booking table describes, eh, a booking. So it would say what event the booking is for, who has booked it and so on.

Database normalisation is an exercise in reduction where you basically reduce all of the elements down into tables as much as is possible. So the above example, you don't want to describe the person who has booked something, in the bookings table. You would have another table for people, one row per person. Then the bookings table can refer to that.

There are two or three other tables I can also think of.

Tbh, if your experience of SQL is limited, then I think that going into serious detail here may only serve to confuse the issue. The best way of learning is by making mistakes.
Though you'll find that many developers know about normalisation, we tend to only understand it and apply it properly when we don't do it initially and then we have to come back and change our database structure and code to accomodate updates and alterations in the program. Then when we start building new applications we understand the importance of normalisation.

For your purpose, it may be best to go ahead as you are and you'll probably figure out the answers to any issues on your own.