Multiple Locations

S

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,
 
A

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top