Hi Jim.

Thanks for the response. That is exactly right. Thanks so much for you insight.

Marcia
>Marcia,
>
>If I understand your problem correctly, you want to be able to associate the contact with the region/facility/etc at initial entry.
>
>I have found that I most such problems which I faced were "solved" by providing for the "natural flow of things".
>
>In this case it sounds to me that it is way premature to "link" an initial contact with a 'final roofing job(s)'.
>In the real world the first contact might be the Purchasing guy/gal, who ultimately has nothing to do with an actual site. There could be countless other scenarios.
>Sooner or later, though, if work is to be seriously considered, there will have to be a body from the buying end who will be responsible for making the decision. There may be other bodies who participate in estimates, negotiations, etc. And obviously that action will be relative to particular structures.
>
>So, towards answering your question, I would consider having several types of contacts and I would "link" contacts to regions and/or facilities. I would provide for several contacts (unlimited) for any potential client company and ultimately one of those contacts will be the one "linked" to a particular job.
>
>Good luck,
>Jim N
>
> etc.>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