I have a question about how I can model my stock database in order to get the best performance possible.
In SQL Server or in the Oracle, each update executed generates a little lock.
I'd like to know what's the best solution that you could tell me
Solution 1: create a product stock table with quantity column and for each input or output execute a SQL update against this column
Solution 2: create a table for product stock movement where for each input I would execute an insert with a positive quantity and for each output I would execute an insert with a negative quantity.
At the end of the day, I would execute an process for update the quantity of the stock products with the "sum" result of the product stock movement table After that, I would delete all records in the product stock movement table
With solution 1, I would have the advantage that execute an simple select to get the product stock quantity but during the day I would have the disadvantage that have many locks due many quantity updates regarding output sold products
With solution 2, I'd have the disadvantage when, I will need to get the product stock quantity, I'd need to make a query with a join with product stock movement table and make a sum in all inputs and outputs of the consulted product, but in this way, during all day I wouldn't have any locks
What do you think about that two solutions presented?
Is it a good practice to make the modeling described in solution 2?
Thank you so much