From: | Antonios Christofides <A(dot)Christofides(at)itia(dot)ntua(dot)gr> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Database design problem: multilingual strings |
Date: | 2003-06-24 17:15:44 |
Message-ID: | 20030624171544.GA1839@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2003-06-24 17:27:04 | Re: postgres 7.3.3 problem - not talking across port |
Previous Message | Mike Benoit | 2003-06-24 17:15:26 | Re: [GENERAL] interesting PHP/MySQL thread |