This can be a question not necessarily about "programming" (isn't specific to the language or database), but much more of design and architecture. It is also an issue from the type "What the easiest method to do X". I really hope does no induce to much "religious" debate.
Previously I've developed systems that in some way, keep some type of inventory of products (not relevant what products). Some using languages/DB's that don't support transactions. In individuals cases I elected to not save that quantity available of products inside a area within the item record. Rather the amount available is calculated amassing inventory received - total of inventory offered. It has led to very little discrepancies in inventory due to software. The tables are correctly indexed and also the performance is nice. There's a archiving process just in case the quantity of record begin to affect performance.
Now, couple of years back I mentioned employed in the corporation, and that i inherited something that tracks inventory. However the quantity is trapped in a area. When an entry is registered, the amount received is put into the amount area for that item. When a product is offered, the amount is deducted. It has led to discrepancies. For me this isn't the best approach, however the previous developers here recommend it.
I must determine if there's a consensus on what's the proper way would be to design such system. Also what assets can be found, printed or online, to find assistance with this.
I've come across both approaches inside my current company and would certainly lean for the first (calculating totals according to stock transactions).
If you're only storing an overall total quantity inside a area somwhere, you've no clue the way you showed up at this number. There's no transactional history and you will finish track of problems.
The final system I authored tracks stock by storing each transaction like a record with an optimistic or negative quantity. I've discovered it is effective.
It is dependent, inventory systems are about far not only counting products. For instance, for accounting reasons, you will need to understand accounting worth of inventory according to FIFO (First-in-First-out) model. That can not be calculated by simple "amassing inventory received - total of inventory offered" formula. However their model might calculate this easily, simply because they modify accounting value because they go. I'd rather not get into particulars since this is not programming problem but when they recommend it, you may did not understand fully almost all their needs they need to accommodate.
both of them are valid, with respect to the conditions. The first kind is better once the following conditions hold:
- the amount of products to sum is comparatively small
- you will find couple of or no exceptional cases to think about (returns, changes, et al)
- the inventory item quantity isn't needed very frequently
however, for those who have a lot of products, several exceptional cases, and frequent access, it will likely be more effective to keep the product quantity
also observe that in case your system has discrepancies then it's bugs that ought to be located and removed
i've done systems for both, and for both can function all right - as lengthy while you don't disregard the bugs!
You need to think about the existing system and also the cost and chance of altering it. I make use of a database that stores inventory type of like yours does, however it includes audit cycles and stores changes much like receipts. It appears to be effective, but everybody involved is well trained, and also the warehouse staff aren't exactly quick to understand new methods.
Inside your situation, if you are searching for a bit more monitoring without altering the entire db structure then I'd suggest adding a monitoring table (type of like out of your 'transaction' solution) after which log changes towards the inventory level. It should not be way too hard to update most changes towards the inventory level to ensure that additionally they leave a transaction record. You might give a periodic task to backup the inventory level towards the transaction table every couple hrs approximately to ensure that even when you miss a transaction you are able to uncover once the change happened or roll to an earlier condition.
If you wish to observe how a sizable application will it have a look at SugarCRM, they've and inventory management module though I am unsure the way it stores the information.