Database design - similar contact information for several objects

Question

I understand that the answer to these types of questions is often "up to me," but I’m still wondering what common consensus there may be.

I deal with several objects, such as

• Company
• Charity
• Auditor
• Stocktaker

etc. etc.

They all have contact information such as email, phone and address.

Two design methods that I thought to preserve contact information were

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

• 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: the database should have only one address, phone and mail table, and all phone numbers, addresses and emails for each type of object are stored in one place. Disadvantages are the creation of many associative tables

Method 2) Create a separate contact table for each company, charitable organization, auditor and accountant

• dbo.Company → dbo.CompanyContactAddress
• dbo.Company → dbo.CompanyContacttelephone

• dbo.Company → dbo.CompanyContactaddress

• dbo.Auditor → dbo.AuditorContactAddress

• dbo.Auditor → dbo.AuditorContacttelephone
• dbo.Auditor → dbo.AuditorContactaddress

The advantages of this are easier to implement and maintain. Disadvantages - contact information is stored in several places on the database.

If anyone has any other ideas, it will be very appreciated.

Thank you very much

Answer 1

You are right when you say "it depends." It depends on what your data will be used for OLTP, you would look at the normalized design and reporting system that you would like the data to be abnormalized using contact information in accordance with other data components.

A normalized database may also discuss the level of normalization. Some will say that the contact information is grainy, as in the first scenario. I like the middle of the road. I would get all the contact information in one table, which included the address, phone 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 simply adding the ContactID table to the company and avoiding a separate relationship and join.

You can also implement a table with ContactTypes .

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

You can then specify this in the CompanyContact table and remove the relationship requirement. Although it matches your scenario of 1 contact for each type, it leaves no room for growth.

Answer 2

I like your method 2 better, but it still seems like too much work. Why not just have a table PhoneNumber, Address, etc., which stores ALL addresses, phone numbers, whatevers, and then refer to what the company, the auditor, etc. ?? I probably would have done it.

Answer 3

You can use one table for all and use data types as shown below.