Re: Multilanguage application & database

"K" <kristofvdw_at_matt.es> wrote in message news:ef74fcf7.0410220525.5a22ee07_at_posting.google.com...
> Hi,
>
> We have to create a multilanguage application; meaning that tables in
> the database with fields like "description", "label", "name", etc.
> need this information in more than 1 language, so it can be shown to
> each user in his own language. So I'm NOT asking for different
> character sets (I've found dozens of posts about that).
>
> e.g.: EMP(IDEMP, NAME, IDDEPT) >---- DEPT(IDDEPT, NAME)
> Some EMP's row: IDEMP: "1", NAME: "A.KING", IDDEPT: "1"
> Some DEPT's row: IDDEPT: "1", NAME: "ACCOUNTING"
> Some result for English user: "A.KING works at ACCOUNTING"
> Some result for Spanish user: "A.KING trabaja a CONTABILIDAD"
>
> POSSIBILITY 1:
> Putting in a field for each language isn't really possible, since we
> don't know how many languages the application will use (and new ones
> might be added later).
> -> DEPT(IDDEPT, NAME_EN, NAME_SP, NAME_NL, ...)
>
> POSSIBILITY 2:
> Putting a foreign key to a language table doesn't seem possible, since
> every language would need a row in the resulting table. We'd need a
> composite key of IDDEPT and IDLANG, which results in the link to EMP's
> foreign key being lost (or we should remove the foreign key constraint
> and manage it ourselves, but that means removing that what's Oracle
> all about)!
> -> DEPT(IDDEPT, NAME, IDLANG) >---- LANG(IDLANG, NAME)
> DEPT's row (1): IDDEPT: "1", NAME: "ACCOUNTING", IDLANG: "1"
> DEPT's row (2): IDDEPT: "1", NAME: "CONTABILIDAD", IDLANG: "2"
> ...
>
> POSSIBILITY 3:
> Thanks for your suggestions, guys!
>
> K

First, I haven't tried the solution below. Evaluate at your own risk!

An oft overlooked possibility is to use structures that only support a single language, and install different language versions into different schemas. Of course this has limited applications, so it really depends on the business case for multiple languages.

Something that should work well in conjunction with the above method is to split tables in two. One table containing non-localizable data, and a related table holding the locale specific data. Only the locale-specific table needs to be deployed into multiple schemas. Access to the remaining non-localizable tables are granted to each user. Users connect to the schema of their language in order to get the "local" version.

An interesting side effect of the above is that not all data is relevant in all languages. For example, a retailer selling products over the internet, might not want to sell Americana on Spanish web-site. So, instead of having to create business rules that control which items are available under which locale, the absence of a translation into that locale prevents the product from being listed.

One thing about multi-lingual data that always throws me for a loop is how to get all of the languages created as the information is entered into the application. Unless you're lucky enough to have users that speak all of the languages, there are hoops to jump through in order to get it done. How do you manage this?

Hope that helps.

Tom Received on Fri Oct 22 2004 - 16:36:42 CDT