1 Reply Latest reply on Feb 10, 2014 6:41 AM by philmodjunk

    Inventory Management Plan

    AdieWebster

      Title

      Inventory Management Plan

      Post

           OK, here is the problem, and my first attempt at a solution. I seek guidance on if this is the right approach. As yet I’ve not even turned on the computer smiley

            

           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:

            

           Table 1:

           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).

           Table 2:

           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.

           Table 3:

           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.

        • 1. Re: Inventory Management Plan
          philmodjunk

               I'd consider treating Disposal as just a special kind of transfer and thus recordable in Table 2.

               

                    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?

               It's easy to add a field that records the account name used when the record was last modified. Tracking which field(s) in that record were modified and how would involve quite a bit more effort.