How to design a database schema for storing text in multiple languages?
- by stach
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.
