Good morning. I have an interesting scenario where one of the requirements of the application is to translate an application to different languages. Modelling the system to translate the UI went over without a hitch, however, we have now been told that the application will need to translate all the existing database tables. Has anyone ever had to do anything like this?
I was curious if anyone out there has any suggestions on how to approach this issue... ways in which it could be modeled, alternative approaches, anything at all would be appreciated. Many thanks in advance!
Do you need to change the schema (tables, indicies, etc), the data in the tables, or both?
I would STRONGLY advise against changing the schema (especially the object names), with the possible exception of adding a language_id column to the tables where it was needed. This is a reciepe for disaster!
If you need to translate the data in the tables, you need to get some advice from a competent linguist... Not just someone that knows the target language of the translation, but someone who understands language itself because there are many pitfalls to avoid in this process, and any given translation will probably only find some of those pitfalls (with the possible exception of American English to Mandrin Chinese).
This is an area that is near and dear to my heart... I've had to do several of these. It is not a task for the faint of heart, but it can be very rewarding when done well.
The schema would have to change whether we would add a column which was my suggestion or to use the existing structure which was modeled for the UI translations. Currently our structure for the UI looks like this:
LanguageCode - PK, FK
TextCode - PK, FK
The TextCode is embedded in the application and then transformed through the parser when rendering.
It has been argued by internal personnel that the existing text be moved to the ApplicationTranslation table, where as I felt it would be best to just add a Language column to each table and let the end users enter the information in their native language while specifying which language it was targeted too using the Language column. To me this approach seems to have far fewer implementation impacts than the other method.
Since this is an area in which you have experience do you have any further words of advice? Any words of advice on Collation? Do's or Don'ts? Once again thanks for your input!
1 Use multiple tables with a language id, as you'd suggested.
2 Have a default language, preferably the one used by the developers.
3 Have a language table, giving the language names in Unicode in both the default language and the native language.
4 Track language preference by session, to allow for multi-lingual access at the same time.
5 If a native expression isn't available for a request, use the default language.
6 Watch out for languages that expect different orientations (English uses right/left-top/bottom, not all languages work that way).
7 Be careful with color... It can mean very different things in different cultures (red means danger in English, a passing comment in Japanese).
8 Make EVERY text column that even might be presented to a user support Unicode.
9 Get someone on staff that understands the target markets, ASAP! Microsoft's experience with XP sp2 in Latin America is a case in point!