Help how to implement Database Design Inventory with FIFO

pthsoon pthsoon 1 Reputation point

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
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

    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

    More interesting article

    0 comments No comments

  2. Olaf Helper 41,571 Reputation points

    See => Inventory for some examples.

    0 comments No comments