1 of 1 people found this helpful
I notice that your employee and location info are described as separate tables, but you intend to use them either interchangeably, or, possibly, as attributes/modifiers of each other.
jamesonelam wrote, in part:
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.
That tells me that the employee/location info will be most efficient is those peices of info are separate fields in the same record table.
From your description so far, this is the simplest structure I picture meeting your needs:
Vendor -< Items -< join table of items to employees (with location attribute for employee at this instance) >- Employees
Obviously, you will need date_out/date_in info in the join table. You can then run reports on the join table for items checked out but not yet returned for current location/employee.
A join table (or more) will be necessary but I think there are some things you need to consider...
Will you have more than one flashlight? What happens when it dies or is stolen?
Do you have an inventory where stock is received from outside the system...
Some things will check out permanently and some will be returned... to where?
What about when something gets checked out to a vehicle... doesn't someone do that? Isn't someone responsible?
I know you have considered these things but to spell them out is to define what specific relationships should occur.
Your 'join table can have many functions so it is a mistake to make the base table connect directly to the employees or items.
Just say we call this table "Transactions". You might have Transaction_Contact as one relationship to Contacts... and Transaction_Item as a relationship to Items. Each would have an ID to make the match... with the ID_Contact and the ID_Item fields in Transactions... providing the ability to see the information from Contacts and Items from the context of Transactions as well as visa versa.
Next... there are some status and condition things which can be determined by Finds or by setting up other relationships...eg:
Our Flashlight must be returned... so in it's item record there must be something to reflect that... like a checkbox "must return" or such.
Our Ream of A4 paper is not expected back so it would have that checkbox empty.
Within Transactions you might have a field which calculates the status of an item. The item is CHECKED OUT on a DATE and TIME and as it is marked "must return" in it's item table the auto-enter status might be "CHECKED OUT". When that item is returned the Status can be marked as "RETURNED" (perhaps a check-button with a script attached) and the RETURNED DATE and RETURNED TIME are auto entered from the script. Similarly the status could auto-enter the value of CONSUMED when an item is not marked as "must return".
THAT gives you another field for matching for relationships. Within Transactions you can set up a global field which has a popup with a valuelist attached with CHECKED OUT, RETURNED and CONSUMED as options and a SELF-relationship can be created by matching the global field to the status field in the Transactions and call it something like "Transactions by Status". Using that relationship you can view the data via a portal or gotorelatedrecords() in a sorted list to give you the summaries.
For Consumables you might want to add a Quantity so that you can track the amount of paper an employee uses if you suspect they might be taking it home LOL...
I hope this helps you delve into the detail a little better...