Companies with Multiple Locations and DB Design

[wmugrad28]

I've been designing a database PT for several weeks now. Some of our customers have two (or three) locations in the same town right near each other. Each location has it's own mailing address. So the city, state, company name are the same... just that one might be "Plant 1" and the other "Plant 2".

How should I set things up to be able to combine the two at various points in the database? For example, we might want (on one screen to view or print) a list of all equipment that the customer owns, both by seperate plants or together. It this going to be a lot of extra work?

Greg

[transpower]

You could have a company code and a company location code, so each item would have two codes. You could then have report layouts based on one or the other or both.

[Ender]

Ideally, you would have a Location table linked to Company by CompanyID. This would be where the Address is stored for each location of each Company.

For Invoices, you would then specify the Location to ship to. This can be done with a conditional value list, showing just the Locations for the current Company.

Now with the Location information tied to the Invoice, you can use that as a Sub-Summary part if you wish, breaking things down by Location.

The Business Tracker database on filemaker.com has a good implementation of this model, though I don't remember if it has sub-summary reports by Location.