5

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)?

Improve this question
1
  • 6
    +1 for the Go to hell message to Admin. Commented May 10, 2011 at 12:02

4 Answers 4

Reset to default
4

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.

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

Comments

1

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.

Improve this answer

1 Comment

the problem with gettext for me (i used it for Wordpress translations before) is synonyms. For instance I use Class two different places in my software (one of them for categorizing, another for school class). That works with English, but when I want to translate it, that's problem for other languages. Maybe gettext solved this problem.
1

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.

Improve this answer

Comments

0

Did you look into gettext?

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

Improve this answer

3 Comments

@progrsm... the problem occurs when administrator wants to change default language in the future.. so i have to transfer NEW DEFAULT_LANG from TRANSLATION table to PRODUCT (PRIMARY) TABLE.
@dino I'm not sure how your idea was about gettext, but it's very simple. You have all the translations in gettext for each language. In database you have only the "default" language. You fetch for example "Music Album" call gettext and ask it for a specific language. Bang you have it. Fast and simple guaranteed!
something to add, to put the translation into the database is a BAD idea. Gettext is your friend.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.