Managing multilingual data

Hi All,

I want to develop a multi-language website which captures both English and Chinese data. I would like to know how I can handle multi-language data within a single database (For example, English users will enter the "Name" in English whereas Chinese users will enter "Name" in Chinese). I heard that this could be managed via SQL collations. Please help me with more details.

Thank you

Hi,

How about making use of Resource files / Satellite Assemblies? if the data is common and not much?

Thanks,

Vikram Pendse.

Microsoft MVP

http://pendsevikram.blogspot.com

Hi Vikram,

I am using resource files to manage multilingual GUI. I am, however, looking for a method by which I can save multilingual data (user input) onto a SQL Server DB. For example, let us take an application that takes user name and address as input.English users will enter the data in English and Chinese users will do the same in Chinese. Can I save both types of data onto a single database? If so, how?

Thank you again for your support and co-operation.

If you really wish to use sql server for saving translations use a table structure like this:

id - key - languagecode - value
-------------------------
1 - title_hello - EN - Hello
2 - title_hello - DE - Halo
3 - title_goodmorning - DE - Guten Morgen
...

Thank you, that was a good one.

Just to make the concept clearer, is it possible to save date which is in a language that contains special characters? For example Chinese, Arabic, French etc. Does the concept of collation has anything to do with this scenario?

Better reformat it to DateTime before you save.

But, I have more than one field to be saved. I have numerous fields like name, address, qualification etc. All these are of types nvarchar.

Are you saving all fields in same field?

Anyway, no problem even if it is single field. Just cast it to .NET DateTime format and save it. When you retrieve you can convert it to a desired culture format from UI.

Let me make my point clearer.

I have a table by name TBL_MEMBER which has mainly 4 fields, NAME, ADDRESS, FATHER'S NAME, DOB. All these fields except DOB are specified as of nvarchar datatype and DOB is of datetime type. My users can enter data either in English or in Chinese. I want to save the data onto a single table. As you mentioned previously, I can manage this by adding seperate table columns that specify language or culture. My question is, can I save the Chinese characters onto the same table? Except for DOB field, I can't make any DateTime format casting.

Thank you for your support.

>> can I save the Chinese characters onto the same table?

Yes. By plaing with COLLATION

But nvarchar datatype can contain Unicode characters right, then why we need COLLATION?

That means, I will have to keep 2 name columns: one for English names and one for Chinese names. For the former, I can have default collation and for the latter I need to use a collation that supports Chinese characters. If the user's culture is supporting Chinese language, I need to retrieve data from Chinese columns and vice versa.

This gives me something to work on.

Thank you for your valuable suggestions.

Does that mean I can save Chinese characters directly onto the same column?

If it is possible, then only one column would be required, which contains English and Chinese data.

If so, in which scenario do we need to use Collation property?

>>But nvarchar datatype can contain Unicode characters right, then why we need COLLATION?

I thought he was having issues with existing collation. Yes nvarchar is unicode.