How to create a database schema for storing text in several languages?

Question

We have a PostgreSQL database. And we have several tables that should contain certain data in several languages ​​(the list of possible languages, fortunately, is determined throughout the system).

For example, let's start with:

create table blah (id serial, foo text, bar text); 

Now let's make it multilingual. What about:

 create table blah (id serial, foo_en text, foo_de text, foo_jp text, bar_en text, bar_de text, bar_jp text); 

This is good for full-text search in Postgres. Just add a tsvector column for each language.

But is this optimal? Maybe we need to use another table to save translations? How:

 create table texts (id serial, colspec text, obj_id int, language text, data text); 

Maybe, maybe we should use something else - something from the SQL world? Any help is appreciated.

Answer 1

I think it is best to create two tables. One for languages, one for identifiers, etc. first_table (id) second_table (s_id, id_first_table, language_id, language_text)

Answer 2

Here's a great article that Mozilla developers put together to make their database multilingual. This is specific to CakePHP, but the information can be easily applied to other systems. Also note that SQL queries are much more complex, which is a drawback. This is usually true, regardless of the i18n implementation.

• Part 1
• Part 2
• Part 3