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
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
, 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?