Hi all, I am adding inventory control to our online store here at work and I'm not sure of the best approach.

Let's say Customer A and Customer B are both looking at the product page for Product X at the same time, and there is only 1 left in stock. What should happen if both click "add to cart" at (more or less) the same time?

Should the last Product X get put "on hold" for one of the customers, and then decremented once the sale is complete? Or should the database decrement the product count, and then increment it if the sale doesn't go through successfully?

Is it still possible for both to add it to their cart? Are there any mysql features I can use to ensure both cannot purchase the last product?

Thanks!

[Edit: Also, what if a customer adds a product to their cart, and then leaves it there for a week?]

Hi mattca,

Both should be able to put the article in their cart.

Here is what happens most likely:

They both add to cart.
One will be the first to actually order.
At the moment he orders, you query the database and remove 1 of that article. This leaves the number to 0.
The second customer tries to order, your script should check the number. It finds 0 and thus cannot order that article.

The way you handle the database yourself is important. Make sure you check the quantity of an article just before someone starts to pay. No check will mean the amount of the article will just go to -1.

Best way to check if all will go well is to write or draw your process. You'll see the flaws (if any) straight away.

1 members found this post helpful.

Thanks, Mark, that is really helpful.

I'm only seeing one problem. If we decrement the inventory count when the user starts the checkout process, we need to increment it again if the sale is not completed. Currently we're using a third party shopping cart, and it doesn't notify us if a sale is not completed.

Actually, that wouldn't help anyway because we would have no way of being notified if the customer, for example, simply walked away from their computer without completing the sale.

I think the best we can do is put a timer on it, and then increase the inventory again once the timer expires.

In that case, maybe it makes sense to put the item "on hold", rather than removing it from the store. Kind of like putting a product in a physical shopping cart at an actual store. It hasn't been removed from the store's inventory yet, and if you leave the store without checking out, it'll eventually find its way back to the shelf.

Thanks again!

[Edit: actually, after thinking about it more I don't think the "on hold" solution is feasible. It would involve a counter for each and every individual product.. that would be a lot to manage. There must be a better way.]

For popular items, you can also just use what a lot of computer hardware stores use. Tell your customers with a green, yellow or red flag if the product is in supply. Use different delivery times for each flag. And let the paying process actually discount every article. That means you will get negative numbers in your system on your stocks. You then know you sold articles you need to get more of.

Example, article A:

Green flag: 100 pieces in stock.
Yellow flag: 40 pieces in stock.
Red flag: 10 pieces in stock.

Green flag: delivery within 5 working-days.
Yellow flag: delivery within 10 working-days.
Red flag: delivery within 20 working-days.

This means you don't need a real-time up-to-date stock. And you allow yourself 4 weeks of getting new stock in case it is needed.

Please note: I just came up with some random easy to calculate numbers. I have no experience with stocks what-so-ever. You'll have to think of your best settings yourself.

Hope this has helped you.

Thanks again Mark, that is helpful. But, unfortunately doesn't work in my case. We're implementing inventory control because we recently started selling "limited edition" products - products that we only have a certain amount of, and will never again be able to sell.

Your solution would make a lot of sense if we could continue selling the products - we could easily see how many we need to fulfill existing orders. But in my case, we absolutely have to prevent a customer from ordering a product once is it sold out.

In that case, why not take something of the following?

You mention the fact that it's limited edition products.
Let your user order the product, not yet pay.
The order is sent to your system.
State that you have received their order and will send a confirmation back.
You now got all the orders and are able to see who gets the products and send them the e-mail. E-mail those who are not able to get the product about that fact and offer something else.
When you send them the e-mail, you require they pay within 8 or 30 days or something.

This way you never give out products you don't have.

Just slow the confirmation by a minute, this won't lose you any customers.
In case 2 customers order at about the same time for the last product, your system will give out 1 product and send an e-mail to the other stating the product is out of stock. (Of course with extra advertising for surrogate products or whatever.)
You keep both customers, one really happy, the other less happy but thinks it's nice to have some alternatives.

Just my next thoughts on the subject. Correct me if I'm wrong. Also note that I have not done this myself before.

Don't decrement stock when the user adds it to the cart - otherwise someone could just add everything to a cart with no intentions of paying but would mean no-one else could order! You should let both add the part and then do a check at the point of order confirmation.