good day everyone
The question is the following, in the tables, how do they relate the quantities of a product, for example, in one table it is for the warehouse and the other table is for the display, to insert the products in the table in the display table cellar to the display table, how this process is carried out, a function is made that validates the quantity in the cellar table later if it complies with the validation, the quantity is subtracted from the cellar table and the same quantity is inserted into the table exhibition? Is it something like this or what is the way to do it? Another, how to know which is the best query? How do you measure the performance of different queries that do the same job?
I think you have a database design problem. It all depends on the requirements you have. A simple solution would be to have, for example, a table
STOCK
with the columnsPRODUCTOID, SITUACIONID, CANTIDAD
and another namedSITUACIONES
with the fieldsSITUACIONID, SITUACION
with the values 1 BODEGA and 2 EXPOSICIÓN. When new product enters the warehouse, aINSERT
table is madeSTOCK
with the amount that enters withSITUACIONID=1
. When the quantity of a product is increased, a changeUPDATE
of that product is madePRODUCTOID
, increasing the existing quantity with the new one. When a quantity of a product goes on display, two are madeUPDATE
, one subtracts the quantity of product withSITUACIONID=1
and increases it bySITUACIONID=2
, previously you must know if you have an entry in the STOCK table with that product inSITUACIONID=2
, otherwise that secondUPDATE
would be aINSERT
. To ensure that the data is correct in case of error or failure, the two statements would be included in a transaction.But you can complicate it. They may ask you to know the history of movements that have taken place. For this you must have another table that we can call MOVIMIENTOS_BODEGA, where the entries and exits to STOCK are inserted, positive if the quantity in the warehouse increases, negative if it leaves, together with the date of the movement.