Help how to implement Database Design Inventory with FIFO

Other SQL Server Topics
1

I like to doing a project on Inventory with FIFO,
can anyone give me idea on how to doing inventory with FIFO in database design?

Currenty I have this table

  1. Product (ProductID, ProductName)
  2. Purchase (PurchaseID, ProductID, Quantity, UnitPrice, SubTotal)
  3. Sold (SoldID, ProductID, Quantity,UnitPrice, Subtotal)

Somehow, i do not know how or way to tracking Inventory with FIFO using this table above, does i make a new table for FIFO tracking, or new database Design idea

can anyone help?

2

First thing is to make sure FIFO is understand correctly:

You have a Product A and you buy first 1000 units for $2 and the second order is 1000 units for $3.

When you sell the product for 600 units your unitprice will be 2. Your stock is now 400 unit * $2 and 1000 *3. The second time you sell 600 units it will be 400 units * 2 and 200 units * 3, the unit price will be 2.33. The stock is now only 800 * 3.

So your database design is good, it's only relevant to the unit price when you sell the product. However, your don't know why you have calculated the 2.33. That is because you don't know what the data was at time of calculation. What if the second purchase order was not the second but the thirth as a second purchase order wasn't in the system and you have a 1000 units at 2.5 wich should be sold earlier? Based on requirements you should first make clear what you/the business wants to happen.
Some companies don't care, some companies make a recalculation each night. I would first make documentation and ask the business what they want.

You could add the PurchaseID to the Sold table so you can always calculate how much stock of a purchase is sold and you can add QuantitySold to the PurchaseTable so you always know what part of the purchase is sold.

Make sure you can always explain what the system has done and that you can easily support the questions you get from the business and make sure you have documentation that you can give to a colleque when you go on a holiday :wink: