12

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:

enter image description here

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:

enter image description here

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

13

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.

  • Yes, they will be critical so I wont make any mistake in the future, based on that I would only allow numbers, what is your suggestion? Is easy to add a new column that hold the extension number or I want to make people enter numbers as 1-800-DBA-HELP by it's digits – ReynierPM Dec 24 '15 at 19:22
  • It really depends on your requirements. If you need to retain human-recognizability I would certainly want to keep the text-based numbers somewhere, probably in a text field. If you don't care about the text part, then don't store them. – Max Vernon Dec 24 '15 at 19:25
  • 1
    INT is certainly not big enough if you store the full number with country code. BIGINT is probably big enough. – Max Vernon Dec 24 '15 at 19:44
  • 1
    I'd want at least 20 digits. – Max Vernon Dec 24 '15 at 19:45
  • 1
    With MariaDB you could use a computed field to extract just the digits for an auto-dialer. Maybe in MySQL 5.7 (not sure). – Vérace Dec 25 '15 at 13:12
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.

  • Great, can you improve a little your question by adding some queries? I mean I got the concept but I am not sure how to create the virtual or generated values. I am thinking in use CONCAT or something else but not sure at all. Also you mention a search by CAPS using LIKE could you put an example of this too? What about the performance of columns calculated on the fly (virtual) vs persisted (generated`)? – ReynierPM Dec 29 '15 at 19:29
1

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.