Doubts database / batch control system

Navigation
0

I'm working on a project where I came across a problem where I do not know what would be the most efficient way to do it.

My project has a product registration in which I want to control by batch, for example:

Product - Rubber

Lot 1 - I bought a lot of 100 rubbers for R $ 50 reais and I will sell the real R $ 1 each for a total of R $ 100.

Lot 2 - I bought a lot of 100 rubbers for R $ 70 reais and I will sell at R $ 1.70. increase due to purchase period

My bank has 2 tables so far, Product and Product_lote in which I will leave below as is. My question is: how to drop the quantity of each lot when there is a sale and automatically the system move to the next lot without there being any change in the quantity field so that it will serve as a history in the future.

    
asked by anonymous 19.10.2015 / 17:41

1 answer

1

Create a table for history control ... So your inventory table will have the current quantity, and in your history table you will have recorded all operations of buying / selling products ... example: 

ID   ID_PRODUTO   OPERACAO   QUANTIDADE   DATA
1        1          COMPRA       1        19/10/2015
2        1          VENDA        1        19/10/2015

and you create a trigger in the stock table to capture operations and record in history

    
19.10.2015 / 17:47
Display results centered on a table Animate Load when clicking