Your inventory database can become a drifting liability if the events in it are not fully reconciled with physical operations.
A receiving operation creates receipts in one database, a transfer in another, a return processed elsewhere – and what all gets counted as inventory may have little connection to any of these numbers.
Applications which interact with physical inventory should focus on events as the primary mechanism.
- Model events rather than quantity
You probably had an idea for a simplistic inventory which only has a SKU and current quantity.
The mutable value suggests that this inventory may change, however, there is not enough information recorded about how exactly the quantity changed.
A preferable way of designing the inventory is to capture the events which affect the quantity:
RECEIPT
PICK
TRANSFER_OUT
RETURN
DAMAGE
ADJUSTMENT
Make sure that the events know which product, quantity, and any applicable warehouses are involved, what time the event was done, who was responsible, and what transaction id, if any, is involved.
One particularly important case here is a transfer between warehouses, where the movement from one is effectively canceled into another in two separate operations.
Both of these actions should bear the same transaction id to allow for reconciliation.
- Transaction handling is idempotent
Devices used in warehouses can sometimes be offline or experience connectivity issues.
Operators can mistakenly resubmit the same scanning results if they do not get feedback that a certain item has been successfully processed.
Integration platforms can also malfunction and replay the same requests, leading to the same transaction being submitted multiple times.
An inventory should account for these issues by being able to accept the same transaction multiple times without resulting in extra quantities of product being available.
One way to do this is to allow a unique id or an external key to be specified with every request.
If an identical transaction is attempted again, the application can find that it has already been processed and skip it.
An alternative is to specify such constraints in the database itself.
- Do not allow concurrency errors
It is possible that the very last unit of a certain good will be picked by two different operators simultaneously.
The application might read the available quantity just before either operator submits their request.
The database may be instructed to allow both orders, since at the moment of creation, both picked quantities are available.
Depending on the database and the application architecture, this may be prevented with eitherlocking, row-level locking, or through a conditional atomic update.
The most important detail is ensuring that reading the quantity and making a change to it is a single atomic operation.
- Available quantity is a derived value
A certain quantity of goods may effectively be unavailable for picking or allocation.
They may have been set aside, damaged, or simply in the process of being received and counted for quantity inspection.
The application needs to make a distinction and treat these goods differently from those which are immediately available for shipping or consumption.
A particularly useful practice here is to make a distinction between a general ‘on hand’ quantity, and other derived values.
Available = On hand − Reserved − Unavailable.
- Always reconcile events against physical operations
No matter how good the application is, it will still have errors.
Cycle counts and reports on exceptions which the application generates can be useful for making manual adjustments.
Those adjustments, however, should make new events in the same event queue as any others.
This will keep the reconciliation process consistent.
- Ensure proper scanner and application inventory integration
Barcode or RFID devices can generate identification events, but the application itself needs to manage the validation, deduplication, reconciliation process, and database mapping.
There should be some validation or de-duplication routine before the value is even considered to be entered into the database as inventory.
Testing is also essential when considering a new application in this space.
Various edge cases, such as disconnections, multiple submissions, partial transfers, stalled transactions, and concurrent reservations should ideally be tested.
For those seeking more information on different forms of inventory management solutions, including RFID or barcode scanning platforms, and general application support, The Inventory Master features a comprehensive list of solutions.
Event-based inventory software design guide
Quantity management applications tend to fail in specific ways depending on what assumptions are made.
A reliable system should make events the first differentiating factor, build transactions around them, and ensure that any concurrency problems are resolved.
Top comments (0)