Database design to store localized country names and language names

Question

I'm working on swift / iOS with a parse.com backend but this question could be applied to any language and is a general conceptual question. I'll use a specific example for illustration purpose.

I have a content type, let's say "user" that is saved in a database. Each user is categorized by countries. The app is localized. Any user, must be able to filter users list by country. And here is the problem:

On database user table "countries" field, each user has entered his own country from a drop down list of countries, but these country names are localized. So for example, an american user, has chosen "United States of Americas" as his country, and that is the string in the "Countries" user table, but a french user looking to filter all users from USA, will launch a search to corresponding localized "Etats-Unis d'Ameriques" which will not match "United States of Americas" users.

To avoid this localization categorization problem, I was thinking of abstracting the name countries by associating each with a numeric ID, in a tuple or array, then store in the database that ID. So when referring to it I would look-up the array ID and refer to localized string other member in the array.

Is this a proper way to handle this scenario ? I guess there is a "right" way to handle this.

Answer 1

Make a table COUNTRY with the ISO code of each country. A table LANGUAGE with the ISO code of each language.

Then a join table COUNTRY_LANGUAGE with the name of each country in each language and another join table LANGUAGE_LANGUAGE with the name of each language in each language.

You can populate such tables from the ISO lists available in the internet.

Users will select countries in its own languages but joins and queries will be donde using the ISO code.

A PNG is worth 1024 words:

Answer 2

An enterprise application I work on does this which makes for messy queries but allows as many languages as you want. The following is an overview not intended to be run on an ANSI compliant database

Table Locale
ID number primary key  
VALUE  string: examples, English, French, German....

Table Countries
ID number primary key
Date_created
Date_last_modified
...

Table Countries_Locale
ID number part 1 of primary key, foreign key to Countries
LOCALE_ID part 2 of primary key, foreign key to Locale
VALUE string: examples United States, Les Etats Unis.....

So a dropdown list would be this query:

Select Countries_locale.VALUE, Countries_locale.ID
From Countries_locale
where Countries_locale.LOCALE_ID = 2  --all English names for countries