Hoy hoy!

Question for you all, I'm hesitating between two concepts for tracking inventory and am wondering if anyone have an opinion on the subject. Doesn't really matter how the inventory is been changed (could be through a Sale order, a Shipping statement, a Balance Sheet, etc.), I'm more curious about what you think is the best practice.

Have a Product form with a field Inventory_Quantity

When a product is been sold/bought/lost, etc. update the quantity field directly

Have a Inventory_transactions form with fields Product (related to form Product) and Quantity

When a product is been sold/bought/lost, etc. create a record in Inventory_transactions for that product with Quantity either been positive or negative value.

Create a Pivot table or HTML list report to display the SUM of Quantity for each unique Product

Option #2 is obviously better because you have a history of your inventory fluctuation. On the other hand, if you have 10 000 products and are making 1000 changes to your inventory every day... a year from now, you won't enjoy loading that Pivot/report no more.

Also inventory balance needs to be checked on every new row/product added to a Sale_Order OR on a screen for employees to know if there's enough quantity in stock to fulfill a Sale_Order. This also is problematic for Option #2 because now loading time is immensely important...

So I'm pretty hesitant on what the best practice should be. Have you experience implementing Option #2? Will it become unusable quickly in Zoho Creator? Is there another practice I am unaware?