Allocation of Telephone Numbers, Emails & Faxes

[Garyeag]

I am designing a database to hold details of people we have used for research and people we may want to use in the future. Their contact details are very important to us.

I have studied normalization, 3NF etc, and the arguments against strict implementation of it. My question is, what is the best way to deal with the phone numbers for the people I want to hold records on?

One idea I had was to group all their "contact details" together, so I had:

RESPID
H_Phone
W_Phone
M_Phone
Fax
E_Mail
Best (the preferred contact number).

My difficulty is that many of the people on the database are their because of their job e.g. IT Managers. For these people we don't have a home address and phone no. Further, it is not inconceivable that in the future the database will hold records of people who don't work and therefore W_Phone is irrelevant to them.

I choose the first grouping based on logic. Should I normalize the data formally/differently? Should I put H_phone in the table with home addresses; W_Phone in with job details, M_Phone in with personal details, and Fax and email whereever.
I can't work out the relative merits, apart from when I try to model the data with contact details grouped together it gets tricky because some belong to work and some to the individual.

I would be grateful for any insight anyoone can give me on this.

 

[JerryDennison]

You've come to the age old question. How far should I take my 'normalization'? In it's strictest sense you would create a table for the entity Phone. It could have a many-to-many relationship to Contact. But in my mind, this would overly complicate your particular needs.

I like to look at it this way: Do my users need to have flexibility with these attributes or do they need to track these attributes usage over time?

In your example: If you give them hard coded choices for capturing phone numbers will this leave a large number of empty fields in your db because you must allow for every contingency? Will the users be hampered because they have no place to store new types of phone numbers that you haven't allowed for? If the answer is basically no to these questions then you're ok using this approach. In the one sense you are repeating groups but in another sense you're not.

Now to the short answer that you're looking for. Based on your example I would recommend that you have a table for communication information. Your people table would have a 1-many relationship to their communication information. Structuring it this way allows you to capture any number of ways to contact the people and utilize the db more efficiently.

 

[Garyeag]

Thanks for that. It was a struggle with this normalization lark at first and it helps to have some feedback on the pros and cons.

With regard to the communication numbers I am building in a set of assumptions that one person can only have one of each type of phone, fax or e-mail. Nowadays, there are many people who have many email accounts, small businesses might have 2 or more lines and so on.

One of our objectives however is to eliminate unnecessary calls by telephone recruiters. We don't need to track the usage of these numbers, we are including them as a means to contact the individual. And the more direct the number the better. This is important because on high profile projects the recruiters may spend a lot of time negotiating with switchboard and personal secretaries.

Initially, the users wont be able to amend the fields, only a few keys administrators will. The way recruiters will be using it is that they will have lists with names and numbers of people to contact.

I noticed a method some programmes are using where they allow the inputting of a number, followed by another field which indicates what that phone is associated with: i.e. a fax, office, home, mobile. I have rejected this temporarily because of the extra work and because when users do start using it there is a low level of IT skills and I think to many drop down boxes everywhere are going to get confusing eventually.

Anyhow, thanks,