Datatype for phone number: VARCHAR, INT or BIGINT?

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.

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.