MySQL structure for translations

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 prim开发者_StackOverflow中文版ary 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.

gettext solves a different kind of problem than a multilingual database: gettext is for the UI, while the database is for the content.

You'd use gettext or equivalent for fixtures like error messages, a button label or a page title, where there's a small list of items that change infrequently.

A multilingual database is for large volumes of content that may or may not change frequently, like, say, the titles of hundreds of thousands of books and movies, or descriptions for tenders.

My approach I think it would be (I'm in the process of building this myself):

Maybe (1)Product has (N)Names (depending on language, assuming you only have one column describing the language, like 'es', 'en')

[ Product ]—(1)—————(N)-[ProductName]
|    id   |             | name      |
                        | language  |

Maybe it's better Product-Language in a N:M relationship:

[ Product ]-(N)—————————(M)-[Language]
|   id    |        |        |iso_code|
             (translation)  | name   |


[   Product   ]<————[ProdTranslation]———>[language]
|    id       |     | name          |    |iso_code|
|default name?|                          | name   |--- (name of language: "English")

Another option could be to consider each translated of the product to be a "type of" Product (a subclass). Logical model follows:

[   Product   ]
|    id       |-(1)————(1)-[ EnglishProduct ]
|default name?|            | name (in eng.) |
|             |
|             |-(1)——(1)-[FrenchProduct]
|             |          | name(in fr.)|

When implementing this in a relational database, then you could either a) have 3 tables (in this example, more with more languages) or b) put it all in one table. It depends on how many languages you would have, size of the name/description, performance, etc.

[ Product ]
| id      |
| name_en |
| name_fr |
| name_de |

The code querying the database would know the language to be used, and then query the name from the right column.

Did you look into gettext?

http://www.gnu.org/software/gettext/