Database Design | Add and Subtract calculation values on database collection

Ask the community
, ,
1

Hello Velo by Wix from the Philippines, I’m trying to create an inventory management system using Wix, where I have the Beginning history of the product, Deployment History, and Item Return History.

Questions:

  1. What will be the best approach of database design to handle those data comes from the Beginning history when the product is added and will set a new “Job Order Number” when this product is deployed and returned, so I can display it in a table for their history.

  2. What example functions I need to subtract and add the value from the database, For example, I have 100 Quantity items for Product A, and will subtract when I deploy 20 items and will add back when Item is returned, I’m new to Wix code with very minimal code experience, I will highly appreciate if anyone explained this with code sample, Thank you so much!

Here is my sample database structure

1913×940 70.2 KB

2

If you have an order database you can use Hooks,
First you will add another field of current inventory to this table you sent.
And when an approved new order is added to the orders database, you download how many orders have been ordered so far and deduct from the total and update in the balance field.