{"id":50,"date":"2009-11-23T00:10:18","date_gmt":"2009-11-23T00:10:18","guid":{"rendered":"https:\/\/rwec.co.uk\/blog\/?p=50"},"modified":"2019-08-12T16:10:09","modified_gmt":"2019-08-12T15:10:09","slug":"atomic-translations-part-1","status":"publish","type":"post","link":"https:\/\/rwec.co.uk\/blog\/2009\/11\/atomic-translations-part-1\/","title":{"rendered":"Atomic Translations – Part 1 – Database i18n as a Data Type Problem"},"content":{"rendered":"

[Part 2: Translated Text as a Data Type in PostgreSQL<\/a> | Prototype Postgres Library<\/a>]<\/p>\n

The most obvious task when making any application multi-lingual is to make the UI<\/abbr> translatable; there are absolutely tons<\/strong> of options for this, libraries in all sorts of languages, recipes, discussions, etc. But for many applications there’s another task, just as important, which is taking a database, and allowing someone to translate the data inside that.<\/p>\n

There are various approaches to “database i18n<\/abbr>” too – generally involving re-designing your schema in such a way that the translations are to some extent part of the “normalised” data. But these tend to make the schema somewhat unwieldy, and writing ad hoc queries and reports becomes tricky. So it occurred to me that if you treat i18n not as a schema<\/strong> problem, but as a data type<\/strong> problem, you could save some of that pain.<\/p>\n

<\/p>\n

The Problem<\/h2>\n

Just to be clear, let’s have an example we can work through: if I have a table of countries in my database, and one of the columns is the name of the country, that name isn’t going to be the same in all the languages of my new super-duper internationalised system. So let’s say we start with this:<\/p>\n\n\n\n\n\n\n\n\n
Table “country”<\/th>\n<\/tr>\n
id<\/th>\nname<\/th>\ncurrency<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nUnited Kingdom<\/td>\nGBP<\/td>\n<\/tr>\n
2<\/td>\nGermany<\/td>\nEUR<\/td>\n<\/tr>\n
3<\/td>\nFrance<\/td>\nEUR<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Existing Solutions<\/h2>\n

I’ve come upon various approaches to the database i18n<\/abbr> problem, but they all group down to two basic ideas: using one big table<\/strong>, and treating i18n<\/abbr> as a problem of normalization<\/strong>.<\/p>\n

One Big Table<\/h3>\n

The basic idea of the “one big table” solution is to extract all the text that needs translating, and put it in one big list, ready for translation. This is, after all, how UI<\/abbr> i18n<\/abbr> usually works, so why not just treat your data as “some more translation entries”? As an example, this appears to be (I haven’t actually experimented with it) what the built-in i18n system in CakePHP<\/a> will do for you.<\/p>\n

So, for our example, we create a new “text” table which contains all the text we want to translate. The primary key on this table will be a combination of some kind of unique ID, plus a language code; something like this:<\/p>\n\n\n\n\n\n\n\n\n\n\n
Table “text”<\/th>\n<\/tr>\n
id<\/th>\nlanguage_code<\/th>\ncontent<\/th>\n<\/tr>\n<\/thead>\n
…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n
42<\/td>\nen<\/td>\nUnited Kingdom<\/td>\n<\/tr>\n
42<\/td>\nfr<\/td>\nRoyaume Uni<\/td>\n<\/tr>\n
42<\/td>\nde<\/td>\nVereinigte K\u00f6nigreich<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

(Here, item 42 is “the name of the UK”, and we have translations for that item in English, French, and German.)<\/p>\n

Now probably the simplest way to use this is to replace the “name” column in our original “country” table with a reference to this new table – giving us something like this:<\/p>\n\n\n\n\n\n\n\n\n
Table “country”<\/th>\n<\/tr>\n
id<\/th>\nname_text_id<\/th>\ncurrency<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n42<\/td>\nGBP<\/td>\n<\/tr>\n
2<\/td>\n43<\/td>\nEUR<\/td>\n<\/tr>\n
3<\/td>\n44<\/td>\nEUR<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

This looks like nicely normalised data – you can set up a foreign key from the “name_text_id” to the “text” table, and so forth. But what kind of a table is “text”? It has nothing to do with your schema – it’s not really representing anything<\/em>; if you think about it, you could just as well have a table called “numbers” as well, and reference it whenever you want a numeric value…<\/p>\n

A slightly neater variant of this is to replace the “id” in the “text” column with some reference back<\/em> to the table and column being translated. Maybe we already have a table holding all our UI messages, appropriately categorised. So, with a bit of extra normalisation, we might end up with this:<\/p>\n\n\n\n\n\n\n\n\n
Table “country”<\/th>\n<\/tr>\n
id<\/th>\ncurrency<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nGBP<\/td>\n<\/tr>\n
2<\/td>\nEUR<\/td>\n<\/tr>\n
3<\/td>\nEUR<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n\n\n
Table “text_item”<\/th>\n<\/tr>\n
id<\/th>\ntype<\/th>\ncategory<\/th>\nsection<\/th>\nitem<\/th>\n<\/tr>\n<\/thead>\n
…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n
42<\/td>\ndatabase<\/td>\ncountry<\/td>\nname<\/td>\n1<\/td>\n<\/tr>\n
43<\/td>\ndatabase<\/td>\ncountry<\/td>\nname<\/td>\n2<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n\n\n\n
Table “text_translation”<\/th>\n<\/tr>\n
text_item_id<\/th>\nlanguage_code<\/th>\ncontent<\/th>\n<\/tr>\n<\/thead>\n
…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n
42<\/td>\nen<\/td>\nUnited Kingdom<\/td>\n<\/tr>\n
42<\/td>\nfr<\/td>\nRoyaume Uni<\/td>\n<\/tr>\n
42<\/td>\nde<\/td>\nVereinigte K\u00f6nigreich<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

This is quite attractive – all your translations are in one place, so writing an interface to keep them up to date should be quite easy – but as well as the meaninglessness of the “text” table, we now have another oddity, which I call the “anorexic table problem<\/strong>“.<\/p>\n

The problem is that the “country” table has given away most of its data to the “text_translation” table – without joining through, you can only learn the currency. And this is a mild case: you could start off with a table with 5 columns, decide all of them except the primary key need translating, and be left with a glorified sequence – a table with nothing but a primary key.<\/p>\n

This isn’t unique to i18n – indeed many extreme forms of normalisation pretty much require you to create such tables – but in practice, it’s a nuisance, and a downside of a lot of i18n schemas.<\/p>\n

I18n as N11n<\/h3>\n

The other set of i18n schemas I’ve come across can be broadly called “normalisation-based solutions” – they take the existing schema, add multiple languages to it, and apply the rules of normalisation to the result.<\/p>\n

There are various ways this can end up looking, but the idea is to treat the translations on each table as you would any 1-to-many relationship: with a new table. So for our example, we might create this:<\/p>\n\n\n\n\n\n\n\n\n
Table “country”<\/th>\n<\/tr>\n
id<\/th>\ncurrency<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nGBP<\/td>\n<\/tr>\n
2<\/td>\nEUR<\/td>\n<\/tr>\n
3<\/td>\nEUR<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n\n\n\n\n\n\n
Table “country_names”<\/th>\n<\/tr>\n
country_id<\/th>\nlanguage_code<\/th>\ncontent<\/th>\n<\/tr>\n<\/thead>\n
…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n
1<\/td>\nen<\/td>\nUnited Kingdom<\/td>\n<\/tr>\n
1<\/td>\nfr<\/td>\nRoyaume Uni<\/td>\n<\/tr>\n
1<\/td>\nde<\/td>\nVereinigte K\u00f6nigreich<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Now, this example only has one column to translate, but I’ve called my new table “country_name”, implying that if there were multiple columns to translate, I would have created multiple new tables. So now not only do I have an “anemic” country<\/em> table, I have a huge number of new tables cluttering up my schema.<\/p>\n

You don’t have to go this far – you could instead create a “country_i18n” table, with columns for each of the bits of country information you need to store, which would at least keep this explosion down a bit. This seems to be (again, I’m afraid I haven’t experimented) what the Symfony framework offers you<\/a>.<\/p>\n

There are a few other variations on this, as well – I came upon this article with various examples<\/a> (demonstrated, oddly, in terms of XML, not a DBMS). One worth mentioning is the idea of base + translations<\/strong>, which avoids the “anorexic table problem” by leaving the base language in the original table, and only the translations in a new structure. This is certainly an interesting compromise – but it requires you to formally pick a single “base” language, and I suspect the inconsistency of where data is stored would make writing maintenance code trickier.<\/p>\n

Translations as Sub-Atomic Particles<\/h2>\n

Both of the approaches I’ve mentioned, and all the variations within them, frame the underlying problem<\/em> in the same terms: translations are additional pieces of data, and your schema needs to accommodate this data. But there is actually a different way of looking at the problem, and that is that the translations are all representations<\/em> of the same value<\/em> – and the act of translating them is about selecting a representation<\/em>, not filtering and combining additional data<\/em>.<\/p>\n

In other words, translated text is a data type<\/strong>.<\/p>\n

Now, one of the principles often cited in relation to databases is that of atomicity<\/em> – values in the database should be atomic<\/em>, with structure defined at a relational level, not within each value. But as many people have pointed out – see this blog post<\/a> for example – it is quite hard to pin down just what atomic<\/em> means.<\/p>\n

If you’re not allowed any<\/em> structure, you shouldn’t really be storing strings – they are, after all, an ordered list of characters. But, crucially, that has no bearing on the data you are trying to represent <\/em>– you might want to model a “country”, but you don’t want to model <\/em>a “name”, you just want to store <\/em>and use <\/em>it. And – in my view – having multiple translations of that name needn’t change that fact. As far as your schema is concerned, all the translations of a particular name form a single atom, and the individual strings are just a part of that value.<\/p>\n

So just as you don’t use the schema<\/strong> to link together the letters ‘G’, ‘e’, ‘r’, ‘m’, ‘a’, ‘n’, and ‘y’, you shouldn’t use it to link together the translations ‘Germany’, ‘Allemagne’, and ‘Deutschland’. Instead, you select a data type<\/strong> that represents the kind of data you are storing.<\/p>\n

The Atomic I18n Solution<\/h2>\n

So if we go back to our original example, and look at what it would mean to treat i18n as a problem of data types, not schema design, what would it look like? Obviously, the table still has to change, but not by much – we just change the type of the “name” column to our new “translated text” type. Once we’ve stored our extra translations, we end up with something a bit like this:<\/p>\n\n\n\n\n\n\n\n\n
Table “country”<\/th>\n<\/tr>\n
id<\/th>\nname<\/th>\ncurrency<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n{“en”:”United Kingdom”, “fr”:”Royaume Uni”, “de”: “Vereinigte K\u00f6nigreich”}<\/td>\nGBP<\/td>\n<\/tr>\n
2<\/td>\n{“en”:”Germany”, “fr”:”Allemagne”, “de”:”Deutschland”}<\/td>\nEUR<\/td>\n<\/tr>\n
3<\/td>\n{“en”:”France”, “fr”:”France”, “de”:”Frankreich”}<\/td>\nEUR<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Note that we don’t need to create any new tables – our schema is effectively unchanged – and we’ve avoided the “anorexic table problem”. We still have to update any existing queries to select the name in a particular language, rather than all of them at once, but we don’t have to pull more tables into the query just to see what we’re doing.<\/p>\n

Of course, this relies on a “translated text” type being available in our DBMS, along with all the functions we’ll need to interact with it.<\/p>\n

This post has grown somewhat longer than I expected, so I have split it into two parts; in the next part<\/a>, I will go into the details of my prototype implementation, which aims to create a user-friendly translated_text type from standard features of PostgreSQL.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

[Part 2: Translated Text as a Data Type in PostgreSQL | Prototype Postgres Library] The most obvious task when making any application multi-lingual is to make the UI translatable; there are absolutely tons of options for this, libraries in all sorts of languages, recipes, discussions, etc. But for many applications there’s another task, just as […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[51,46,49,50,47,48],"class_list":["post-50","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-atomicity","tag-database","tag-i18n","tag-internationalization","tag-postgres","tag-postresql","post-preview"],"_links":{"self":[{"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/posts\/50","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=50"}],"version-history":[{"count":23,"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/posts\/50\/revisions"}],"predecessor-version":[{"id":373,"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/posts\/50\/revisions\/373"}],"wp:attachment":[{"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=50"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=50"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rwec.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=50"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}