Hello everyone, hope someone can help me with what I am trying to do. Here you go: I work at a company that provides support IP telephony support to clients. I need to create a database to keep track of our business partners and agreements in the USA. Here are the things we have to keep track of, and the entities I've come up with:
Partner_Agreenement
Partner
Partner_Contact
Partner_Location
Client
Client_Contact
Client_Location
Vendor
Vendor_Contact
Vendor_Location
Vendor_Product
Now here is my challenge, the Partner, Client, and Vendor entities all have Contact and Location in common, meaning that a Partner can be in many locations around the USA as well as the Client and Vendor. How would I go about implementing the relationships and dividing the entities in the ER Diagram. Should I create a single entit called Contact and another called Location and then use all the foreign keys from the Partner, Client and Vendor tables into the Contact and Location, or should I have different contact and location tables for the respective partner, client and vendor like I described above, Or should I use entity types and subtypes, and how do I implement them? I have to run reports from this database that answer questions such as: 1) show all the partners, clients, and vendors that are in Texas 2) What partners can support Vendor_Product xyz for a client in Arizona? for this last questions I should be able to look at the client's location and find out what partners are around the area that can service the client. I know this is kinda long, but hope someone has the time to give me some insight on this, I would greatly appreciate it. Thanks!