Hey there! I'm developing a comprehensive stock management app, with production, purchase, sales, adjustments etc. Since GSheets can't hold a lot of data, I'm using MySQL (hosted on Digital Ocean). Everything's fine, but I'm stuck at this:
My schema looks something like this:
A Products table that lists all products
A Parts table that lists all parts (which are used in the production of products)
The Products table has a child table BOM which contains which parts are used for each product along with the quantity of each part (The child is a part of the parent)
A Production table that lists the date of production (date is the key, since I want only 1 entry per day), with child Table Production_Deets that lists the Product ID and the quantity produced. (The child is a part of the parent)
Since Products are sold, and parts (which are like raw materials) purchased:
A Sales table that lists the Sales reference number, date & party name, with child Sales_Deets that has the details of the products sold along with quantity. (The child is a part of the parent)
A Purchases table that has the party name, date, with child Purchases_Deets that has the details of the parts purchased along with quantity (The child is a part of the parent)
There's also a Stock Adjustment table for Products (wherein stock of products can either be increased or decreased manually), and another for Parts (wherein stock of parts can either be increased or decreased manually).
Now the challenge is to combine all product-related tables into 1 table, and all parts-related tables into a second table.
I'm looking for a solution to prepare a stock ledger for products, that shows Production and stock-in, while Sales as Stock-out, along with adjustment entries (which can be either stock-in or stock-out depending on nature of adustment). This would then help me also calculate the total stock-in quantity, total stock-out quantity & current stock balance.
Similarly, I want a separate stock ledger for parts, that shows Purchases as stock-in, while Production as Stock-Out, with adjustment entries (which can be either stock-in or stock-out depending on nature of adustment). This would then help me also calculate the total stock-in quantity, total stock-out quantity & current stock balance.
I though of mainatining 2 separate stock ledger tables (with automated behaviour actions that add entries to it when a Production/Sales/Purchase/Adjustment entry is made), but the issue is, what happens when an update is made to one of those entries.... i.e:
Let's say I entered a production entry wherein I produced 5 units of Product 'A' (which requires say 3 parts:
1 unit of Part X, 2 units of Part Y & 3 units of Part Z)... so my ledgers look like this now:
Product Stock Ledger:
| Stock Line ID | Type | Parent ID | Product ID | Quantity |
| jhijsd3 | Production | a2xcds | A | 5 |
Parts Stock Ledger:
| Stock Line ID | Type | Parent ID | Product ID | Part ID | Quantity |
| asdj23 | Production | a2xcds | A | X | -(5X1)= -5 |
| asdj25 | Production | a2xcds | A | Y | -(5X2)= -10 |
| asdj26 | Production | a2xcds | A | Z | -(5X3)= -15 |
Now, I can't figure out how would I update these entries if a change is made in the Production table itself, i.e say I delete the production entry of A, or change the quantity to say 10 units...what happens then???
Thanks for your time & attention!
LinkedIn
Twitter
