Welcome to WebmasterWorld Guest from 18.104.22.168
Forum Moderators: buckworks
I do a second check at the point that the customer clicks 'Checkout' to make sure the quantities in the cart are still not above the inventory level. I'm not sure you can do anything better than this.
We have three tables in our DB, stock_inventory, pending_inventory, and purchase_inventory (for example.)
When customers are adding items to cart, we don't do anything at all. When they make a purchase, the items are added to pending. When the item is shipped, it subtracts the pending from stock, and adds the pending to purchases. This gets really complex when you're dealing with item options (an item that has color, finish, and size options is not really "1 item.")
The pending status is important. We've had a few fraud attempts or people call up and cancel an item in an order. We remove the item from their cart and it just removes it from pending, no items have been moved out of stock at that point.
Visible from the product display is stock minus pending.
This is also not perfect, as it doesn't "reserve" items put in a cart, only when they are checked out. We considered many approaches to this, the most likely being a "timeout" - the items go into pending for X hours. The problem there is we have even had customers organize a cart on Sunday and not actually complete the purchase until Thursday.
Also there is the potential for "cart fraud" by competitors, similar to click fraud with adWords. All a competitor has to do is figure out your stock decrements when items go into your cart, then they can go in and "buy up" your stock without buying anything. Legitimate customers see no stock and move on.
The bottom line, I guess, is it's not sold until it's sold, so if one customer beats another to it, that's just the way the early bird works. We're sticking with decrementing at point of sale.