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?
Improve this question
4
  • 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. Commented Jul 25, 2012 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 Commented Jul 25, 2012 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... Commented Jul 25, 2012 at 18:21
  • Thanks, checked this. But the solution is no good if you have multiple translation columns because joining will be really slow. Commented Jul 25, 2012 at 18:28

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.