In my inventory database there is a
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.