Iam working on inventory control system. I want to take monthly reports it should contain all transactions occurred and quantity of item at the end of the month. I have item table and shop tables (not shown here).
shop_inventory
shopinventory_id |shop_id | item_id | Qty | reorder_level |
shopinventory_id - 1ry key shop_id-foriegn key(1ry key of shop table) item_id - foriegn key(1ry key of item table) Qty- total quantity
report_item
R_id |shopinventory_id | transaction_type | date/time | transaction_amount |
R_id -1ry key shopinventory_id-foriegn key
transaction_type - issue or recieve
if issue transaction_amount will be reduce from Qty and update shop_inventory table
if recieve transaction_amount will be add to Qty and update shop_inventory table
my problem is in this design i cant take the month end total Qty. because i am updating shop_inventory table. I wanna keep tables in 3rd normal form and take month end qty for reports. What are the changes that i should made?
