Is it better to store the phone in three fields or as one?

Question

I am struggling with the decision to separate phone numbers stored in a MySQL database.

One school of thought should tear out a phone like:

• area code (123)
• prefix (123)
• suffix (1234)

Another is to simply put the file in one field with any formatting that is considered appropriate:

• 123456789
• [phone]
• 123-456-7890

My initial reason for thinking first would be better if you could quickly and easily collect statistics based on the phone numbers collected from our members (for example, the X number of members has a zone code of 123).

Is there really a “right” way to do this? I really understand that when paired with PHP, I can get and reformat any way I want, but I would like to use the best practices.

thank you for your advice

EDIT

For now, I will only store North America numbers

Answer 1

I will vote for one field, processing the data when you put them so that it is in a known format. I tried both methods and the single-field approach seems to generate less code overall.

Answer 2

You want to save it in the most efficient way in the database, precisely because it is so easy to reformat it in PHP. Go for a full field without separators (1231231234), as that would be a better way. If you have international phone numbers, add the country code. Then in your code, you can format it using regular expressions to see how you want.

Answer 3

I would save the phone numbers as strings, not numbers.

Phone numbers are identifiers that use numbers. Phone numbers starting with zero are valid, but can be interpreted as octal in the programming language.

Divide the phone number only by numbers and save the extension in a separate field.

This will allow uniform formatting later.

For the United States, divide a digit up to 1 digit (and determine the formatting based on the length of the string (10 digits for the USA)).

Answer 4

I am creating a callcenter application (it manages contact information queues for a group of distributed subscribers for communication), and the architecture indicates one field, spaces, dashes, etc. After quite a bit of analysis, I agree that this is best.

Based on the variability of the input for phone numbers (apostrophes, periods, dashes, and combinations of each), I built a simple function that deals with the user record, cutting off everything except the numbers themselves, as well as a "perestroika" that reformats the original number into something more attractive to the user.

Since they were useful to me, here is what I wrote so far:

public static function cleanPhoneNumbers($input) { return preg_replace("/[^0-9]/", "", $input); } public static function formatPhoneNumbers($phone_number) { if(strlen($phone_number) == 7) { return preg_replace("/([0-9]{3})([0-9]{4})/", "$1-$2", $phone_number); } elseif(strlen($phone_number) == 10) { return preg_replace("/([0-9]{3})([0-9]{3})([0-9]{4})/", "$1-$2-$3", $phone_number); } else { return $phone_number; } } 

Some caveats. My application is not available to international clients right now (there is a voip application built in that we don’t want to allow outside the US right now), so I did not find the time to configure for international opportunities. In addition, as this happens, I will most likely return to the refactor and support these functions later.

I have found one weakness so far that has been a little sick for me. In my application, I must prohibit calls that should be made according to the time zone depending on the time of day (for example, do not allow someone on the West Coast to call at 6:00 in the morning, when he is at 9:00 in the east). do this, I have to join a separate area code table to my table with phone numbers by comparing 3-digit area codes to get the time zone. But I can’t just compare the zip code with the phone number field because they will never match. So, I need to deal with extra SQL to get only the first three digits of the number. Not a game changer, but work and bewilderment nonetheless.

Answer 5

Definitely store them in one field as a text string and store only numbers. Think of it this way; no matter what the numbers are, its only one phone number. However, segmenting numbers depends on a number of things (locality, the number of numbers provided, even personal preference). It’s easier to save it and change it later with text manipulation.

Answer 6

I think splitting a number in three fields is the best option if you want to use area codes as filters, otherwise you should use only 1 field.

Do not forget to use ZEROFILL , you plan to store them as numbers;)

Answer 7

it really depends on a couple of factors:

• Is it possible that you will have international numbers?
• how much code / code of city or city code will you make?

Regardless of the fact that I will only store numbers, it's easy enough to format in either MySQL or PHP, and add brackets and dashes.

If I were not going to keep a regional code search log, I would just put the entire phone number in one field, since I assume that most of the time you will still get the whole phone number.

If it is possible that you will accept international numbers in the future:

You might want to add a country field, however, you won’t have to guess what country they are in when dealing with a number.

Answer 8

What you use depends on how you plan to use the data and where the program will be used.

If you want to efficiently search for records by regional code, separate the area code; queries will run much faster when they perform simple string comparisons and string manipulations with the full phone number to get the area code.

HOWEVER, keep in mind that phone numbers formatted by XXX-XXX-XXXX are found only in the US, Canada, and other small Caribbean territories that are subject to the NANPA system. Different regions of the world (EU, Africa, ASEAN) have very different numbering standards. In such cases, separating the equivalent of the “area code” may not make sense. Also, if all you want to do is show the phone number to the user, and then just save it as a string.

Whether to store a room with a format or not, these are mostly personal preferences. Saving a raw number allows you to easily change the formatting; you can go from XXX-XXX-XXXX to (XXX) XXX-XXXX by changing a couple of lines of code instead of reformatting the 10 million numbers that you already have. Removing special characters from a phone number is also relatively simple Regex. Saving without formatting will also save you a few bytes per number and allow you to use a fixed-length field (while storing additional data overhead inherent in varchars). This can be useful in a mobile application where storage is premium. However, this 5 terabyte distributed SQL cluster in your server room will probably not notice much difference between char (10) and varchar (15). Saving their formatting also speeds up data loading; You do not need to format it first, just pull it out of the database and stick it on the page.