Help how to implement Database Design Inventory with FIFO

pthsoon pthsoon 1 Reputation point
2022-03-01T03:41:29.347+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,925 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,565 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-03-01T05:07:45.763+00:00

    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/

    0 comments No comments

  2. Olaf Helper 41,571 Reputation points
    2022-03-01T08:15:59.653+00:00

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

    0 comments No comments