[orm-devel] How to handle multi language?

[Ross J. Reedstrom]

Hi Deidriech and Werner - 

I've been lurking here as well, so don't think you're talking in a
vacuum. Haven't had a chance to use ORM much recently, but we _have_
been thinking aobut translation issues.

On Tue, Jun 21, 2005 at 01:18:58PM +0200, Werner F. Bruhin wrote:
> >Anyway, I'd like  to have one row per country, though dispite the face 
> >that there are  three country names, it's only one country.
> 
> You are right there should only be one row per thing (country in this 
> case), I am not really happy with my solution nor with your's, so it is 
> probably back to the drawing board.
> 
> I use gettext for the program labels, maybe one could base it on their 
> approach. 
> 
> The text to translate becomes the "ID" to find the translation, i.e. 
> have a column type "TRANSLATE" which does a lookup in a table containing 
> translations if the language ID is found for the "ID" that entry is used 
> otherwise the "ID" is used.

I think this is a reasonably good idea, except for using the ID as the
default translation: even with gettext, if the string is reasonably
long, its common practice to assign an actual ID. I think for a real
database, that's the right way to go from the beginning. So defaulting
becomes using a COALESCE, with one column per language, in order of
preference:

SELECT COALESCE(gr_tran,fr_tran,en_tran) as txt FROM texts WHERE txtid = 123;

would return the first non-NULL translation for an order of
pref: German, French, and English.

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm at rice.edu
Research Scientist                                  phone: 713-348-6166
The Connexions Project      http://cnx.rice.edu       fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE
Ross