What is the best way to store phone numbers in the database?

Question

SQL

Number formats can be different, 6, 7, 11 digits.
How exactly
123-45-67 or 1234567
[phone] --- +7 or 8 ?
(In the second case, I guess you can use the country code from a separate table)
varchar is heavier than int 'and

Answer 1

IMHO, the correct answer is as required by consumers (other systems).
You can make several fields:
1 - reference number format varchar2(20 char). For example, 79898112211
2 is the converted number format. For example, [phone].
When adding, give the user a question, do you want standard or non-standard + options for non-standard spellings, etc.
The reference format must be kept. Like a key. And store the converted one so as not to perform the conversion every time it is accessed.

Answer 2

It is best to store numbers in the database in the international format, in the form 7XXXXXXXXXX (only numbers) in the Decimal type (if mysql is used). With this approach, you can output numbers anywhere in your application in a single format, as well as ensure correct search and filtering by numbers.

Answer 3

You can save the number as a number, and format it on the application side when you select it.

Answer 4

varchar separated by city code (operator) and the number itself

Answer 5

What about delimiters, is it better to just remove them?
out of 8 (912) 12
will be 891212
Just then we lose one thing.
Maybe the user wants his number to be seen as:
Gentlemen, how do you all look at this?

Answer 6

If the phone is given to the user, then it's better like this [phone], if in Excel, then it's better to add something before "+7", for example "t" or "_". It looks like this _+7 [phone] or like this t+7 [phone]
For normalization, it is more convenient to store 9169182486 in 10-digit form (if the base is ONLY by phones in Russia and / or Kazakhstan). For other countries, you need to look at what options may be and analyze.
It is a little more difficult to remove doubles from the database (simultaneously in the "phones" cell of one company: 916918-2486 and 8 [phone]) or "broken" phones (when the phone must be 10-7-6 digits in this country-city, and it is, for example, 4-5 digits.It is better to deal with this programmatically, for example, using Python.

Answer 7

In principle, the best, slightly idealized option is to separate the country code, area code and the phone number itself. Well, at the level of countries and zones of countries to store masks.
Then you get a universal scheme that can show numbers in the format familiar to users by mask and is not littered with the specifics of inter-zone, long-distance and international codes.
That is, for the Russian Federation [phone], [phone], + 7 (876-55) 5-25-25, and the same numbers for the German will be 007 900 1000000, 007 876 5229999, 007 876 5552525,
and for Ust-Uryupinsk - can be as follows:
8 900 100-00-00, 8-2 22-99-99 and 5-25-25
although in the database it will be 3+ fields
in principle, it makes sense to store it as an int, but for the "tricks" of searching by partial masks, just keep the calculated fields where the number is cast to [n]varchar