1

I have a conceptual question.

My DB has a table that stores information about people. One of it's fields is their phone number (8-digit for my country).

The thing is that in some cases, two or more people will have the same phone number.

My question is: will it be a better choise to store the phone numbers on another table and then reference it by a foreign key instead of just storing them as a field?If so, will the result be the same for whatever the size of the DB is?

I don't know if this will make any difference, but the table will have no more than 600.000 - 800.000 records, and I guess the coincident phone numbers will be about 10% of the total records.

EDIT:

-Each record can have up to 4 phone numbers (two lines and two cells)

-Both cases will occur, there will be sometimes where the users will be looking for all the people having a specific number, and times where the user will want to know what are all the phone numbers a person has

Improve this question
5
  • if you have just one phone number per user, you can keep it as is. Commented Feb 24, 2015 at 21:05
  • Sorry I didn't say that, I have editted the question. Each person can have up to 4 numbers Commented Feb 24, 2015 at 21:09
  • 1
    I would ask this question. If a number changes for one user, will that imply the number changes for all users with the same number? If so, I would use a separate table. Otherwise, it seems of little consequence. Commented Feb 24, 2015 at 21:09
  • 1
    if you have more then 1 phone number per person - there is a reson to set new table like: id, user_id, phone, type, description Commented Feb 24, 2015 at 21:11
  • Actually, that can happen, or not. The phone number corresponds to all the people living in a house, so it could change for just some of the group's. Commented Feb 24, 2015 at 21:12

4 Answers 4

Reset to default
1

Technically to be normalized, you would have a separate Phone number table and then a PersonPhonenumber table.

However, in practice I have rarely seen this structure for phone numbers and addresses. For one, it is easy to make a mistake and update more than one person's addess or phone when you only mean to change one. For another it adds an extra join that seems unnneeded to most people. You aren't really gaining much by going to this level other than a small amount of duplication.

The real decider is how you are going to use and update the numbers. If you want to update all the people with the same number frequently, it is better to go fully normalized. If you will usually only want to update one person at a time, it is probably less risky to only have a Person table and a PersonPhone Number table.

If you want history, then I would go with a person table and aPersonPhoneNumber history table. It would have the personid, the phone number, the startdate and the end date. So when John and Mary get divorced, his phonenumber woudl have an end date but hers woudl not and you could clearly see who had the number when.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I get your point, and that's exactly what my question was about. However, regarding the performance. Would you suggest to create two tables (PhoneNumbers and ContactHistory), or just one and store all on it? I'm just worried about the grouth of the contacty history, we would be talking about an increment of about 2.000 records per day. Advice me on that and I'd gladly accept your answer.
WIth good indexing, the performance will likely be ok, but I am not a mysql expert since I am more familar with SQL Server. If you choose to separate history from the current phone numbers, then make sure you populate the history table from a trigger to ensure that all changes are documented. .
1

If you have more then 1 phone number per person

There is a good reason to set new table like: id, user_id, phone, type, description

So type could be list of Home, Work, Office2, Boss, Wife, Fax, Mobile etc...

and description like "work hours only", "evening", "24x7", "Emergency only" etc

If you really manage phone book for your application that is good idea to separate phone numbers from original user table.

Improve this answer

Comments

0

If you have two people with the same phone number you will encounter a problem when searching for a specific phone number. A search for a specific phone number will sometimes return more than one result (10% according to your estimate). If you search by phone number AND by people, you can require all searches for a phone number to include a user identifier (first name, last name, location, etc). It depends on what your objective is.

Improve this answer

1 Comment

Both cases will occur, there will be sometimes where the users will be looking for all the people having a specific number, and times where the user will want to know what are all the phone numbers a person has
0

Usually the phone number is just a number, and without a person has no meaning. So you store it in the Person table.

But it you work for a telephone company for which a phone number has a different meaning and usage (like history, phone lookup, billing) then it should be stored in a separate table. I hope it helped.

Improve this answer

4 Comments

Never use a separator for more than one number. If you will have multiple phone numbers per person, ther should be a related table. I agree withthe rest of what you said but storing comma delimited lists is a sql antipttern.
It did helped, It's not a telephone company but the phones will indeed be used for some other reasons (phone lokup and history will be the most commons). will sepparating them improve the performance significantly?
I agree the separator is a bad idea. For phone look-up you can make an index on the phone number column. For the history it does not work.
It's not a performance issue as much as an issue of very ugly code to split the numbers apart. If, on the other hand, MySQL does not need to look at individual phone numbers (via WHERE, ORDER BY, etc), the you can have a TEXT field with whatever you want.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.