How to resolve the error of phone number data type in sql?
So this will be the dummy question of the year but I need to ask since it is not the first time I passed 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 holding this kind of information - maybe I am wrong, you tell me - so I am thinking of changing 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 mentioned previously I had to. So if I do this: BIGINT() I got this error:
Which guides 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 makes me ask: what value should I 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 to create the ER diagram. I am using also MariaDB 10.0.x
How would you handle a phone number data type in sql with an extension, such as "+1-000-000-0000 ext 1234" ?
Note, the "+" indicates international dialling 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.