Best practice for releasing inventory in a database

Question

I'm building a ticket-selling application which tracks an inventory of tickets, de-activating them when a particular ticket is sold out.

I'm wondering what the best practice is for releasing inventory back into the store when an order is abandoned mid-way through.

The current flow:

• Users add items to an order as line_items and the order is marked as completed on successful payment
• items has a quantity_available that's updated based on their line_items
• I sweep periodically for orders with no action in > 20 minutes, delete those orders' line_items and update the quantity_available

It feels like I'm missing something with this. For one, I lose the ability to review abandoned orders in detail (I still have any payments/declines, etc... but no line items). And if a user tries to resume an old order after 21 minutes they'll have to start fresh.

Conversely, it ties up inventory for 20 minutes which could lose us sales when a show is nearly sold out.

Any insight would be much appreciated. Thanks.

Sirocco

As a follow up: I thought about counting the line_items at runtime to get the current inventory available as discussed here. To me that seemed like it would be slow needing to perform that query on every single transaction, and that caching the quantity would be better. Very possible that I'm wrong about that, though. – Sirocco

It's hard to avoid this, however I'd store each reservation/purchase, when someone's signing up you'd check count(r+p) against quantity_available (which should never be adjusted). If you don't do that you can have the case where some script/process adjusted the number but you can't see why - which could lead to over/under sell. FYI I run several such software systems and have not run into the "what if someone wants it but someone else has it reserved and doesn't use it" situation - yet (although I also feared it) – Broad

Answer 1

How about introducing a different tag: reserved or something. While an order is processing, you can mark the ticket reserved which decrements the total inventory count. But you now know exactly how many tickets are in limbo.

During your 20 minute long order, if the number of on-hand items is very low or empty, you can send updates to the user. "Order has been stagnant for 5 minutes. Ticket sales are going fast, please complete your order soon to ensure your ticket is still available."

You can also tell potential buyers that there are x number of reserved tickets that may become available, so they should check back or something. Maybe they could sign up to receive an email if a reserved ticket comes back into the system.

Miscalculate

I like the idea of warnings to add that sense of urgency. It would definitely add a bit of front-end complexity but it may be worth it. – Sirocco

I ended up do something like this -- I added a column to line_items called affects_inventory that is true for complete and pending orders and false for abandoned orders. – Sirocco

yes, the front-end gets more complicated, and probably AJAXy, but as a consumer I appreciate those kind of messages. It might decrease your abandonment rate. – Miscalculate

Answer 2

I am wondering why you tie up the inventory based on an unprocessed order? I am thinking of the way popular sites like Newegg and Amazon work. I can create a shopping cart and it will live indefinitely. But it doesn't do anything to my inventory, it's just a record in a database. With Newegg, I can go back to the site months later and my abandoned shopping cart is still there (which has been very handy for me in the past).

You only modify the inventory when the user completes the order. This would also allow you to run reports on abandoned shopping carts because you'd just use the last modified date combined with order complete to identify which carts are abandoned.

Windmill

What happens when there is only one ticket left, for example, and two orders are both in progress for that ticket. Theoretically they could both make a payment at the same time and one of them would be out of luck. – Sirocco

You'd have to code for that. If you use DB transactions you can always check as your final step to see if any other orders have depleted the inventory. If so, rollback. – Windmill

when you're looking at routers, it doesn't matter which one you get. if you're talking about reserved seats, it very much matters. i might not want to go through with a transaction if it's not the exact seats i picked. – Miscalculate

I noticed your followup above appended to the original question. I would not worry about performance for checking inventory. If you index your tables, a simple check inventory query should only take a couple of milliseconds. It's better to have good customer service and than to code for performance. If performance is a problem, upgrade the hardware (or look at optimizing the DB/code). :) – Windmill

Oh...I guess I didn't read the original question well enough and didn't catch that we're talking about event tickets here. I totally agree, in that scenario it makes sense that you'd want to let the user reserve the tickets before hitting complete. Disregard my comments. :) – Windmill

How about this idea? What if you put a "reserve" stage into your order processing. Make it clear to the user that once they hit that stage (maybe after they fill out the initial form and before payment, or maybe once they click the original order button), their tickets are reserved. You could also communicate at this point that they have x minutes to complete the order or their tickets go back into the pool. That way it is all in the user's hands. And you continue to use your sweep process to reclaim abandoned inventory. – Windmill

Answer 3

If I understand this correctly, it sounds like you should be encapsulating the different steps into a single transaction that rolls back or suspends, if not completed in a timely manner - i.e. the 20 minutes you have mentioned. That way you could lock and unlock line_items records without having to add them back and forth.

It also sounds like you need to think about how you define an 'order'. You probably need to introduce some more steps. The process of going through an order is 'reserving', only when payment is made is it confirmed. That way you could release stock to 'reserving' (over reserving is OK) and the first person to make payment gets the order. The others fail - they should have been quicker!

Machado

I'm hesitant for people to end up on the checkout page and find out that they weren't fast enough. That may be the only way, but I know that as a consumer I'd be pretty pissed if I started an order, went to get my credit card and came back to find I'd missed out. – Sirocco

@AlexDunae, I addressed this issue in two steps. first put a tick down clock telling how much time in minutes and seconds is left for them to complete the order no matter if they are in the final stages or even providing CC info. second when the order is in progress (payment is being made) the gateway response is awaited. If the tick down counter ran out and if the payment was declined - then the customer is informed with the error message and also the cart is emptied. this way it works fine! and to maintain the inventory when the payment info is sent to gatway i simple mark it payment pending – Rawdan

@AlexDunae, and i count the payment pending status in the reserved criteria. If the payment was declined then the status will become pending or declined or expired if the tick down counter ran out! – Rawdan