Im trying to design a rental inventory system for our company. I have a few issues that I can not figure out how to solve.
1) When booking gear, I want to book it by product ID, but when signing them out (with bar/QR-codes), I want to track serial numbers. When something is lost or broken I want to be able to check where that piece of gear have been used.
2) We have several different models for some gear. On most jobs it might not matter which is used but on a few it might. (Made up example: "network switches", might be a group and on most jobs you might just need a few generic ones, but on some jobs you might need this specific Cisco switch since it has a special feature)
I was thinking:
Inventory -< Transactions >- Rental Orders
Inventory table (table of individuals):
Serial number (maybe about 50 per Product ID)
Product ID (maybe 3-5 per group)
Transaction Type (Bought, Sold, Rent, Broken, Lost)
But this would force me to choose individual serial numbers while booking, but if I only track serial number in the transaction database, I still need at least a lookup table of serial numbers somewhere to connect them to products.
The second problem would be (if we fix that), If I plan an order where I need 7 generic network switches (4 or 16 ports). But at the same time we need for another order 3 specific switches (16-ports). If we have 10pcs 4-port and 5pcs 16-port. Order. 1 can not during checkout pick any 7 generic, it has to pick max 2 of the 16-port otherwise order 2 wont have enough, the reservation need to change from group to specific products.
Am I trying to do this completely wrong?