In a stock control scenario where 1 product can be supplied by 1 or more suppliers and 1 supplier can contain 1 or more products, what to do on the following occasion:
Product "X" has 2 items in stock provided by supplier "A" and has cost price at R $ 23.00 each and sale price of R $ 50.00. The owner of the store not to let the stock run out, goes and buys another 100 units of the same product at supplier "B" with cost price at R $ 13.00 each.
In this scenario the profit on each sale of this product provided by vendor A is $ 27.00 and purchased by vendor B has a profit of $ 37.00 on each sale.
My question is:
I can not simply go to product "X" and change cost_cost to supplier value "B" because this product provided by supplier "A" has not yet run out and doing so would generate inaccurate reports. Is there any way around this without having this duplicate product in the system? Is this my model not the most appropriate for this type of situation?
EDIT1:
Followingthehelpof@mottaIremovedthecostpricefromtheproducttableanddecidedtoinsertitintothesupplier_producertable,howeverthatwayeverytimeImakeasaleIhavetomakeakindofsuminthestockcolumnofthesupplier_productstabletocheckifthereistheamountofproductspurchasedinstockwouldthisbeabadpractice?
Mypreviouslyraiseddifficultyremains,ieifacustomerbuys5unitsofproduct"X" at $ 50.00 each unit and I have in stock 2 units of that product provided by vendor "A" with cost price of R $ 23.00 and 100 units of that product supplied by vendor "B" with cost price of $ 13.00 per unit, what would be the process to calculate the profit from that sale? and How would this distinction of cost values from the same product be represented in the "sales" table?
