Multiple Localization of data/tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Multiple Localization of data/tables

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-08-17 : 19:01:32

Hello -
I am working on a re-design of an application that wasn't previously localized at the database level.

The database needs to support the localization for all tables/columns that the end-user can input data into. This application might be installed in a location where users are entering data into the database in multiple languages.

I have searched around and found a few options. Most of them deal with localizing tables that are look-up lists or maybe fairly static data that the user doesn't input.

In our app, the data is more dynamic, with users entering data all the time. So, in one system, one end-user might input data for a customer or person account in French and then another end-user might enter data for the same or different columns in Spanish or English.

I haven't seen a way that really seems to provide good support for this, other than having a table with multiple columns duplicated for each language.

If anyone has an idea/suggestion or pointers to other articles that would be appreciated.

Thanks,
- will

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-08-17 : 20:14:41

Well...I'm not entirely sure what you mean by "localized at the database level" but I assume that we are talking about special characters, time formats and time zones? For special characters I guess the most commonly used option is to use the unicode datatypes for storing text (nvarchar/ntext/nchar)...it will double the size of your data columns but it does support all languages including arabic and such. For time zones my suggestion would be to store all date/times in the same timezone and then change them according to the users locale in the front end.

- Lumbago

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-08-18 : 10:39:04

Thanks for the reply. Not so much time zones, formats or even special chars. My plan is to use nvarchar as a data type where appropriate. No, my question is more about how to design the tables in such a way to support users entering data into database in multiple languages.

For example, suppose there is a table that contains information about a Customer (Name, Contact Name, Phone Number). In our app, someone could access and enter information about the same customer in Spanish, French AND English.

So, here was my intial thought of a possible design:


CREATE TABLE Languages
(  LangID int,
   Language nvarchar
)

CREATE TABLE Customer
(  CustID int,
   CustType int, 
   CustTimeZone int
)

CREATE TABLE Customer_Localized
(  CustID int,
   LangID int,
   CustomerName nvarchar (200),
   Contact_FName nvarchar (100),
   Contact_LName nvarchar (100)

The PK for the Languages table is the LangId and the PK for the Customer table is the CustID. For the localized Customer table (Customer_Localized), I am thinking that the PK would be a composite of the CustID and the LangID.

I am not completely sure, but with this design, I think that we can support multiple languages for the same Customer. Also, for installations where there will only be one language, there won't be any extra rows of data.

Anyway, not sure, as I am still experimenting with this idea. But just curious if others have any additional thoughts?

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-18 : 11:28:52

if just english, spanish and french, no need to use unicode data types

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-08-18 : 12:18:29

we need to support english, spanish, french, german, chinese, japanese, arabic...etc!

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-08-18 : 13:23:27

Just wondering too...is it possible to support languages like English, Spanish and French in the same table/database as languages like Russian, Chinese, Japanese?

I manually inserted some data into a table that has values in English, French and these displayed fine when I run a select on the table. But the values I inserted as Russian and Japanese are displayed as '?????'.

Is there something else I need to do to get the values to be stored and displayed correctly for each language? I did set the data type for the column as nvarchar.

thanks
- will

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-08-18 : 14:27:14

I think i figured out one way to solve my issue of the question marks. If I include the N prefix before the string, then the values are stored and displayed correctly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources