Help how to implement Database Design Inventory with FIFO

Question

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?

Answer 1

You can have a new Stock table (LastQuantity, ProductID, CurrentPrice, Margin) and use triggers on both tables to update that table. That would make FIFO simple.

I worked on FIFO problem a while back and found it to be quite complex. My article on this topic
https://social.technet.microsoft.com/wiki/contents/articles/18711.t-sql-fifo-inventory-problem-cost-of-goods-sold.aspx

More interesting article
https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/t-sql-window-function-speed-phreakery-the-fifo-stock-inventory-problem/

Answer 2

See http://www.databaseanswers.org/data_models/ => Inventory for some examples.