Re: Database design problem: multilingual strings - Mailing list pgsql-general

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
belowneeds 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
>