The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Multi-language SQL Server question

OK, as a follow-up on my question below about multi-language support in VB6, has anyone tried the following (and if so/not, is this practical?)?

The problem is that for example a "Automatic transmission low-gear clutch plate" is only called that in English.  In other languages it has a different name.  How to accommodate that?

Here is one idea:

Table PartsMaster
PartUID GUID
BlahBlahBlah

Table WarehouseMaster
WarehouseUID GUID
BlahBlahBlah

Table Languages
LanguageID
LanguageCodePage (?)
BlahBlahBlah

Table Identifiers
TableName
ItemUID
LanguageID
RecordID
RecordDescription

Users would enter the languages they want to support in the Languages table.

The Parts table master form, for instance, would have a grid of codes/descriptions that users could enter in their local language.  A "default" would exist if no local-language description has yet been entered.

To simplify use on reports and in data entry forms, like an Invoice form, all master file items would be accessed via a view that accepts the local user's language and returns data in that language.  By using the view the entire application interface can have a consistent strategy for retrieving locale-specific data.

My biggest questions are:

- In non-English speaking countries, are users content to look at descriptions in either English or some other "only" language (thus making this idea moot)?

- Would the JOINs required of this strategy bring a large multi-user application to its needs even if the DB is otpimally indexed?

Comments?
Karl Perry Send private email
Sunday, October 28, 2007
 
 
Karl

I think if you were to integrate all the different languages into your one database structure, it could get complicated and your database would be huge.

You haven't indicated whether you are doing separate versions in separate languages or whether you have one single multilingual version (not recommended - why would a Japanese want to suddenly switch to an English interface).

Have one database structure, code for that structure and copy an empty version of it (without any data) for each separate app version.
Ezani Send private email
Monday, October 29, 2007
 
 
You won't repeat the complexity everywhere.  You only need translations for descriptions, field labels etc. in look up tables. (separate the description from the item.)  Then you can reuse descriptions in multiple places.

productlookup table
  productid
  descriptionid
...

partslookup table
  partid
  descriptionid
  unitprice....

Languages table
  Languageid
  description (so you have a list of drop downs)
    flagimage (so you can have a cute flag image for the person to choose their language)

Descriptions table
  Descriptionid
  languageid
  Description or text
JimK Send private email
Monday, October 29, 2007
 
 
I guess I didn't do a good enough job of explaining.

I know that the field labels and form explanations, etc. will have to be stored - somewhere.

Ezani, we have clients with both English and Spanish speakers in the same location.  In addition we anticipate sales to companies with divisions in France, Germany and Italy for example, with users in different places expecting different languages.  Is the Italian computer user going to be ok with seeing some part s/he is trying to pick from a list be spelled out in English (or French, or German) description field for some widget, or is s/he going to expect that the list of parts be presented in Italian?

What I was trying to ask was, have people found the need to make database elements multi-lingual within the same installation?  Not just field labels and other fixed text, but the actual names of "things" that users will put into a database, i.e.:

- Inventory part codes and descriptions.
- Vehicle types ("car", "truck", etc. in English, whatever they are called in other languages).
- Payment types.
- Other data.

So this is NOT a question about translating form labels, it's a question about the need for the text in the database data itself to be multi-lingual, and whether my scheme for storing this text in a separate table makes sense from a performance standpoint?
Karl Perry Send private email
Monday, October 29, 2007
 
 
Our app is for stocks and supports both English and French users. We have the same idea, eg. one table with the non-language specific info in it, one record per stock, and another table with a record for each combination of stocks in the first table and language.

This works fine. What is a much bigger problem is all the little places in our app where OTHER language-specific text exists. For example, some tables have status fields in them in the form "Posted", "Pending" etc. Ideally these should all be language-agnostic codes that get translated at runtime.
Greg Send private email
Monday, October 29, 2007
 
 
Karl

I was just about to say what Greg said above. For your initial proposed structure using Description ID, its fine if you just wanna have the Product Description translated or you have only a few description fields per form translated - then you can have Language IDs. Problem is you would probably need to translate the whole screen (including Status combos, menus and online help, etc so it would not just be a database issue alone) so Product or Stock Codes would ideally also have to be translated and Pricing and Currencies,etc. I don't know how well this is taken care of by changing the Regional Settings but the user probably doesn't want to change Regional Settings evertime a language switch is required.

I see your point. May I propose :

APP INTERFACE --> TRANSLATOR MODULE --> DATABASE STORAGE

Translator Module could be in the form of DLL or a third party app so you just only need to store all data in English?
Ezani Send private email
Monday, October 29, 2007
 
 
Sorry, the arrows works both ways.
Ezani Send private email
Monday, October 29, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz