Since a large part of the world doesn’t care about i18n and L10n, remarkably little articles on best practices are available about these topics.
If you live in a multilingual country however, you can’t build an application without. For a recent project I was looking at a way to avoid language specific tables (it goes without saying language specific fields are not an option either!).
Since we all like to keep our data as clean as possible, I have created my language specific tables like this example:
CREATE TABLE IF NOT EXISTS `country` (
`code` char(2) NOT NULL,
`language` char(2) NOT NULL default 'en',
`name` varchar(100) NOT NULL,
PRIMARY KEY (`code`,`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
A sample country table with the ISO code as identifier, a language ISO code and the country name in that specific language. Notice that code and language form the composed primary key. In a multi-table set-up you would probably have a country table and a country_language table. Although this solution looks so much cleaner it creates a challenge when it comes to querying.
Imagine you would have these records:
DE,en,Germany
DE,de,Deutschland
DE,fr,Allemagne
US,en,United States
US,fr,Étas-Unis
and you want to get a list of countries for a German audience.
Since much data in these kinds of tables won’t be translated into all possible languages (which is the case for German in this example), you will probably want to settle for a default language if German is not available: let’s say English in this case.
Basically you want to have a result with Deutschland and United States. After some thought, this query is my best bet:
SELECT IFNULL(language_set.`code`, default_set.`code`) AS 'code',
IFNULL(language_set.`language`, default_set.`language`) AS 'language',
IFNULL(language_set.`name`, default_set.`name`) AS 'name'
FROM `country` AS default_set
LEFT OUTER JOIN `country` AS language_set
ON default_set.`code` = language_set.`code`
AND language_set.`language` = 'de'
WHERE default_set.`language` = 'en'
This creates a set of German data joined by English data if German is not available. Since MySQL supports sub-queries for some time, you can use this set as a sub-query in any larger query. In my project I’ve put this default query in each language-based model for re-use in larger queries. For example:
$sql = "SELECT DISTINCT country.*
FROM (" . $completeSet . ") AS 'country'
INNER JOIN other_table ON country.`code` = other_table.`country_code`
ORDER BY country.`name`";
This would query all countries that have records in other_table.
Until now, I haven’t found a situation where this could not be used. I don’t guarantee that there isn’t one though. Any improvements are highly appreciated!
