2

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.

1
  • You never add to inventory?
    – paparazzo
    Jan 29, 2018 at 11:48

1 Answer 1

3

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.

enter image description here

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

1
  • Good point. So now if you're calculating remaining stocks (ProductInventory), it should be SUM of ProductInventory for a productId, MINUS the SUM of all OrderQty field that has been sold in SalesOrderDetail table WHERE the order Status field has not been cancelled? Is it ok on the fly for performance? What if at some point we have a difference because of an bad stock items? Should we edit stock? Or add new stocks to fill the gap sometimes?
    – KeitelDOG
    Jul 9, 2020 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.