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.