AnsweredAssumed Answered

Multiple inventory management

Question asked by StephanToppinger on Jan 8, 2014
Latest reply on Jan 16, 2014 by philmodjunk

Title

Multiple inventory management

Post

     HI!

     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:

     SHOPS
     -SHOP_ID
     -name
     -address

     PRODUCTS
     -PRODUCT_ID
     -name
     -design date

     A joint table of these so I know which shop has what kind of products on what price:

     INVENTORY (in different shops)
     -SHOP_ID
     -PRODUCT_ID
     -price
     -current stock

     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)
     -SHOP_ID
     -TRANSACTION_ID
     -Date
     -Kind (resupply or sell)
     -Total
     -Vat
     -Gross
      

     TRANSACTION_DETAILS (related to transactions and an other product table)
     -TRANSACTION_ID
     -PRODUCT_ID
     -quantity
     -price (i want to store the price cause if I change it in the Inventory I can still review it later)
     -total price

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

Outcomes