database: best practice countries, country codes, country phone codes

Question

I am looking for a "best practice" if you store country codes in a database but couldn't find a "this is the right way" for that. I want to store the 2 chars country code and also the country phone codes (eg Germany would be "DE" and "+49").

Actually my plan is as follows: create one table countriesand one table with country_codes. Something like this:

TABLE: countries
id INT(11)
code CHAR(2)

TABLE: country_codes
id INT(11)
country_id INT(11) FORGEIGN KEY (countries -> id)
phone_code VARHAR(6)

I think I need to split them because some countries have more than one phone code. This way a country can have multiple phone codes.

But to my question: is that the "best practice" to do that? No only from that point "that will work" also more from that view if I want to rollout my application in "all" countries or if I want to translate the app in multiple languages (in that case I wanted to use the countries table also for the different languages.

What is your way to do thing like taht, if you want to able to translate your app in any language without the need of re-coding stuff and if you also need a list of all countries in you app?

If it should matter: I am planing to go with laravel for this app.

Answer 1

Country codes are standardized to two letters by ISO 3166-1-alpha-2, so storing them that way will work. It's often helpful to include a country name in the table, so a user can choose the right country without having to know all the codes.

Telephone numbers are far less standardized. The ITU offers recommendation E.164 for representing actual telephone numbers (called "directory numbers" in telephony jargon). Country codes are defined as one to three digits. North America (including USA, Canada and many Carribean nations) all are part of the North American Numbering Plan and share the country code 1.

Directory numbers are typically preceded by + and punctuated by dots. So, for example, the published New York City directory assistance number is (or was when they still had such a service) +1.212.555.1212. If you called that number from someplace in Europe, you would see the + and substitute your local international prefix. In NANP, multiple nationalities have the same country code.

But, UK is strange. Calling from outside the country, it's +44.exchange.number. But calling long distance from within the country it's (0) exchange.number.

My point: it's hard to get it right if you try to compose directory numbers with a country code in your software. You're probably better off asking users to provide their telephone numbers with the international prefix.

You should definitely not tie E.164 country codes to ISO 3166 two-letter country codes by putting them as different columns on the same row of a table. You need two separate tables to be future proof. The standardization organizations are different and do their own things, so your data model should reflect that.

Read this: Falsehoods Programmers Believe About Telephone Numbers.