Best practice for storing multilingual strings

Question

I need to store different versions of not very long lines for different languages ​​(2-4 languages) in a Postgres table.

What is the best way to do this? Array or JSON or something like that?

Answer 1

First, make sure the database can work with different languages. Use UTF-8 server encoding. If necessary, set LC_COLLATE = 'C' to neutral ground or use sorting for your first language to have a default sort order. Start by reading the Sort Support chapter in the manual.

I would highly recommend using the latest version of PostgreSQL (9.1 at the time of writing) because it has excellent sorting support.

Regarding the structure of the table : keep it simple. It seems that there is a small, fixed number of languages ​​to work with. You can only have a column for each language:

 CREATE TABLE txt ( txt_id serial PRIMARY KEY ,txt text NOT NULL -- master language NOT NULL? ,txt_fr text -- others can be NULL? ,txt_es text ,txt_de text ); 

It is quite effective, even in many languages. NULL storage is very cheap.
If you have a different number of languages ​​to work with, a separate table may be the best solution. This solution assumes that you have a "main language" where the line is always present:

 CREATE TABLE txt ( txt_id serial PRIMARY KEY ,txt text NOT NULL -- master language NOT NULL? ); CREATE TABLE lang ( lang_abbr text PRIMARY KEY -- de, es, fr, ... ,lang text NOT NULL ,note text ); 

Or, if the (two-letter) abbreviation is enough, simply create an enum type to identify the language.

 CREATE TABLE txt_trans ( txt_id int REFERENCES txt(txt_id) ON UPDATE CASCADE ON DELETE CASCADE ,lang_abbr text REFERENCES lang(lang_abbr) ON UPDATE CASCADE ,txt text NOT NULL -- master language NOT NULL? ,CONSTRAINT txt_trans_pkey PRIMARY KEY (txt_id, lang_abbr) ); 

Not processing the special language of the wizard and saving all variants of the language in one table can simplify the work with your application. But it really depends on your requirements.