1
A stock table that will be widely accessed where I will have the quantity field being constantly changed with inputs and outputs which is most indicated in the matter of tunning and performance?
1- My stock table own one row per product and at each stock update I do an update on Qtd?
Or
2- my stock table own the product id field and Qtd. Instead of doing an update on Qtd I would have a relationship with a daily stock movement table and would be made input Qtd Inserts with positive vlr and output with negative vlr.
All my queries would.Oin between the 2 tables summing the stock table Qtd with the daily moved Qtd sum At the end of the day a process would be run where it would update the stock Qtd by deleting the data of the previous day from the movement table
This solution 2 I found strange but was indicated by a dba for the reason of many updates cause small Ocks causing loss of performance, since Insert does not cause Lock
I asked about the select where in solution 2 there would be need to join with sum and group by and in solution 1 a simple select would already solve.
He told me that in this case a heavier select would burden the bank less than a simple select but numerous daily updates.
What do you think, guys? Solution 1 or 2?
You are in the data modeling step or in the physical database implementation step?
– José Diz