MySQL structure for translations

Question

According to this approach, default language is already translated in first table. If a user doesn't need a translation, he won't struggle with them. Just connect primary table, that's all...

PRODUCT TABLE (InnoDB):

 Obj_id(PK)             name                    desc
 ---------      -------------------     ------------------
     1          Million Dollar Baby    Short description is...
     2          Music Album            Another explanation...

TRANSLATION TABLE (InnoDB)

 trans_id (PK)    Obj_id (FK)   lang      field              trans
--------------   -----------   ------    --------    ---------------------
      22              1          TR        name       Milyonluk Bebek
      23              1          BA        name       Djevojka od milijun...
      24              1          TR        desc       Kisa açiklama burada
      25              1          BA        desc       Kratki opis je ovdje
      26              2          BA        name       Glazba albuma

But the problem occurs when administrator wants to change default language. I have two options to solve this problem:

1. Administrator has to decide default_lang at the beginning of the project and if he still wants to change default_lang in the future, the program will say: Go to hell.

2. Like first solution, admin has to decide default_lang at first, but the system will be able to transfer new default_lang data from translation table to primary table (i don't do this actually).

I think my solutions are not good enough.

Do you have better idea about DEFAULT_LANG problem with or without change the structure (if it's possible, don't change the structure - i like it)?

Answer 1

I wonder if having a table per language would be better. That way you'd just switch out tables that are being used.

TRANSLATIONS_TR
TRANSLATIONS_EN
TRANSLATIONS_FR
TRANSLATIONS_BR

Then in your user display routines, you decide what table to query when displaying translation text.