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