Datatype for phone number: VARCHAR, INT or BIGINT?

Question

So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition:

Take a look at the column from_number which is a VARCHAR(45) right now but it will hold a phone number. Since I don't know how many numbers a phone could have all over the world then I am trying to cover almost all of them. I want to keep database integrity as much as possible so I think VARCHAR is not a proper type for hold this kind of information - maybe I am wrong, you tell me - so I am thinking in change to INT or even BIGINT.

When I am defining a column in Workbench I should specify the number between parentheses () not in all the cases but in those I mention previous I had to. So if I do this: BIGINT() I got this error:

Which guide me to read a bit about this MySQL type here. Basically the info is this:

A large integer. ... The unsigned range is 0 to 18446744073709551615.

Which make me ask: what value I should set for parentheses when I am defining a BIGINT() type. (I am using BIGINT because I don't know if INT can hold as many numbers as a phone could have - perhaps I am wrong too). Which is the right way to create|design a column in MariaDB/MySQL databases?

Anyway I would like to know your opinion, experience and of course I would like to get an answer

Note: I am using MySQL Workbench latest edition for create the ER diagram. I am using also MariaDB 10.0.x

Answer 1

How would you handle a phone number with an extension, such as "+1-000-000-0000 ext 1234" ?

Note, the "+" indicates international dialing rules should be applied; so from North America, the system automatically knows "011" in front of international calls, etc.

Also, what about phone numbers such as "1-800-DBA-HELP"?

I would typically store phone numbers as text. Having said that, it really depends how critical your phone number column is. If you are running automated dialers from that column, then you'd really want to ensure that only numbers are included, and the data represents well-formed phone numbers.

You could have separate columns for extensions, and phone numbers that have text, such as the "1-800-DBA-HELP" example I provided.

Answer 2

Previously it was written:

"With MariaDB you could use a computed field to extract just the digits for an auto-dialer. Also works for MySQL 5.7."

In response to the OP's question about this ("can you explain a bit what are you telling me?"), here is an explanation.

Many database systems have now introduced this feature. These are fields which are known variously as "computed", "virtual" or "generated" which are derived from values in other fields. The power of this feature will vary depending on your RDBMS. I know that Oracle, Firebird, MariaDB and now MySQL 5.7 have them. Others probably also do.

An easy example would be to have a surname column and have a computed column which "stores" (remember, they can be virtual - i.e. calculated on the fly, or they can be physically stored on disk) the surname as all capitals, thereby making searching easier. That way you only have to search on CAPs (using, say, LIKE), knowing that the data being searched in the [computed | virtual | generated] field is in capitalised text.

The concept for MySQL 5.7 is explained here and here. It has been in MariaDB for a bit longer and the concept is also explained here. Some possible uses are suggested here, but you are really only limited by your imagination. They can be seen as a convenient (and less error-prone) substitute for triggers.

For your particular use case, you could derive a dialable number from a text field "+" --> "00" (or whatever your international dialling code is). Just a thought.

Answer 3

Hmm. Phone numbers are made of numbers. Using varchar allows user to store any type of formatting, with ( or not, with - or . and it quickly creates a mess with your data. A phone # format is "country" dependent, the mask should be tied to the country. Extension is an extension and is optional, so it should be stored in a "extension field". (int also). For 1-800-DBA-HELP, i would convert that on the fly and store actual number. If you really need these human readable phone #, store it in separate varchar field.

Answer 4

I usually stores the Phone Numbers in simple text. Formatting and display leave it to the client code.

Here, more than, how you store? what you are going to do with that phone number is really important.

If your business wants to perform outbound calls from your system, application will extract only numbers. If your business wants to make international calls, store country code and area code in separate columns.

If your business wants for reporting, the application will format and display with extension and numbers separately.

From my understanding, designing universal data model for phone number is not a good idea. Each country has different numbers, extensions and area code apart from country code. Also, I came to know, some countries does not have area code.

This may not answer your question but it will help to widen our understanding. Thank you.