SBSP has some good suggestions. Also, tracking value is different from tracking qty. Value can be based on Avg costing or FIFO. Very different implementations.
A lot of the implementation deoends on your requirements. Firstly, lets assume you track 'qty of' like items Product A/B/C as you said. So typically you will have a table that identifies the product code with a unique code, some descriptions etc and Current stock qty. I normally like to have a top-level qty that shows the total qty of items in stock:
Table PRODUCT
- CODE
- NAME
- QTY
There are several activities that influence product qty:
- Receipt
- Sales/Invoice
- Stock adjustment during stock count
- transfers from one location to another. This does not change total stock, but changes stock qty in the diff locations.
- etc
What i like to do is to create an audit or detail table that records each and every qty change in the system. not only is that good from an audit perspective, but also good from a support and reporting perspective. I.e. looking at a single product code, be able to see all the changes in qty for that product code by looking in one place. By tallying the audit records, the top level total as well as the receipts and invoices and adjustments in a 3-way comparison you quickly find bugs. So the audit table could look like this
-ID
-Timestamp
-UserId
-ProductCodeId
-TransactionTypeId
-Qty
-ReceiptId
-InvoiceId
-FromLocationId
-ToLocationId
Each and every update to qty has one or more entries into this table as well as the ProductCode table qty.
The timestamp and user columns could tell you who made the change and when. The TransactionTypeId identifies the activity that caused the qty change e.g. Receipt, Sale, Transfer, Stock Adjustment, etc. Then in the audit table you can also store a reference to the document that relates to the change. What is important is that the Qty column is >0 for increases in qty and <0 for decreases. This means that ProductCode.Qty = sum(Audit.Qty). Believe me you gonna need this and at some point and your customer will ask for this.
One of your questions related to whether you calculate then qty on the fly or not. Always keep a total qty. You cannot, as the system grows, total up millions of rows just to get the current qty. If you suspect the top level qty is wrong you can always reupdate it from the audit (after you fixed the bug that cause it to drift). Having th qty on the ProductCode row also makes coding so much easier. You have to obviously be aware of concurrent updates and how to resolve those conflicts but we can dwell on that later if you wish.
When it comes to updating qty and inserting an audit record, it depends on your requirements and intended workflow. In my case, you could create a sale and add products to a sale. This does not yet update qty because you can cancel a sale. Only when you invoice the sale, you update qty because you can never cancel or delete an invoice. The invoice activity is a point of no return. The only way to reverse an invoice is to issue a credit note. That in turn increases stock again.
These were just some initial thoughts, sitting in bed. Feel free to ask if you have detailed questions.
EDIT: as a matter of interest, my ERP manages stock as above (I call it non-tracked items) as well as tracked items where each item has its own life-cycle throughout the production (receipt to sales). The individually tracked items build up a complete history of attributes as it moves along the production process. The implementation is totally different from normal stock management because of the traceability aspect.
![[)roi(]](/forum/data/avatars/m/8/8711.jpg?1563828683){kind=avatar}
