Multiple inventory management
I am looking for some help regarding the following task, that's proven too difficult for me :(
I have products in different shops in commission. I have one product list and all the shops has the same list. Some of them has some products from the list that others dont. However the prices can be different in these shops.
From time to time I have to restock these shops and sometimes I have to make the Invoice for the goods they've sold.
So I made these tables so far:
A joint table of these so I know which shop has what kind of products on what price:
INVENTORY (in different shops)
I have to make transactions (resupply and invoice). The products in the shops are not invoiced only if they sell it. Later if I want i should be able to search back by transaction. so:
TRANSACTIONS (related to inventory through shop_id)
-Kind (resupply or sell)
TRANSACTION_DETAILS (related to transactions and an other product table)
-price (i want to store the price cause if I change it in the Inventory I can still review it later)
So this is where Iam stuck Completely. I want to calculate the current stock in Inventory according to transactions. My Idea was to SUM up all the "total price" fields by shop_id and product_id and deduct (sale/invoice) or add (resupply) to my current stock in inventory table. But I dont have the slightest idea how. Or my database design is the problem.
Please help me out...