is there any stock management design pattern?

Question

We want to design an e-commerce application, and we are mental about consitent stock numbers. We don't want our customers finding out, after they have bought an item, that that item is out of stock, that's a big thing here. The average order here has about 60 different items, which will makes things even trickier.

Let's imagine these two scenarios:

1st Scenario:

1) Customer C1 opens the online store and find a product he/she wants to buy;

2) That product is shown as "in stock" (but the current stock is 1);

3) Customer C1 puts 1 item in the basket;

4) Customer C2 gets into the website and select the same item (put in the basket), which is still marked as "in stock" (stock is still 1);

5) Customer C1 goes to checkout and confirms his purchase and the application decreases the current stock for that item to 0;

6) Customer C2 keeps buying items, let's say 35 other distinct items (it took 20 minutes to customer c2 to select the items he wanted);

7) Customer C2 goes to checkout and confirms this purchase, but now, the first item he bought is no longer available (and we CAN NOT sell it);

8) The application warns customer C2 that the first item is no longer available and that he has to check his basket;

9) Customer C2 gets pissed and close the browser without buying anything.

2nd scenario (but I think it is unnecessarily complex and buggy):

1) Customer C1 opens the online store and find a product he/she wants to buy;

2) That product is shown as "in stock" (but the current stock is 1);

3) Customer C1 puts 1 item in the basket (and the application decreases the current stock for that item to 0);

4) Customer C2 gets into the website and see the item he/she wanted is out of stock;

5) Customer C2 leaves the website;

6) Customer C1 keeps buying items (the stock decreases for it of these items);

7) Customer C1 closes the browser;

8) Every now and then some batch routine kicks in to remove the items which had decreased the stock but didn't get bought/confirmed.

We have just a few distinct products, but we have been selling about 30.000.000 items by phone, some products get sold as much as 2.000.000 every day, so the concurrency in the row responsible for the stock of that product might get many updates at the same time, so it's important we get a good performance.

Those are usual scenario, but is there any design pattern which gives the user a better experience while keeping the stock numbers consistent and yet yield a great application performance?

Any help will be much appreciated.

Cheers

Answer 1

Your 1st scenario is what most companies do, and that's why stock management systems have the concept of a back order.

Your 2nd scenario is more beneficial to the customer, but will reduce your sales somewhat, as well as be more complicated to manage.

This really isn't a database decision. This is a management decision on how you want to handle your inventory.

Most relational databases supported with sufficient hardware can handle 2 million changes a day.

Answer 2

First off, taking a step back, do you really need to solve the inventory management problem on the front end? Since you're selling large volumes of a relatively small set of products, it should be relatively easy to manage your inventory so that you are never out of stock or, if you are, it doesn't prevent you from fulfilling orders. There is a great deal of literature and examples that deal with calculating safety stock which requires just a bit of statistics to follow. It would make far more sense to me to focus your attention on giving the company the tools (if it doesn't already have them) to manage their inventory to prevent stock-out situations rather than trying to prevent them from happening in the sales portal.

That being said, I'm not quite sure that I follow your problem with the two scenarios you outline. Even if the database performance was flawless, if you have only 1 of item A in stock and you can't sell an item if it's not in stock, then one of the two customers, by definition, one of the two potential customers is going to lose out. If in the first scenario C2 is going to go away without buying anything if any of his 35 items are not in stock (which seems unlikely if he spent 20 minutes filling his cart), there is nothing you can do in the database to prevent that. Your interface could potentially have some AJAX that alerts them while they're shopping that one of the items in their cart is out of stock much like StackExchange notifies you while you're entering an answer that someone else has entered an answer. It's not at all clear to me, that telling C2 about the problem earlier is going to be beneficial-- if he's going to leave if he can't buy all 35 items in one transaction, he's going to leave no matter when you tell him that C1 bought the item. Realistically, there is no way to design the system so as not to disappoint one of the two customers in that case.

It might help if you can explain a bit more about why your application and your customers are so sensitive to stock-out situations. Most customers and most retailers are relatively accustomed to the fact that sometimes after placing an order they get notified that the retailer isn't going to be able to fulfill the order as quickly as they had expected and are given the option to cancel that part of their order, the whole order (assuming the remaining items haven't shipped yet), or to wait for the item to come back in to stock. Assuming that you do something to notify customers while they're browsing that inventory is relatively low (i.e. Amazon will tell you "N items in stock" if you're looking at an item for which they only have a handful of stock left), most customers are reasonably understanding 20 minutes when they get to the checkout and are told that the item is now out of stock since they knew in advance that they needed to order quickly. And most retailers are comfortable that even if they run out of stock of most popular items, they can still satisfy more requests than they have inventory in hand because they undoubtedly have new inventory arriving in the next day or two or they can rush an order for new inventory.

Answer 3

You could try to find out how other online retailers do it, and emulate them. For example, when Amazon is almost out of a product, they'll often display a notice saying, "Only n left in stock!" Try to find a product like that, then add it to your cart in one browser and use a different browser to see what happens to the inventory.

Answer 4

I am with Justin and Gilbert. This is more about logistics than front-end. There is also the amazon solution of saying "I want all these things shipped in the same packet", (i.e. that will take longer, as all the bits have to wait for the slowest one) or "send them separately, as soon as they are available". Basically, you give yourself time to restock.

I think the most infuriating scenario is booking airline/ferry tickets, and when you get to the paying part, they either time out, do "not have that price available anymore" or some such nonsense. Particularly annoying, as i is not exactly buying cruise boat propellers..

You could do a kan-ban routine, where you basically say that when you have 10 (or whatever) left of something, it is shown as "1 item remaining" in front end. That means that customerA and customerB buying at the same time both gets their stuff. And then the warning goes to procurement within the company: "we are "out" of objectN".

I would be interested in knowing what kind of stuff your client is selling, that most customer buys 60 objects.