Database structure for multi-language system

Navigation
6

I already have a developed system, using several tables in the database. I have to start migrating the same to English and Spanish .

The development part I have an idea already (not total), but I do not know how to leave it in the database.

What I'm thinking of doing:
A produtos table, for example, has the fields id and name . I'm thinking of changing the name field to name_pt and add two more fields: name_en and name_es . In this case I will keep the table with the following fields: id , name_pt , name_en , name_es . I realize that I will also have to change in the system for data entry to have these new input fields, apart from some other points.

I wonder if this is a correct way or if you have some other better way to take care of it?

I do not know if it interferes with anything, but the system is being developed in PHP with CodeIgniter and MySql.

    
asked by anonymous 11.02.2014 / 04:49

4 answers

2

To make a multi-language system you'll have to refactor it practically whole. Adding one more column per language as you intended is not considered a good practice (it's a gambiarra). Ideally you should have a table of "expressions" with (ExpressionId, languageId, Expression) and use that everywhere, product names, user messages, interface labels, and so on. In addition to the refactoring, you have the basic work of swapping all the interface strings with these references and (depending on the size of the system you hire a translator). But the problem is not over, you'll have to change date / time masks, swap currency, etc.

    
11.02.2014 / 11:08
1

How would I do this implementation:

  • You would have a table containing all expressions in all languages - for example, messages .
  • It would have a second table containing the languages that the application supports - for example, languages (some entries: pt_BR , en_US , es_ES , etc.)
  • The first table would contain a column saying the language in which that message is (for example, en_US ) and another column referencing the original message (it may be the message id in the application's native language, for example English)

From this, simply elaborate and execute queries to: check how many messages are missing to be translated and their respective languages; which messages have no translation; how many sentences the system has; etc.

Finally, the staff has proposed several solutions and this I think is a very minimal solution, only containing one table with all the messages and another containing the languages (in some cases, we do not even need this second table, if you put the due integrity checks on the bank).

    
11.02.2014 / 15:07
0

I use a table called linguagens where I register language name and shortname (eg pt-BR) and the folder where it is!

I have a file inside this folder where it contains váriaveis de linguagem .

So I check the language in the bank and pull these variables. This is for static items. For dynamic, in the case, what comes from the database, I create a enum field, which is where I inform the language of the text (it can be news, an article or anything).

You can save currency, timezone, everything in this table of languages. Then you retrieve according to the selection, via the user's cookie. If no cookie is set, you inform the browser to linguagem default .

    
11.02.2014 / 14:10
-1

Use a separate table, one row per pair (product ID, Language ID). much easier to add one more language in the future. I suggest a standard like Iso 639 to identify languages.

    
11.02.2014 / 05:23
Efficient data structure for competing problem of high scores Implement message exchange between applications [closed]