Internationalized App-Free Text Fields Design

[Mark Spritzler]

OK, I know there is a solution that is out there. This has been done before. The System Engineers here seem to want a solution that I don't feel is correct, but I don't have a better way to suggest.

Here's the deal. The Internationization of Language in Oracle is good for Boolean and Numbers, but it the field is a Free Text varchar2(50) where the operator can type in arbitrary text, how do you handle an application that has many languages. English, French, Arabic. So one operator might enter a description in French, and the English guy wants to see it in English.

The specs require that language changing can occur on the front end at any time. Meaning in the middle of data entry they can change the language at any time. Right now they are suggesting having multiple columns in a table to store each language's version of the text, and sometimes worse, the English guy can't see what the French guy typed in their field. I think this scares me

Does anyone have experience with this, how to design the database, the Business Layer, and the Front End. I posted this here in Oracle, since I believe the solution starts with the database and the data model design.

Thanks for the help. I am also searching Google and the likes to see what I can come up with.

Mark
[ January 20, 2005: Message edited by: Mark Spritzler ]

[Lasse Koskela]

Having a multi-language web site is not news from a content management point of view or otherwise. If you have to have every piece of text in multiple languages, you simply can't escape the responsibility of somehow coordinating that the users (content editors) keep their stuff in sync.

Well, in theory, you could have the English text be the master copy and use an automatic translator... That might be interesting for all the non-English users

Regarding the database schema, you basically have multiple localizations of each piece of text. I'd imagine the schema would look a bit like this:

CREATE TABLE I18N_TEXT (
    key VARCHAR2(50) NOT NULL,
    lang VARCHAR2(5) NOT NULL,
    text VARCHAR2(500) NOT NULL,
    PRIMARY KEY (key, lang)
);

That might of course be too limited in many ways, depending on your particular application.

[paul wheaton]

In the early 90's I wrote a C++ app that was translated to 12 languages.

About five years ago I wrote a java applet that used three languages.

Are we talking about an app of some kind or web pages or ... ?

[Mark Spritzler]

It is an App, no Web Pages. For some reason they ruled out a Translator program that would change the text on the fly.

Mark