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:
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.
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)?
解决方案
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.