Inventory Management Plan
I need to create a database which tracks an inventory of equipment on our account. That equipment is likely to be loaned out and returned multiple times. I also need to check the equipment as a register, so I know what is where at any time.
My approach is a relational database as such:
This is the equipment details. I envisage it having fields for:
UniqueID, a category (cat1, cat2, cat3 etc..), equipment type, eqpt colour, eqpt weight, equipment serial number (cant be UniqueID as some equipment serial numbers are not all numbers!), accountable class (Y/N), remarks, Which account it is on (Company, loan or OFF account), owner name, owner address, owner reference. (The owner might be us, it might be someone we borrowed the equipment off).
This would be the transfer data. Each equipment above can have multiple transfers:
TransferID, Eqpt FK, Loaned to name, loaned to address, loaned date, loaned by name, loan remarks, returned from name, returned from address, returned date, returned to name, returned remarks.
This would be the disposal data
Each equipment can be disposed of once. (Not sure if this should be in the equipment table then).
DisposalID, EqptFK, Disposed off date, to name, to address, by name, reference, remarks. I would also like a container to add a pdf copy of the disposal document.
I also need to ensure that the Account in Table 1 cannot be set to 'OFF account' unless the disposal data fields are complete.
So, I’m thinking either:
Table 1 to table 2 as a 1 to many relationship
Table 1 to table 3 as a 1 to 1 relationship
Or combine table 1 and 3 and make this to table 2 as a 1 to many relationship.
I would also like to get the database to generate a loan form (serial numbered) when the equipment is transferred or disposed off. And a check list of accountable equipments for use during a stock check.
Total accountable equipments will number less than 100.
Now, pushing my luck a bit, is it difficult to have multiple log ins into the data base, and it record a history of who makes what amendments and when?
I welcome any advice if I’m on the right track or not, o any other suggestions you guys might have.