Arthur Fuller
artful at rogers.com
Mon Mar 28 15:49:59 CST 2005
Just to close off this thread, what I ended up doing (and which works) is this: One table is called OnLineOrders, with a column called DateTimeEntered and another called BookingExpires, which defaults to GetDate() + 10 minutes. The table where the inventory is kept is called TicketAllocations. Any entry in OLO has a TicketAllocationID. I subtract the tickets allocated in OLO from the number of tickets available in TicketAllocations. A simple UDF handles the details, returning the number of tickets currently available both to the Access app and to the web app. A sproc runs every five minutes, although the "window" is 10 minutes. Any row in OLO older than 10 minutes is flagged as "dead". The subtraction above ignores these rows. It was decided by the PTB (powers that be) that we should preserve these rows for followup by a human, so I don't delete them, just flag them. It seems to work so far. No complaints yet LOL. Arthur Jim Lawrence wrote: >Hi Arthur: > >On an application, I was involved with, that required a specific time, the >purchase was recorded down to the hundredth of a second. The time was >recorded from the moment that the individual started the actual purchase >event but the first person who completed the payment transaction was the >individual that was recorded. The system was setup to simply roll-back any >process that did complete on time, against a particular product and display >some message like 'The transaction could not completed, please try again.' >In the couple of years that followed the roll-out, I never heard of a >transaction that had to be aborted part way through, by the system. The time >between the start of the session and the completion could be 10 plus >minutes... > >My thought on the subject is that the first person who's card transaction >completes get the tickets ... 'you snooze you loose'. > >Jim > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur >Fuller >Sent: Saturday, December 25, 2004 10:06 AM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] TicketMaster-like Timer > >In this particular case, it is not serialized inventory. In fact, we >often don't know the particular seat numbers when we go on sale. Rather, >what we know is that we are guaranteed possession of say 200 GA tickets, >200 Silver and 200 Gold. And also that they are in the best half of said >blocks. For example, our U2 stuff goes up for sale on Jan. 6, which is >when the tour will be officially announced. You might be buying tickets >to a concert in July. We won't actually get the tickets in our hands >until sometime in June, but since we're partnering with U2 on this tour >we know we'll get the tickets. The customer receives a voucher etc. and >depending on the package she buys, perhaps also a hotel voucher and >maybe a limo voucher etc. > >So what we are concerned about is the number of tickets of each quality >that is available. > >When you're dealing with serialized inventory, particularly reserved >seat tickets, there are lots of issues that are pretty difficult for >software to handle, but rather simple for a human. For example, you have >to be very careful about selling single tickets, because you most >definitely don't want to sell one seat from a group of 4 adjacent seats, >although you will sell one on either end of a group of 5 adjacent seats. >The general rule is that only losers go to concerts alone, so devote >your efforts to the groups of tickets, first by even numbers and then by >odd numbers. Another cute wrinkle in this situation is that you may want >4 tickets and I have no groups of 4 left, but I do have two "vertically" >adjacent pairs (row 6, seats 7 and 8, row 7, seats 7 and 8). On its >face, that requirement is not all that tough to meet with software, but >then you have to factor in the fact that there are several different >numbering systems in use at the various venues, a couple of which seem >to have been designed by people in serious need of professional help. > >Anyway, those are not my concerns with this app. I'm concerned only with >the timer-issue. So the specific problem is: > >a) User #1 requests N tickets. >b) Software subtracts N from Available Inventory. >c) Start a 5-minute timer. >d) User either completes the transaction or the timer times out. In the >latter case, add N tickets back to Available Inventory. > >This sequence can occur for an indefinite number of users at once. Call >that number P. So I need a method of setting up P timers. Lately I've >been thinking that a trigger might be the way to go with this. Or a >sproc that executes every minute. I haven't looked into the granularity >of the schedule thing, dunno yet if you can go down as far as one >minute. It certainly wouldn't hurt performance to run such a sproc once >a minute, given that all it has to do is delete rows whose timestamp is >older than Now - 5 minutes. > >Arthur > >P.S. >It's Christmas day and you can see that I have no life. > > >John W. Colby wrote: > > > >>Arthur, >> >>The thing here is that you are selling SPECIFIC inventory items, i.e. >>specific seat numbers in specific locations, not a generic telephone or >>laptop. Thus you have specific inventory records, and can reserve those >>with a date stamp field in that specific record and some field that says >>what "user" owns the seat. Set the date stamp, then clear it and the >>"owner" X minutes later. >> >>John W. Colby >>www.ColbyConsulting.com >> >>Contribute your unused CPU cycles to a good cause: >>http://folding.stanford.edu/ >> >>-----Original Message----- >>From: dba-sqlserver-bounces at databaseadvisors.com >>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur >>Fuller >>Sent: Saturday, December 25, 2004 12:06 AM >>To: dba-sqlserver at databaseadvisors.com >>Subject: Re: [dba-SQLServer] TicketMaster-like Timer >> >> >>Regardless of the particular inventory stocked, assume that it moves >>quickly and that there are a finite number of objects available. Looking >>at the TicketMaster layout, you have 5 minutes to comple an order or >>it's cancelled. Which means, I think, that some magic happens in the >>back end that subtracts the number of tickets requested from the >>available inventory, counts ticks, and if you don't press Submit quickly >>enough your order is toast. This is the part that is giving me >>conceptual problems. Assume 49 people are logged on and there are 100 >>inventory objects available. How do I set up 49 timers and roll them >>back after exactly N minutes? >> >>A. >> >>Mark Breen wrote: >> >> >> >> >> >>>Hello Andy, >>> >>>I hear what you are saying but.... >>> >>>1) If you delay long enough on an Airline site, do you believe that you >>>will retain the seat >>>2) If you delay long enough on a Dell site, do you really think that >>>the PC is being 'held' for you. >>> >>>The reality is that you either disappoint many customers by having the >>>stock all tied up with enquiries or people that do not have the ability >>>to pay for it, versus, once in a blue moon, you have to display a >>>message that "this product has just gone out of stock, but we would now >>>like to offer you this as an alternative", depending on the scenario, >>>you could even offer it as a free upgrade. >>> >>>I accept we are only talking in general terms here, we do not know >>>whether Authur is talking about Blood donations or Candy bars in a >>>store. But my point is just to consider the implications of tying up >>>stock with mere prospects rather than customers. >>> >>>I did not get as far as suggesting, but Authur could also consider more >>>sophisticated means of pooling the enquiries and limiting them to >>>10-20% of the stock, so that all enquiries are seeing the same pool. >>> >>>All this is difficult without knowing the business in detail (which is >>>not the intention here), but I still say that it is be be considered. >>> >>>Perhaps it is a B2B model within one company and in that case, it is OK >>>to hold the stock in advance. >>> >>>Cheers and have a good Christmas, >>> >>>Mark >>> >>> >>> >>> >>> >>>On Mon, 20 Dec 2004 11:26:26 +0100, Andy Lacey >>><andy at minstersystems.co.uk> wrote: >>> >>> >>> >>> >>> >>> >>>>As a customer of these kind of sites (and I am) I'd soon stop using it >>>>if it told me there were tickets, I said I'll take 1, went to the >>>>trouble of putting in my details, then my cc details, then it told me >>>>my tickets had been sold to someone else. It may sound like business >>>>sense not to hold that stock, but the longer term good business is not >>>>to aggravate your customers. >>>> >>>>-- >>>>Andy Lacey >>>>http://www.minstersystems.co.uk >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>_______________________________________________ >>>dba-SQLServer mailing list >>>dba-SQLServer at databaseadvisors.com >>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>>http://www.databaseadvisors.com >>> >>> >>> >>> >>> >>> >>> >>> >>> >> >> >> >> > > > >