I had a weird code rescue project the other day where the client was complaining that they couldn’t update profile phone numbers. My test data for phone numbers is usually 416-967-1111 (a Canadian pizza chain,) and it was working fine. I thought maybe there was an issue with brackets or dashes or something, but every permutation saved fine for me.
Then I looked at the schema. The phone numbers were being stored as integers.
There’s nothing wrong with storing numbers as numbers, and phone numbers are numbers, so let’s review the positives here: the storage requirements in the database are smaller than they’re going to be with string storage (char or varchar,) and it lets you take out the formatting information so that could, in theory, be moved to the presentation layer (in this case, it wasn’t, but let’s not dwell.)
On the other hand, you have to format the numbers every time (phone numbers in a web app are basically display-only, though mobile web has some interesting bits coming up,) and there’s an important gotcha that the previous developer missed:
Numbers typically don’t pad left with zeroes.
In other words, “001 234 89439” is going to be stored as 123489439. (No, I don’t understand international phone numbers. It’s an example, OK?) This means that your parsing code can’t make assumptions about the length of the number if there’s a chance that zeroes will be at the beginning.
In this case, it got worse, and explained why I couldn’t recreate the problem: the code was parsing the individual components of the phone number, so for North American numbers (it knew the region already) it would break it down to 1-xxx-yyy-zzzz (with optional extensions added to the end) so a number stored as 4169671111 would render as 1-416-967-1111.
Except. The zeroes. In each section, now.
Basically, the parsing was using something like sscanf($number, ‘%1d%3d%3d%4d’) (this was in PHP) to break it down and then there was some concatenation on the elements.
Unit testing would have been helpful here (I ended up adding some,) because then you might have noticed that 14160380042 would ultimately get displayed as 1-416-38-42, because the leading zeroes get parsed but not displayed.
Anyway, between some badly needed unit tests and changing each %d to a %s, the numbers displayed correctly. What’s neat here is that everything was in fact saving fine, so no data was lost – it just didn’t render correctly, which was a relief to the client.
I’ll be honest, I usually store phone numbers as text (I rarely work on systems with billions of phone numbers, so the space hit isn’t a big issue compared to other optimizations I could make,) and then I can strip and parse things as needed while keeping the original input as entered (it’s similar to how I like to store HTML unfiltered, but that’s a story for another day.) After some reflection, storing the phone number as an integer isn’t as big a red flag as I thought it might be, but you definitely need to watch for the edge cases that your typical test cases (pizza, anyone?) won’t catch.