How to share One-To-Many table

Question

I am trying to create a database to hold my address book but I've ran into a slight problem. My database is supposed to hold individual contacts along with company contacts. That is an entry can be either for an individual or a company. As you know both individuals and companies have addresses and I want a One-To-Many relationship between individuals and addresses as well as companies and address.

So an individual can have many addresses and a company can has many addresses

the problem is when trying to design the schema I am not sure what the best approach here is. Should I put two foreign keys in the address table, one for individual_Id and one for company_Id or should I create a linking table for each individual to address and company to address relationships.

The foreign keys do sound easy but don't know if that's the right approach, and linking seem like the right approach but don't know if this is overkill as they are mostly used for Many-To-Many relationships.

Thoughts?

Answer 1

There are various approaches to this...The approach we took was to have a "Names" table, the equivalent of your contacts table; an addresses table, and a phone numbers table.

The addresses and phone numbers tables each had a foreign key to the names table. The names table had a "type" field in it so that we could tell if the name was that of an individual, company, sole proprietor, etc.

The phone numbers table had a type field in it also, so we could tell whether the phone number was Work, Home, Cell, etc. In addition, we allowed the user to store a copy of one of the phone numbers in the Name table to serve as a Primary phone number.