Inventory Schema Advise?

help
1

I would like to create new project Inventory.
I am not clear for Collection Schema with Inventory/Qty and AVG Cost.

My Schema

Product = {code, name, cost, price....}
Purchase = {date, ....} ---> product details to Inventory
Sale = {date, ...} ---> product details to Inventory

Inventory = {
  refType (Purchase, Sale),
  refId (purchaseId, saleId),
  productId, 
  qty (+ from purchase, - from sale),
  amount,
  total (qty * amount),
  balance: {
    qty (ending),
    total (ending),
    avgCost (ending)
  }
}

Problem:
- Entry new `purchase or sale` on previous date
- Modifier old `purchase or sale` on previous date
- Dynamic view inventory report (current or previous date)

We should recalculate all next Inventory records when have any modify previous records???
Please advise!!!

2

I would read through as many of these as possible. I’ve found this site to be really helpful when exploring schemas/data structures that I haven’t built yet:

http://www.databaseanswers.org/data_models/

3

@vigorwebsolutions Thanks for your quick reply :blush:

4

After I check

image
image418×806 16.1 KB

So it mean that We must up to date all of Product Inventory record, if have any modify on previous record of Purchase/Sale

5

Yes, I think that makes sense, if you have limited quantities of inventory?

6

don’t have limitated quantities of inventory.
If we sale with no inventory, it will show (-)