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

depends on what your applciation is, and what its going to run on.

In the past I have implemented single language per db, but more recently I prefer multi language in one global db. Lots of reasons
-maintainability (any change to the app happens once in once db, one version to roll out/support), bug tracking is easier....
-extensibility (if a customer wants a new language we/they 'just' provide the translations and the db is then extended, if a new language is required its an addition ot the existing product
-some sites/apps are multilingual so there is a deployment benefit in that the app doesn't care who is running it it can switch to the desired language (this can be triggered by user id or selecting icons (eg flags or from a combobox)

some downsides
you have to be very carefull with display sizes (esepcially with text boxes & command buttons, as some languages (mostly non English European languages can take a great deal more space than English)
you may have a problem with non alphabetic (A-Z) languages (eg Chinese, Japenese, Arabic etc...), some of which display bottom to top, right to left. Fortunately I haven't had to deal with this yet so have always avoided the issue.
takes more resurces and the apop my well be slower / more processor/network hungry (some of that can be minimised by utilising local tables to store the local language if your app permits it)

it depends on your development environment / front end. But most environments allow you to populate virtually any form or report at run time. Its pretty easy in Acccess, VB & .NET. You need to either calucalte the maximum text width at load time to adjust the display size (either font/font size or display width)

if you are developing in VB .net C++ then you could use resource tables to handle the application translation, but you still have the requirmeent to translate your data

Its going to take much longer to design and test the application, but I would honestly say if you are going to support more than one language then unless your customers have the budget I wouldn't go down the route of separate language per db.

Thanks for that reply healdem.
The Software will be front/back end in MS Access and there will be caracters/fonts issues as 1 language is Khmer (I know, not even fully supported yet). But for now, I concentrate more on the data model than the presentation.
So if I follow what you suggest, I should store all data in the same tables:
- 1 table DataTable that contains both English and Khmer Data
- 1 link table "translated in" that contains (DataTable_PK_Language1, DataTable_PK_Language2, LanguageCode)
- I apply this model to all the tables in the DB
- When Data is translated, I need to update all the link tables to point all the existing records to their translation

That does seem like a lot of work on the insert/delete but - in my case - they are not frequent so that should not be a big issue. What concerns me is the update and the integrity of the data that are not language dependent: for example one data might be "length of the pipe" which would be a number stored in Latin number. When an update is made on one of the language records (English), the same data in the other language will not be updated (unless lots of manual work / code writing / trigger).

Cheers,
Jerome