6

Possible Duplicate:
Schema for a multilanguage database

I am building a very busy web application which has multilingual data. All this data is stored in a MySQL database. What is the best way to store this data? I have a few guesses though:

  1. separate columns in the table for each language (like title_en, title_fr)
  2. separate tables for each language (like pages_en, pages_fr)
  3. cross table for translations (having an id in the pages table and having this linked id in a translation table where there are multiple columns like trans_en, trans_fr)
  4. having non-multilingual data in one table for example pages and translations in one table per language (pages_en, pages_fr)
  5. other?
Community
  • 1
  • 1
Anze Jarni
  • 1,141
  • 7
  • 7
  • Okay I see a problem with the second solution because if you add a new language and create a new table, all the non-multilingual columns must be copied to this new table. But it is probably faster than the first solution because the table is smaller in size. – Anze Jarni Jul 25 '12 at 18:19
  • Third solution makes primary tables very clean, but it is hard to do manual maintenance and the translation table will be killer big – Anze Jarni Jul 25 '12 at 18:20
  • Second solution is easy to expand to new languages (just adding columns) but can still get very big if the languages drastically expand)... A bit stuck here... – Anze Jarni Jul 25 '12 at 18:21
  • Thanks, checked this. But the solution is no good if you have multiple translation columns because joining will be really slow. – Anze Jarni Jul 25 '12 at 18:28

1 Answers1

1

If I had to make a site multilingual, then I would design the db as follows:

enter image description here

Every page in the application can have multiple languages. You only have to filter on the language id to get the desired language for the page.

Sven van Zoelen
  • 6,989
  • 5
  • 37
  • 48