MySQL database column to Store Phone numberss

Question

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.

Answer 1

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.

Answer 2

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.