mysql datatype for telephne number and address

Question

Hello,

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 i have a country code for Canada, i can use +2 or 002. Which is better or processing?

Thanks for your advice.

Answer 1

Actually you can use a varchar for a telephone number. You do not need an int because you are not going to perform arithmetic on the numbers.

Answer 2

i would use a varchar for telephone numbers. that way you can also store + and (), which is sometimes seen in tel numbers (as you mentioned yourself). and you don't have to worry about using up all bits in integers.

Answer 3

I'm not sure whether it's a good idea to use integers at all. Some numbers might contain special characters (# as part of the extension for example) which you should be able to handle too. So I would suggest using varchars instead.

Answer 4

Well, personally I do not use numeric datatype to store phone numbers or related info.

How do you store a number say 001234567? It'll end up as 1234567, losing the leading zeros.

Of course you can always left-pad it up, but that's provided you know exactly how many digits the number should be.

This doesn't answer your entire post,
Just my 2 cents

Answer 5

Just add my 2p of real-world experience.

We store 2 fields for phone numbers - a "number" and a "mask". Both of these are stored as TinyText types

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: (0123) 456 7890
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 6

int(11) is sometimes to short for postal code, take a look for address representation in Oracle Spatial DBMS

http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10826/sdo_geocode_concepts.htm#CIHBBGIF

Answer 7

I usually store phone numbers as a BIGINT in E164 format.

E164 never start with a 0, with the first few digits being the country code.

+441234567890 +44 (0)1234 567890 01234 567890

etc. would be stored as 441234567890.