title: Best way to translate database driven content
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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..).</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <ul>
  <li>Create a CMS system with multi language support</li>
  <li>No language files (obviously this is why I use templates)</li>
  <li>Being able to translate an original page/newsitem/product</li>
  <li>Optionally: to change the SEF URL according to the language</li>
  </ul>
  
  <p>I think option 3 has all this.. so the steps to create this solution is:</p>
  
  <ol>
  <li><p>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?)</p></li>
  <li><p>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).</p></li>
  </ol>
  
  <p>Any suggestions? :-)</p>
  
  <p>Thanks for thinking along!</p>