Multilanguage and data redundancy

[jbenezech]

Hello,
I would like to have your opinion on the best possible database design for the following problem:
I need to build a small database where the main table will contain about 20.000 records.
The application needs to be multilangual (2 languages at the moment but maybe more later).
The data need to be in many languages as well: the custumer wants to be able to view/print the data in different languages. This concerns every single character data in the database. The data will be first entered in one language and then will be translated in other languages.

possible solution could be:
- to have a database clone for each language. But that solution seems difficult to manage as the original data can be entered in any of the possible language.

- To link each table with itself in a "data translated" relation and duplicate the data in the same tables.

- ....

Any idea would be greatly appreciated.
Cheers,
Jerome

 

[MrTibbs]

I have seen this done with tokens

I have seen this done, fairly effectively. Every piece of text was replaced with a token e.g. "Data Entry" was expressed as token1.
Take care because: Variable text lengths can affect screen design / report output etc. Currency and date formats change depending on language e.g. most EU languages use comma to separate a number from decimal places. English uses full-stop. For true multi-lingual operation you must change the formats to suit the language and be sure to store the data in a neutral format.
Regarding data entry in multiple languages - this will not work automatically (just try auto-translating a common phrase into another language then back again .. the results can be hilarious).
I suggest you store the original input language code with the original data entry then ensure your translation table gets populated, or at least checked, manually.
You may even have to hold all your data in a 'translation' file and build your information searches based on tokens.
This sounds like a horrible project. The only similar one I have seen had the American design team insist all data was to be entered in American and US Dollars. For reporting it could be converted to any desired language / currency but the underlying data was only in American.

 

[MStCyr]

Ive posted a fairly good sample of a bilingual database in the sample databases area. This may be of some help

http://www.access-programmers.co.uk/forums/showthread.php?t=103594

 

[Ron_dK]

The data will be first entered in one language and then will be translated in other languages.

I think that thats the core of your question. What do you use to translate the data from lang1 to lang 2. Is this a translator IN the Dbase or OUTside the Dbase.
If it's outside, the sample of MStCyr seems to be pretty good application.
Translate inside the DB ? Wouldn't know how to do that .

It also depends on the number of tables that you have. If you have only a few , the translation thing is simple and again the MStCyr sample would suffice, but if you're talking about loads of tables, em........