Logical table setup assistance please
I'm just looking for some input on the best way to set up a foreign key.
First, here's my tables:
products (the main product list)
myequipment (Instances of each product. FK to company to show ownership. Contains rates, dollar value, FK to product)
rates (log of equipment rentals. FK to renting company. FK to location_lookup_id to indicate to which jobsite equipment will be located.)
location (a physical address, a "jobsite" if you will)
location_lookup (an instance of a location. FK to company to allow name to show up in drop down list per company. FK to location. Name to allow assigning a nickname.
Now...my question. At this point, I only have one piece of equipment in the rates table (just for testing). I'm trying to determine what the best way to assign a location to a piece of equipment (product). Now, a product can only be in one place at a time, BUT it would be nice to have a log of where the equipment went during it's time away from it's owner, although that's not super important. What IS important is knowing who has the equipment at any one time. So, I'm wondering if I should just assign a FK in the myequipment table. That way I can indicate where a product is at any time. Since it can only be in one place at a time, this makes sense. However, I'm wondering if a new table might be the best route to take. For example:
Thoughts? Alternate ideas?
thanks in advance.