Database design problem: multilingual strings

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.

Responses

Browse pgsql-general by date

  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