I'm not intimately familiar with MySQL, and have done some reading in the documentation but am not confident enough to proceed until I have a little help first!

Let me start by stating that I have the ability to change the design a bit if appropriate


Customers can buy a ticket. Each ticket can be for one or many entries (e.g. one ticket can be for 4 adults and 3 children).

At current this is modelled from the source data as a single ticket entry, with a field containing the number of each type of entry

ticket (ticket_id, num_adults, num_children)

Now here comes the fun part: not all the entries will be arriving together.

In the example above (4A, 3C) that could be two separate parties who might not arrive at the same time. Both parties have the same ticket (and therefore ticket_id).

To top this off, there is more than one entry to most venues so therefore I don't have a single point to control data recording (distributed system).

So, how should I model and/or handle this situation?

The simplest solution it would seem, is to lock the row/table, update the number of adults or kids remaining (in a separate field, perhaps), and done.

Second simplest would be to have a separate table to store the usage, then calculate remaining tickets at a given time. Not sure how I can ensure that two people enter the same details twice and cause an overuse on a ticket (we can't have people scamming the system, can we!)?

Another option would be to create a new table with a single entry per adult and child (these would be created/calculated ahead of time when the initial data is loaded in to the handling system). Then when a ticket is used, mark each individual record. Again, I would need to implement some sort of locking to be sure that two people can't modify the same record.


I think I've stared at this too much and the goalposts have moved so much that I've got lost between versions... The goalposts are firmly set now though. In theory



Any thoughts on what I should be doing?

I'd use a distinct ticket for each person, and associate those with a unique identifier of some sort (such as a serial number, etc). Associate all of the tickets with a "ticket group" which corresponds to the ticket table you descirbed above. When each ticket is used or invalidated, record the time and ticket taker.

Does it make any sense to build this kind of application when there are well built, and very competitively priced ticketing systems such as Online Event Registration - Sell Tickets Online with Eventbrite that have great tools (including iPhone/Android apps) to allow you to manage ticketing in all kinds of creative ways?

-PatP

The "ticket group" you describe is the only thing that will be issued. This will be issued to the purchaser only and it is up to them to share it with their friends/members. This decision is out of my hands.

Essentially when a group arrives at the venue their ticket will be scanned and the number of adults and children in that group will be "marked off" which may, or may not, leave people on the ticket group un-used until later.

Sadly this group is locked in to their current purchasing system(s) for a while yet, across multiple events and groups and none of the existing solutions researched was appropriate, hence why I've been asked to roll something for them.

*bump*

Any insights people?

If they are "locked in" and nothing looked at was appropriate, they may need to quickly move on . . .

What constitutes "a bit"? If some things are "improved" how will they be integrated into the existing "locked in" process(es).