I am in the process of building an inventory/asset management solution for a state organisation. (no prices etc involved). It is actually a distribution network of items. From Department to Users and back after usage is completed.
The basic processes are:
a) Receive new items
b) Loan items
c) Receive lent items back
d) Write-off items (e.g. consumed items)
e) Change items location
The table structure is like this :
-User initiates a new process and populates process lines by selecting available items from Inventory table.
-A script populates a Transaction Table and updates the Inventory Table where total quantities are kept
In Inventory table a record represents a group of items sharing the same value in both three fields :
c) Serial Number
So item qty is grouped for same Item/LOT/Serial Number.
How should I handle items in case for example, I have given a Loan of 20 Steel Wrenches (no LOT or Serial Number) and when the user returns them, reports that 3 of them malfunction,
How should I store that information?