Best way to translate database driven content

Question

I've been struggling with this for a while now. One my CMS' is ready to be extended with a translation module. I've been thinking of different methods but haven't figured out what is the best way so far.

Basically I have a CMS which uses a template system to parse all data from a database to the screen. I've come so far to "split up" my templates in different folders to be able to translate things that are "static" like images with text, footer links, etc.

However, there are many modules (pages, news, products) that have multiple fields that require a database driven method to be translated. I started off with a "languages" table which describes languages (id, iso_code, name). That's as far as I've come.. since there were a couple of projects that had to be done I haven't spend any more time to this subject thus far.

My first thought ("the quick fix") was to add multiple fields inside the tables (such as "title_nl", "title_en"), but this actually makes the database more crowded than is needed in my opinion.

My second thought was to create a table, "news_translations" for example. Which contained the language iso code, a news_id, the fields that require translation. Obviously a news_id connects the translation to it's original and the language iso code is used to get the right language from the database. Then in my front-end code I would first check if the default language is selected (=> select from the "news" table) or a translation (=> check inside translations table). If the 2nd case does not return any results a message is display "Sorry, no translation available" and the default is shown (or an error message, what fits the client best..).

But then there's a 3rd option.. my websites all use search engine friendly links (www.domain.com/pagename/ or www.domain.com/news/1-news-item-here.html). It would be far better if I had the ability to also "override" the SEF URL in my translations table. But I guess in this case I would always need 1 extra query to the translations table (since we first want to check for a translated page)... guess it's not such a big deal, but it's worth considering I guess.

In the end I guess by describing my options number 3 is what I need. But I'd like to have some other opinions on the subject as well! This is what I am trying to achieve:

Create a CMS system with multi language support
No language files (obviously this is why I use templates)
Being able to translate an original page/newsitem/product
Optionally: to change the SEF URL according to the language

I think option 3 has all this.. so the steps to create this solution is:

Create a _translation table for each item (or perhaps even in the original by adding 2 new fields 'translation_to' (containing the PrimaryKey) and 'translation_is' (containing ISO code) - however.. in that case all fields would need to be edited (which is not always necessary.. plus by creating a 2nd table I keep the originals divided with their translations, right?)
If the default language is NOT chosen first query the translations table to find a translation, if one is found display the translation. Otherwise notify/error the user and/or display the original text (based on the SEF URL... if the SEF is not found within the translations or original table, then obviously display an error only).

Any suggestions? :-)

Thanks for thinking along!

Are you trying to translate the content or the system (CMS)? — Marcus Adams
Sorry if that wasn't clear. I'm trying to translate the content! The CMS is in English, if that has to change at a certain time I will be using a key-based translation table. Since that are most likely to be just simple words or sentences :) — Joshua - Pendo

Timothy Perez Timothy Perez · Accepted Answer · 2012-03-14T17:10:52

I would like to see what your table structure looks like. Probably the best thing you can do is generate two seperate new tables named something like "CONTENT_MULTI_LANG" & "SITE_LOCALES".

Then in the code that prints out your content do an initial check for a language flag. I'd create two separate classes for loading static content, something like "Content_LoadStandard" and "Content_LoadMultiLang". So then your conditional will look like this.

if ($this->site_locale == 'standard'){
    $contentLoader = new Content_LoadStandard();
} else {
    $contentLoader = new Content_LoadMultiLang($this->site_locale);
}

$content->blah($cheese);

Your "CONTENT_MULTI_LANG" table should be a narrowed down version of your standard CMS object table, only containing the relevant content field(s) that need to be in alternative languages.

// PSEUDO SQL
  CREATE TABLE `LOCALE` (
      `id` int(11), 
      `locale` varchar(16),    // name of locale (language)
      ...                // any other fields
)

  CREATE TABLE `CONTENT_MULTI_LANG` (
      `id` int(11), 
      `pcid` int(11),    // parent content id
      `lid` medint(),    // locale id
      `content` {$type}, // whatever type you use (varchar, text, bin, etc)
      ...                // any other fields
)

In your Content_LoadMultiLang class, create methods to query alternate content using a join.

TIP: Might be a good idea to establish relationships in your table to do cascading deletes on content rows, that way if you delete content in standard your multi lingual version(s) will also be deleted.

Best way to translate database driven content

2 Answers