Doubt database / batch control system

Asked

Viewed 280 times

0

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

My project has a register of products in which I want to control by lot, example:

Product - Rubber

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

Lot 2 - I bought a lot of 100 rubbers for R $ 70 real and I will sell to R $ 1,70. increase due to the purchase period

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

inserir a descrição da imagem aqui

java database relational-model

1 answer

1

Create a table for history control... So your stock table will have the current quantity, and in your history table Voce will have recorded all the 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 Voce creates a Rigger in the stock table to capture operations and record in the history

  • Yes but the right then would be: when making a sale I remove 1 in the quantity of the stock table and when reaching 0, perform a query of all lots to know which is next? if that is so, after 5 years of company will have lots of lots making the system slow to consult all the lots and check the first registration and still contain quantity.

  • Look. I think stock control should have the current amount of products... and optionally, the initial amount... I don’t think looking for the next batch is a performance problem, but if it is.. create an FK for the next batch.. so when you need to pick up the next batch, you don’t need to do the search..

  • I should create a 'Current Batch' field in the Product table then? and then in each batch I tie with the next batch?

  • is an alternative

Browser other questions tagged java database relational-model

You are not signed in. Login or sign up in order to post.