Database structure for multi-language system

Asked

Viewed 2,553 times

6

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

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

What I’m thinking of doing:
A table of produtos, for example, I have the fields id and name. I’m thinking of changing the field name for 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 the system to input data to have these new input fields, apart from a few other points.

I wonder if this is the right way or if there’s some better way to take care of it?

I don’t know if it interferes with something, but the system is being developed in PHP with Codeigniter and Mysql.

php database

4 answers

2

To make a multi language system you will have to refactor it virtually all. Adding an extra column per language as you intend is not considered a good practice (it is a gambiarra). Ideally you would have a table of "expressions" with (Expressionid, languageId, Expression) and use this everywhere, product names, messages to the user, Labels on the interface, etc. In addition to refactoring has the manual work of exchanging all the interface strings for these references and (depending on the size of the system hiring a translator). But the problem is not over, you will have to change date/time masks, change currency, etc.

  • As for the refactoring part of the system I’ve thought about and tried to calculate the amount of hours with a fat (to have a little idea have 22 models). Now what would an example of this table of "expressions" look like? And yes, there is a lot of work. Valew

  • The table depends a lot on what you want. let’s say you have three languages so you have a column to take the id of that language. The expression id usually I leave a char(n) n between 3 and 7, which gives I use a mnemonic as id facilitating. Also cool is to make a helper to fetch the correct string. From here you can use something: Exphelper("ASKDOWNFILE", 3) that would be something take the expression "Do you want to download the file: {0}?" in Spanish from the bank.

1

How I would do this implementation:

  • It 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 entrees: pt_BR, en_US, es_ES, etc..)
  • The first table would contain a column telling the language that message is in (for example, en_US) and another column referencing the original message (can be the message id in the native language of the application, for example English)

From this, just 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 proposed several solutions and this I think is a very minimal solution, only containing a table with all the messages and another containing the languages (in some cases, we don’t even need this second table, if you put the proper integrity checks in the bank).

  • So, I do not know if I understand, but from what I said the system will have only one table (or two) and the relations part of the system? The static parts of the system I already have a good idea of how to solve, I just do not know the bank part, for example, menus I have in the system are in the database, product categories and so on.

  • I proposed a design of two tables, which could decrease to only one if you want to identify the language in the message table itself. For standardisation purposes, however, it would be interesting to have two. What you can do then is play all the messages to a cache system (e.g., Memcached) and search the message for a function of the type getMessage('this is a text', 'pt_BR');. If the message is not found, you can pick up the fallback language (for example, en_US) or direct print the text that was placed in the function :)

0

I use a table called linguagens where I register language name and shortname (e.g.: en-BR) and the folder where it is located!

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

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

It is possible to save currency, Timezone, everything in this table of languages. Then you recover according to the selection, via user’s cookie. If there is no cookie set, you inform the browser to linguagem default.

-1

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

  • I don’t know about that table, but could it be something that @jean commented on for "expressions" or not? And in this table, thinking, then you can have something like the string that identifies at the end ok.

  • You can separate as well, leaving a table of linkNames_languages to be used in the specific case of the products and another table to be used by the system expressions (messages, Abels, buttons, etc)

  • So @jean from what I understand these static parts of the system (messages, Abels, buttons, etc) I already have a good idea how to do, what I don’t have much idea is the bank part, how to leave the tables, columns, because I have menus, products, product category, articles among many other items that can be translated, and those parts that I still don’t quite understand how to leave.

  • For the data, with certexa, you can create a table of names as I had already explained. It is more practical than creating n columns/table. You can use the same table for both things, but I personally prefer to separate what is data from what is Resources.

Browser other questions tagged php database

You are not signed in. Login or sign up in order to post.