Re: Database design problem: multilingual strings

In looking at your ideas, a thought came to mind.
This issue is something I've been looking at neediing to address, so any comments are welcome.

Whatever the first entry of a string entered becomes the reference string. You could add a field for that.The design below needs a unique index on:

Translations( string_id, lang_id );
Translations( string_id, lang_id, lang_string );
Languages ( lang_name_full_eng );
Languages (iso_latin_abbrev );

CREATE TABLE StringIDs(
string_id serial NOT NULL PRIMARY KEY
);

CREATE TABLE Languages(
lang_id serial NOT NULL PRIMARY KEY,
lang_name_full_eng varchar(30) NOT NULL,
iso_latin_abbrev varchar(2) NOT NULL,
);

CREATE TABLE Translations(
string_id INT4 NOT NULL,
lang_id INT4 NOT NULL,
lang_string BYTEA NOT NULL
);

ALTER TABLE Translations
ADD CONSTRAINT FK_translations_string_id
FOREIGN KEY (string_id)
REFERENCES StringIDs (string_id);

ALTER TABLE Translations
ADD CONSTRAINT FK_translations_lang_id
FOREIGN KEY (lang_id)
REFERENCES Languages (lang_id);

The design above needs a unique index on:

Translations( string_id, lang_id );
Translations( string_id, lang_id, lang_string );
Languages ( lang_name_full_eng );
Languages (iso_latin_abbrev );

Antonios Christofides wrote:

> Hi,
>
> I'm designing a database with a web interface, which will be
> accessed by international users. The French may be requesting/entering
> information in French, the Greeks in Greek, and the Japanese in
> Japanese. I want every string in the database to be multilingual.
> Let's use a hypothetical example:
>
> simple lookup table cutlery_types:
>
> id description
> ----------------
> 1 Spoon
> 2 Fork
> 3 Knife
> 4 Teaspoon
>
> 'description' is no longer enough; it must be possible to add
> translations to _any_ language and to any number of languages.
> I've thought of a number of solutions, but none satisfies me to the
> point that I'd feel ready to die :-) I'd much appreciate
> comments/experience from anyone. I include the solutions I've thought
> of below, but you don't need to read them if you have a good
> pointer in hand.
>
> Thanks a lot!
>
>
>
>
> Solution 1
> ----------
> table cutlery_types_description_translations
> id language translation
> --------------------------
> 1 fr Cuilliere
> 1 el Koutali
> 2 fr Forchette
> 2 es Tenedor
> (or language can be id fk to languages table)
>
> Clean solution, but... an additional table for each string in the
> database?! The 50 tables will quickly become 300 :-(
>
>
> Solution 2
> ----------
>
> translations
> id language translation
> -----------------------------
> Spoon fr Cuilliere
> Spoon el Koutali
> Fork fr Forchette
> Fork es Tenedor
>
> Not possible, because it uses the English version of the string as an
> id. What if the English version is a 300-word essay? What if the
> English version changes? What if no English version exists for that
> particular string?
>
>
> Solution 3
> ----------
>
> cutlery_types
> id description
> ------------------
> 1 { "Spoon", "Cuilliere", "", "Koutali" }
> 2 { "Fork", "Forchette", "Tenedor", "" }
>
> Where, obviously, a languages table tells that 1 is English, 2 is
> French, 3 is Spanish and 4 is Greek. One of the problems with this
> solution is that if I want to add a translation for language 45, I
> need to insert an empty string for the previous 44 languages.
>
>
> Solution 4
> ----------
>
> cutlery_types
> id description
> -------------------
> 1 Some way to represent a hash: 'en' => 'Spoon', 'fr' => 'Cuilliere' etc.
> 2 'en' => 'Fork', 'fr' => 'Forchette', 'es' => 'Tenedor'
>
> The description could be, for example, a TEXT containing all
> translations separated by some kind of separator, or an array whose
> odd elements may be the hash keys and the even elements the
> translations. In any case,
> SELECT id, getstring(description, 'el') FROM cutlery_types
> would use the user-defined function getstring to retrieve the needed
> translation. Far from certain on how efficient it can be done.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>