Tip: Standards-Compliant Countries Table
How to build a Standards-Compliant Countries table
09 September 2016/ 4:56 PM
Every application needs a database, and every database needs a list of countries and/or nationalities for its patients, employees, customers, etc. The classic approach of building a Countries table has been build-as-you-go. The table gets populated as users add more and more records to the database. This causes the Countries table in one database to differ from the other.
In this blog post I present a different way to build a pre-populated list of Countries that is standards-compliant.
You've been given a simple task: Develop a table to store the list of the countries of the World. This table will be referenced by multiple tables and will be used to associate the country name and nationality (demonym). Next thing I know is that you build a quick table with these two columns, and possibly a surrogate primary key. Not that this approach is wrong, but your version of this table would surely be different than the one I'm creating. What if we wanted to exchange customer/partner/employee/patient/etc. details that include nationality between us? We'll need to convert values between our systems. What if I informed you that there are better, standard ways to build this table? and what if I passed a pre-built table in Excel to save you some time?
Official Resources
First, let's look into some of the available International Standards:
-
The United Nations m49Alpha Standard
The United Nations maintains a list of countries on its m49Alpha list of standards. This list is updated whenever the UN accepts an update. It's available at: http://unstats.un.org/unsd/methods/m49/m49alpha.htm . The list also provides a 3-letter abbreviation to each country. If you ever saw athletes in a major competition, you'd notice this 3-letter printed on their shirts. Using this list, we can use the m49 code as our surrogate primary key to the Countries table. Now that we are using the same primary keys as the international standards, exchanging data with other parties is easier.
-
The International Standards Organization ISO 3166-1 decoding table
This table lists the countries, including those which are not sovereign, against their 2-letter abbreviations. The list is available at https://www.iso.org/obp/ui/#search . This information is useful to us, as the 2-letters that appear at the end of a domain name would match these two letters. For instance, Kuwait's 2-letter ISO 3166-1 abbreviation is KW, hence, domains in Kuwait end with ".kw", whereas Italy uses IT, hence it's domains end with ".it". Using this list, we will augment our table and add the 2-letter abbreviation to the other details.
-
ISO 4217:2008 Codes for the representation of currencies and funds
This list shows every country and its currency codes and is available in several places like http://en.wikipedia.org/wiki/ISO_4217. This information is useful for us, as it helps us determine what is the currency of a country. These codes are also internationally-approved, meaning that we would be safe from regulatory issues.
The finished table
Now that we have the names of the countries in both Arabic and English, as well as the nationality (demonym) in both languages, the 2-letter and 3-letter abbreviations, currency codes, we can also add the international dialing codes.
The finished table is available in Excel format, so you can convert it to any suitable table. The following fields are available in the workbook:
| Field Name | Description |
|---|---|
| ID | A numeric code assigned by the UN to each country |
| CountryEn | Name of country in English |
| NationalityEn | Demonym in English |
| CountryAr | Name of country in Arabic |
| NationalityAr | Demonym in Arabic |
| ISO2DigCode | ISO 2-letter code used for the country |
| ISO3DigCode | ISO 3-Letter code used for the country |
| OfficialCurrencyEn | Official Currency in English |
| CurrencyISO4217Alpha | ISO Currency 3-letter code |
| CurrencyISO4217Num | ISO Currency number |
| IsSovereign | True if a sovereign country |
| IsHidden | True if the record needs to be hidden and used only for historic purposes |
| DialingCode | International Dialing Code |
| TLD | Top-Level Domain |