I woke up this morning, inspired by a debate on Stackoverflow to perform a research on how best numbers should be stored.
My first thought was that they were definitely strings. How do you save the brackets, dashes, plus sign and all the other possible characters that shows up in our phone books.
TLDR; Phone numbers aRe NUMBERS! Store them in MSISDN format.
So the FBI instinct inside me began to do some investigation and here is my breakdown; assuming +234(0)1234567890 (fake number) for example.
- Prefixes such as +234, +46, +1… are dialing codes or country codes.
- The plus + sign which can also be represented by 00, are signals or codes that indicates you are dialing an international number. Therefore, +234(0)1234567890 can also be represented as 0023401234567890.
- The (0) usually means that the number you are dialing is located in the same region as you are. So you can just dial 01234567890 without country codes.
- The next few 3 – 4 digits are the area/region/mobile provider codes where the number is hosted.
- The rest of the digits are your subscriber number which uniquely identifies you (Some parts of the subscriber number may even indicate your network provider before number porting was a thing).
- Numbers like 234–805-GOT-MILK is just a phone code and would eventually be transformed to an actual phone number before it is dialed.
How then should we store phone numbers in the database?
Think of the + signs and brackets as codes that helps the mobile equipment to confirm what route to use when calling the number you provided.
This would mean that storing a phone number as +234(0)12345… is saving the phone code or the route of that phone. So if your intention is to store the destination and not the path, then go ahead and store the destination as phone numbers. Yes, actual numbers and not codes.
This is more efficient, simpler, easy to maintain, better performance in terms of query and could even save you some storage space if you anticipate or you are dealing with millions of records.
How do you validate the length of phone numbers?
I would support the argument that this is a business rule and are better kept in the application code. While database’s VARCHAR allows you to set a fixed max length, it would also potentially accept 234abcxyz as a phone number.
Using DB level validation for max length of a phone number is perhaps not the best and may mean that you will have to change your schema every time the validation changes.
The case of extra numbers — vs — invalid numbers.
How do you separate the country code from the number?
Luckily, there are libraries out there that understands the intricacies of how these things work and would help you identify what country the phone numbers belong to. Note: you have to provide the international format of the number to those libraries.
E.g. +1234…. or 001234…
One of which is https://www.npmjs.com/package/libphonenumber-js.
FUNFACT: Dialing codes by zones
Zone 1 = North America = +1
Zone 2 = Africa = +2
Zone 3/4 = Europe = +3 or +4
Zone 5 = South America = +5
Zone 6 = South Pacific= +6
Zone 7= Russia and vicinity= +7
Zone 8= East Asia, Special services= +8
Zone 9 = South and West Asia, Middle East= +9
Zone 0 = TBD
Shoutout to Constantine Kevin for some of his inputs. 👏