RoR-e

If you haven't seen The 15 minute E-Commerce Site CLICK HERE


I'm currently looking for Contract jobs, Contact Me if you are interested. Dave.

E-commerce Tips 1.3 (Inventory) David Henner Jul 06

7 comments Latest by Gary

Tracking inventory can be much trickier than most people imagine. I've seen several different solutions. While many can work with very few purchases others are seriously flawed at scale. Lets go through a few solutions.

The first solution and for some reason the solution I see most goes like this.

@order.pay!
@order.items.each do |item|
  item.product.remove_from_inventory!(item.quantity)
end

class Product
  def remove_from_inventory!(qty)
    self.quantity = quantity - qty
    save
  end
end

Unfortunately there is a lot going wrong above. The first issue has to do with the fact that the inventory is stored in the product class. Inventory and products need to be broken down into separate class. The fact is that they are not the same. The product class should not for more responsibility than it should know about.

Now we can refactor the code to look like this:

@order.pay!
@order.items.each do |item|
  item.product.remove_from_inventory!(item.quantity)
end

class Product
  def remove_from_inventory!(qty)
    inventory.remove_items(qty)
  end
end

class Inventory
  def remove_items(qty)
    self.quantity = quantity - qty
    save
  end
end

So now you have classes that have one responsibility but there is another glaring issue. The math is actually not accurate if you have 2 customers purchase at the same time.

For Example: Lets say order_one and order_two purchase the same item at the same time. This means the inventory object for each order will have the same quantity (let just say it's 5). Now when inventory is updated the first time the quantity will reduce to correctly. The second inventory object still thinks the quantity is 5 though. So when it updates inventory the quantity will still be 4 (if one item is purchased).

This brings us to yet another solution. Lets do the math in the database.

@order.pay!
@order.items.each do |item|
  item.product.remove_from_inventory!(item.quantity)
end

class Product
  def remove_from_inventory!(qty)
    inventory.remove_items(qty)
  end
end

class Inventory
  def remove_items(qty)
    sql = "UPDATE inventories 
           SET quantity = (quantity - #{qty}) 
           WHERE id = #{self.id}"
    ActiveRecord::Base.connection.execute(sql)
  end
end

This starts to help manage the problem better but there are still some issues. The issue that needs to be dealt with next is what should happen when there is only a couple items left to purchase? With the above solution the database would now at least reflect the correct number of items. The problem is the number could be negative if two people purchase at the same time.

This is where business needs will probably determine the correct solution. Unfortunately, I'm not going to give you all teh solutions right now. One solution might be to have your database return an error if the quantity goes below 0. Then you would need an appropriate error handler. Another solution might be this:

class Inventory
  has_many :inventory_items
end

This illustrates that each inventory item has it's own database record. Now if the item is in your cart you are the only one allowed to purchase the item. Again, you will need some business logic that will free the items just in case the customer doesn't make the purchase but with this solution there isn't a way to go below zero items in stock.

Yet another solution might be to have a safety stock level. This would mean when you have less than X number of items in stock people aren't allowed to purchase. That isn't a perfect solution if you do want to sell out but it does solve the issue with over selling.

Two more glaring issue before I move on. This all needs to be wrapped in a transaction. Thus if there is an item with collecting the money the inventory doesn't change and vice versa.

transaction do
  @order.pay!
  @order.items.each do |item|
    item.product.remove_from_inventory!(item.quantity)
  end
end

Almost done... Now lets move the @order.pay! option to the end of the transaction. Otherwise if there is an issue with inventory (or anything else) the order will not be charged. Likewise if there is an issue with paying the whole transaction will be reverted back.

transaction do
  @order.items.each do |item|
    item.product.remove_from_inventory!(item.quantity)
  end
  @order.pay!
end

Quantity isn't enough

Now at a high level you should have an understanding of inventory quantities being accurate and I hope you can improve your code at this level. However another big issue we have is quantity is not a good gauge of what is really happening. Quantity available to sell and Quantity in stock might reflect 2 separate numbers.

For example:

  • You have 10 items in stock
  • One item is purchased online.
  • The amount you can sell has reduced
  • BUT the amount in stock is still 10 until the item ships

So in reality the Inventory class should have the following columns:

  • count_in_stock
  • count_pending_to_customer
  • count_pending_from_supplier (optional)

Now the quantity is just

class Inventory
  def quantity_available_to_sell
     count_in_stock - count_pending_to_customer
  end
end

Well there is still much more to learn about inventory. This might justify creating a new post in the future. I hope you have taken away something useful. Thanks for reading.

7 comments so far

Tony Collen 07 Jul 12

@CB, I've followed a similar pattern. The current inventory level is simply the sum of an "inventory_transactions" table, with all the deposits and withdrawals for a given (location, product) combination. The trick is knowing if a withdrawal would potentially run you out of stock. There's a ton of prior art, and Fowler's "Analysis Patterns" has a bunch of material on this subject.

DRH 07 Jul 12

Tony you are right. I actually only have an add_items method normally and then remove_items is: def remove_items(qty) add_items( - qty) end fixed now... thanks.

CB 07 Jul 12

What I've done in the past is keep an inventory history. There's a manual count to set the base line and then new records of incoming and outgoing values are stored as positive or negative values. Then your correct value is simple the sum of your historical records.

Tony Collen 07 Jul 12

I think this snippet of SQL isn't quite right: SET quantity = (#{num} + quantity) Shouldn't "num" be "qty"? Or maybe even "-qty"?

Alex 11 Jul 12

It looks like a good example of refactoring but it's not really good for inventory tracking. The main thing that i didn't like here is that you may end up with negative inventory. That's bad if you are limited in items. It would happen if someone buy last item at the same time or almost the same. Moreover you want to check do you have all items before making a payment. So as you said - inventory is more complex that it seems.

DRH 11 Jul 12

Alex... Also as I said, I'll be making a new post. Mostly because making sure zero inventory can be implemented in several ways. I sorta describe the solutions but didn't write the end code. I'll admit, I was thinking to myself "this post is never going to end" =)

Gary 14 Jul 12

Another thing is the incoming inventory. One day you might pay X for that inventory but tomorrow you might pay Y.. in order to calculate COGS you need to know what you paid for the inventory and what you sold it at.. Seems like you need more data than just quantity. Also as you grow you may end up with multiple locations. So you'll need to track inventory by location as well.. enjoyed the discussion though.. keep up the good work..

Comments are closed