Multiple Locations

[stonelady]

I'm setting up a database to track inventory, production and spare parts for
several locations.

I am trying to figure out the best way to handle it.

I want to have each site enter their information, but I want them to be able
to search through all sites (for instance if a site is looking for 150hp
motor, they want to be able to search all sites)

How would I best set this up? Should there be a separate database for each
site, or should I make one of the keys be the "site"?

Thank you,

 

[Allen Browne]

You need 3 tables:

Site table (one record for each location, with a SiteID primary key)
Product table (one record for each product, with ProductID p.k.)
SiteProduct table (for recording quantities of products at sites.)

The 3rd table will have fields like this:
- SiteID which site this is
- ProductID what product this is
- Quantity how many of this product at this site.

That's just to record the current quantity of each product at each site. You
need more tables if you are trying to record purchase orders, deliveries
from suppliers (whole/partial/back orders), orders received from clients,
and actual shippings to clients.

 

[stonelady]

Thank you for your assistance. Sorry it took so long for me to respond, and I
hope you check, as I have one question,
What would be the PK for SiteProduct table?

Thank you again.

 

[Allen Browne]

You could use the combinatikon of SiteID + ProductID as the primary key if
you want to ensure there can be only one entry for a product at a site.

Or add a SiteProductID autonumber if you prefer.