Company and multiple addresses DB design

[deekay]

At the moment I have simple Access DB with 3 tables Companies,
Contacts and Activities.

In the Company table we have stored the address fields
Address
Street
Suburb
City
Code

Company ---> Contacts ---> Activities (1 ---> many relationships)

This works fine for 97% of contacts on the database.
A problem happens when a contact has a different address from the one
in the Companies table.
For example, a contact is based in one city but the head office of the
company is in another.

What is the best way to handle this?

Move the addresses to the contact table so address is per contact?
Move the addresses to a address separate address table and relate to
the company so that company has multiple addresses?
Move the addresses to a address separate address table and relate to
the contact?

Also something to consider from the user perspective.

At the moment interface used is a main form in datasheet view so a
single row would for be.

Company name, Job title, Salutation, Fname, Sname, Address, Street,
Suburb, City, Code...

Where Company and the address fields are in the company table but the
other fields in the contacts fields.
A user uses this main form and to add a new contact would add a new
row by putting the company ref_no into the query into a new row which
would then fill in company info like name and address automatically.

Please advise considering the interface as well as db design.

 

[Allen Browne]

Ultimately the call will be yours, but here's an idea you may not have
considered.

Take a look at this page:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

The core concept is that companies and contacts all go into the one "client"
table, and you then connect the contacts with the companies. With this
structure, you can either:
a) Put the address fields in tblClient, or
b) Create a related table of addresses with a ClientID foreign key.

You would use (a) if any company/contact only had one address, and (b) if
any one company/contact may need multiple addresses. But either way, the
companies and contacts have addresses of their own (not merely a company
address, nor merely a contact address), and yet the addresses are in a
single table.

The article also discusses how to interface companies and contacts. If you
choose (b) - a separate address table - you will end up with a main form for
companies and a subform for the addresses. Simiarly, you have a main form
for contacts, with the subform for addresses.

Hope that helps you decide.