If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Stock Control and Locking

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,002
Stock Control and Locking

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?
__________________
George
Home | Blog
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,735
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,002
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.
__________________
George
Home | Blog
Reply With Quote
  #4 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,002
*bump*

Any insights people?
__________________
George
Home | Blog
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Location: 221B Baker St.
Posts: 487
Quote:
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.
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).
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On