Hi all.

I have been working on a small app for the sales force of our roofing division and am having some problems designing my database. The app performs some contact management functions and also creates a roof analysis report that the sales reps generate for potential customers. The data is organized as follows:

Clients have 1 or more regions.
Regions have 1 or more facilities.
Facilities have 1 or more roof areas with various construction details and conditions.
Roof Areas have 1 or more recommendations assosiated with them (e.g., repair, replace...and an estimated cost)

The above info is used to generate the roof analysis report. Now, here is my problem. I have a contact table that contains info about the various people at the client site that the rep is talking to. That contact can be located at either the corporate office, regional office, or one of the other facilities. When the contact is first entered in to the system, the only thing the sales rep may know is the contact's name and telephone number and the name of the company that is being solicited for roofing business.

Any ideas on the best way to associate the contact with the particular location (corporate, regional, facility)? I thought of introducing a location table with the actual addresses, and use the PK of the location table as foreign key in contact and client/region/facility, but the addresses might be entered at a much later time and the contact needs to be associated with the company from the beginning. Also, I think there would be some other problems...like what do I do when the sales rep deletes a company? Do I get rid of the contact and the address?

TIA

Marcia