How to design a database schema for storing text in multiple languages?

Question

We have a PostgreSQL database. And we have several tables which need to keep certain data in several languages (the list of possible languages is thankfully system-wide defined).

For example lets start with:

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

Now, let's make it multilingual. How about:

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

That would be good for full-text search in Postgres. Just add a tsvector column for each language.

But is it optimal? Maybe we should use another table to keep the translations? Like:

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

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

Answer 1

I think it is best if you create two tables. One for languages, one for ids and so on. first_table( id ) second_table( s_id, id_first_table, language_id, language_text)