Pretty wet behind the ears with all of this but I have done my best to watch as many instructional vids and complete real database exercises in order to learn. So here goes.
I work for a company that has both field and office personnel. I am trying to build a database that will track inventory items checked out to either a specific location(field vehicle or office area/desk), or to a specific employee. I would like to show who checked out the item even if it goes to a field vehicle. It will need to keep a current record of items in the warehouse(stored). It will need to store where items come from originally(vendor/distributor). Some items are long use(flashlight, hand drill), others are general use items(printer toner, wood putty). I would like to pull reports as to where certain items currently are, or how many have been checked out to a location/employee during a specific date range. I don't necessarily need to input $value of said items at this time, but it will be easy enough later on.
The list as far as I can tell of said tables should be; employees, locations, vendors and items. A join table should exist between employees and items (transactions).
The main questions I have are:
1) Can the current transactions table also be a join table between vendors and locations seeing as how one of my locations will be the warehouse or should it be a separate table occurrence of transactions linking the two?
2) How would I link the employees and locations tables knowing that I still need to track who "checked" out an item to a location? I should mention that employees are not always in the same vehicle.
I have a good idea on how to handle the calculation fields and portals once I figure these relationships out.
Thank you for any help.