mysql datatype for telephone number and address

Question

I want to input telephone number in a form, including country code, extension

create table if not exists employee(    `   
      country_code_tel   int(11),
      tel_number         int(10),
      extension          int(10),
      mobile             bigint(20)
);

If tel_number is larger than 15 bit, which datatype can I use, I'd better use Bigint(20)?

create table address(
      address           varchar(255),  
      city              varchar(255),
      country           varchar(255),
      post_code         int(11)
);

For example, if I have a country code for Canada I can use +2 or 002. Which is better for processing?

Thanks for your advice.

Answer 1

Store them as two fields for phone numbers - a "number" and a "mask" as TinyText types which do not need more than 255 items.

Before we store the files we parse the phone number to get the formatting that has been used and that creates the mask, we then store the number a digits only e.g.

Input: [phone]
Number: 01234567890
Mask: (nnnn)_nnn_nnnn

Theoretically this allows us to perform comparison searches on the Number field such as getting all phone numbers that begin with a specific area code, without having to worry how it was input by the users

Answer 2

varchar or text should be the best datatypes for storing mobile numbers I guess.

Answer 3

If storing less then 1 mil records, and high performance is not an issue go for varchar(20)/char(20) otherwise I've found that for storing even 100 milion global business phones or personal phones, int is best. Reason : smaller key -> higher read/write speed, also formatting can allow for duplicates.

1 phone in char(20) = 20 bytes vs 8 bytes bigint (or 10 vs 4 bytes int for local phones, up to 9 digits) , less entries can enter the index block => more blocks => more searches, see this for more info (writen for Mysql but it should be true for other Relational Databases).

Here is an example of phone tables:

CREATE TABLE `phoneNrs` (   
    `internationalTelNr` bigint(20) unsigned NOT NULL COMMENT 'full number, no leading 00 or +, up to 19 digits, E164 format',
    `format` varchar(40) NOT NULL COMMENT 'ex: (+NN) NNN NNN NNN, optional',
    PRIMARY KEY (`internationalTelNr`)
    )
DEFAULT CHARSET=ascii
DEFAULT COLLATE=ascii_bin

or with processing/splitting before insert (2+2+4+1 = 9 bytes)

CREATE TABLE `phoneNrs` (   
    `countryPrefix` SMALLINT unsigned NOT NULL COMMENT 'countryCode with no leading 00 or +, up to 4 digits',
    `countyPrefix` SMALLINT unsigned NOT NULL COMMENT 'countyCode with no leading 0, could be missing for short number format, up to 4 digits',
    `localTelNr` int unsigned NOT NULL COMMENT 'local number, up to 9 digits',
    `localLeadingZeros` tinyint unsigned NOT NULL COMMENT 'used to reconstruct leading 0, IF(localLeadingZeros>0;LPAD(localTelNr,localLeadingZeros+LENGTH(localTelNr),'0');localTelNr)',
    PRIMARY KEY (`countryPrefix`,`countyPrefix`,`localLeadingZeros`,`localTelNr`)  -- ordered for fast inserts
) 
DEFAULT CHARSET=ascii
DEFAULT COLLATE=ascii_bin
;

Also "the phone number is not a number", in my opinion is relative to the type of phone numbers. If we're talking of an internal mobile phoneBook, then strings are fine, as the user may wish to store GSM Hash Codes. If storing E164 phones, bigint is the best option.

Answer 4

Consider normalizing to E.164 format. For full international support, you'd need a VARCHAR of 15 digits.

See Twilio's recommendation for more information on localization of phone numbers.