Database design - Similar Contact Information for multiple entities

Question

Hello.

I realise that the answer to these types of questions are often "it depends" but still I wondering what the general consensus might be.

I am dealing with multiple entities such as

1. Company
2. Charity
3. Auditor
4. Stocktaker

etc etc...

Which all have contact information such as e-mail, telephone and address.

The two design methods I was thinking to store the contact info were

Method 1) create role tables between the contact tables and company, charity, auditor and stocktaker.

• dbo.Company -> dbo.CompanyAddress <- dbo.Address
• dbo.Company -> dbo.Companytelephone <- dbo.telephone

• dbo.Company -> dbo.Companyaddress <- dbo.email

• dbo.Auditor-> dbo.AuditorAddress <- dbo.Address

• dbo.Auditor-> dbo.Auditortelephone <- dbo.telephone
• dbo.Auditor-> dbo.Auditoraddress <- dbo.email

Advantages, there only needs to be one address, telephone and email table in database and all telephone numbers, addresses and emails for each entity type are stored in one place Disadvantages are it creates a lot of associative tables

Method 2) Create a separate contact table per company, charity, auditor and stocktaker

• dbo.Company -> dbo.CompanyContactAddress
• dbo.Company -> dbo.CompanyContacttelephone

• dbo.Company -> dbo.CompanyContactaddress

• dbo.Auditor -> dbo.AuditorContactAddress

• dbo.Auditor -> dbo.AuditorContacttelephone
• dbo.Auditor -> dbo.AuditorContactaddress

Advantages of this are easier to implement and maintain Disadvantages are contact details are stored in multiple locations across the database.

If anyone has any other ideas it would be much appreciated.

Many thanks

Answer 1

I like your method 2 better, but it still seems too much work. Why not just have a PhoneNumber, Address, etc... tables that store ALL addresses, phone numbers, whatevers, then reference that from the Company, Auditor, etc...? It's probably how I would have done it.

Answer 2

You could use a single table for all and use data types like below.

Answer 3

You are correct when you say "it depends". It depends on what your data will be used for OLTP you would look at a normalized design, and a reporting system you would want the data de-normalized with the contact information inline with the other data components.

In the normalized database, the level of normalization can also be debated. Some will say to have contact information granular like you have in your first scenario. I like to go "middle of the road" I would have all contact information in one table, which included address, telephone and email.

Contact
ID, Address, Address2, City, State, Zip, Phone, Email

Then create a relationship with a separate table

CompanyContact
ID, CompanyID, ContactID

This too could be integrated into the company table, by just adding a ContactID to the Company table and avoid the separate relationship and join.

You could also implement a table with ContactTypes.

ContactType 
ID, ContactType
1, Company
2, Charity
3, Auditor
....

Then you could specify that in the CompanyContact table and remove the need for a relationship. Although it fits your scenario of 1 contact per type it does not leave room for growth.