In my inventory database there is a sale
table.
This table contains fields sold qty
and balance qty
of an item after each sale.
While implementing 'edit sale' feature, I need to update the sold qty
field of a sale done in the past. This will affect the balance qty
field of all the records past that sale of that particular item. I would need to run a query to update all the sale records past this sale. Assuming the total no. of records in this table < 10 K, is this a good design?
Should I calculate balance qty
on the fly when needed for report/visualization and not storing it in the sale table?
Sale table structure
====================
Sale Id | Item ID | Sold QTY | Balance QTY | Date
1 1 10 1000 2018-01-19
2 1 50 950 2018-01-20
3 1 100 850 2018-01-21
4 1 200 650 2018-01-22
...
This kind of table is useful for a report where I need to show how the items sold over a period. Now I need to give edit feature where a user can edit a past sale for example (sale id = 2) He can change the sold qty to 100 from 50.