Database design problem: multilingual strings - Mailing list 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.