1

What is the best approach to store Phone Numbers (Mobile Number and Office Number) in MySQL table?

Users may have MobileNumber (CountryCode and MobileNumber) and Office Number (CountryCode, AreaCode and PhoneNumber).

My application will primarily target an Indian Market and will grow to other markets as well. I prefer to have varchar columns for storing Phone Numbers.

But should I have 3 columns for Office Phone and 2 Columns for Mobile Phone? Or 3 Columns for all kinds of phone numbers with Area Code as Optional?

I have seen similar question here but no answers yet.

1
  • 1
    Some countries do not use area code. The whole country is one area without any special code (or maybe we can use the empty string for that ;) Oct 25, 2015 at 8:13

2 Answers 2

2

I would recommend the 3 column solution with area code optional. It will be able to handle cases of countries where mobile numbers do have area code (like the U. S.) and will be much easier to maintain and service.

1
  • Thank you @Juan-Carlos-Coto for your input. 3 columns with varchar is much better.
    – Prabhat
    Oct 2, 2015 at 13:50
3

Do you really need to split the number into its parts? Usually the only use for the number is for someone making a call. So, I suggest a single field for a number.

Mobile vs office vs home vs fax vs ... -- Build a table of phone numbers. Clearly this problem is 1-to-many. (1 person to many numbers). That table would have 3 columns: user_id, type ENUM('mobile', ...), number.

Now, for the number itself. Do not default to utf8. Instead, use

VARCHAR(40) CHARACTER SET ascii

(No, I don't know if 40 is sufficient.)

Extensions -- What about 1-800-123-4567x9876 ? Well; maybe that syntax is ok.

Dashes. Please, please, do not force people to remove the dashes (or add parens or whatever) when they enter the number. Cleanse the string yourself if necessary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.