Search code examples
database-designinventory-management

Is Inventory management transaction table necessary?

In inventory management system I've designed I have purchase_orders, sale_orders and inventory_adjustment tables, each with items table (ex purchase_item) holding qty purchased, price etc.

Now to the question,

Usually in inventory management examples I've found there is something called InventoryTransaction table which holds INs/OUTs of products/skus

ID     date         sku_id  type   qty    price   warehouse_id
ABC    11.11.2018   bbb     IN     200    123     11
ABC    12.11.2018   bbb     OUT    120    123     12

This design looks good, because it reduces the complexity of calculating stock on hand, but it also has some drawbacks. I was thinking about using triggers on purchase, orders and adjustment tables to populate inventory_transaction table.

Is it worth the hassle ? Or should I just keep my existing setup and rely on sales, purchases and adjustments to calculate stock on hand?

I have limited experience with inventory management systems so I am not really sure what requirement I might get in the future. I prefer avoiding seeing for myself in two years what was the best choice :)

Thanks

Solution

  • The inventory transactions approach is the standard de facto which is used in many ERP systems. It is based on abstract accounting engine principles, too. The transaction history is required to ensure a non-negative total amount of every SKU in every period (i.e. day) not only in current one.

    For example, when modifying some document and corresponding transactions date, it may be yesterday or week ago or even tomorrow. However, all SKU amounts at both dates, old and new ones, should be > 0.