Re: Database design problem: multilingual strings

You might want to try abstracting the languages further, and have a central
store of strings for everything.

E.g. something like:

languages
id   handle   ------------
1    en
2    fr
3    es

strings
id   handle
-------------
1    FORK
2    SPOON
3    LARGE_ESSAY

string_translations
id    string_id     language_id     translation
--------------------------------------------------
1     1             1               Fork
2     1             2               Forchette
3     2             1               Spoon
4     2             2               Cuilliere
5     3             1               This is a large essay in Engligh. Same
would apply in any other language.

cuttlery_types
id    description_string_id
-----------------------------
1     1
2     2


Then you can use a query like this to get an array of cuttlery types in any
language:

SELECT cuttlery_types.id FROM cuttlery_types,
       string_translations,
       languages
 WHERE cuttlery_types.description_string_id=string_translations.string_id
   AND string_translations.language_id=languages.language_id
   AND languages.handle='fr';

Just swap the 'fr' for 'es' or whatever for a different language. Use the
same central store for all other text fields in the database that needs to
be language-independant. If you want to allow for missing values just use a
LEFT JOIN or similar. You could also write a fairly simple query to give
you a list of missing translations, which could be handy.

HTH,

Russ.