Inventory database maintaining remaining qty of an item

Question

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.

Answer 1

I'm not a sqlite expert, but I definitely think your database should be normalized further and you should store Quantity for an item on a separate table than the Sales table.

Even though you've tagged your RDBMS as sqlite, I'd recommend having a look at the AdventureWorks table definitions to get an idea of how you might split your data attributes out to reduce data redundancy. In the AdventureWorks database, the Quantity is stored in ProductInventory table.

At a minimum, you could start with:

• Customer Table - Contains current customer information. Customers are categorized by type: individual consumer or retail store.
• SalesOrderHeader Table - Contains the general, or parent, sales order information. The specific products associated with the sales order are stored in SalesOrderDetail Table.
• SalesOrderDetail Table - Contains individual products associated with a specific sales order. A sales order may include orders for multiple products. The general, or parent, information for each sales order is stored in SalesOrderHeader. Each product that is ordered, or child, is stored in SalesOrderDetail.
• Product Table - Contains the products sold or used in the manufacturing of sold products.
• ProductInventory Table - Contains the product inventory information.

Be sure to check out the other AdventureWorks table definitions as they may give you additional ideas about splitting out your data.