Localization issue

[iceriver]

Hi All,
I already have a web site running with SQL Server as a backend (in
english)For future growth, I would like to make it localized. Regarding
the database, I have come up with several approaches.
1) just simply add the column in those table which needs different
language.
2) add additional tables to do it.
3) create a new database to store different language's information
As mentioned, my database have already been implemented, so the minimum
modification is preferred. Could you guys suggest me the best way to do
it?
Another thing, if I alter my existing database into UTF-8 now, will it
affect the original data (ie. English).
Thanks.
Ice

[Erland Sommarskog]

iceriver (hf***@hotmail.com) writes:

I already have a web site running with SQL Server as a backend (in
english)For future growth, I would like to make it localized. Regarding
the database, I have come up with several approaches.

1) just simply add the column in those table which needs different
language.

2) add additional tables to do it.

3) create a new database to store different language's information

As mentioned, my database have already been implemented, so the minimum
modification is preferred. Could you guys suggest me the best way to do
it?
Adding multi-language support is a task with an impact. Without knowing
the nature of your database it is hard to give recommendations.

In the system I work with, we once faced this problem. At that time,
many our tables had two name columns, for instance countryname and
countrynamefor, holding the Swedish and English name respectively. As
we entered the Finnish market, we needed support for a third language,
since in Finland, both Swedish and Finnish are official languages.

After some discussion, we decided to take the big step: the names were
moved out specific name tables. For instance the countries table
would get a subtable countrynames with the key (countrycode, languageid).
For simplicity we did keep a name column in the main table, so we
can use that as a fall back if there is no name in the current language
in the name table.

Adding an extra column may be easy for the first language you support,
but if you add specific tables, you have the infrastructre built for
your third, fourth language etc.
Another thing, if I alter my existing database into UTF-8 now, will it
affect the original data (ie. English).

There is no support for storing data in SQL Server as UTF-8. To store
Unicode data, use UCS-2. (The same as UTF-16, but SQL 2000 does not
support surrogates.) That is the nchar/nvarchar datatypes. Note that
depending on which languages you plan to support, you can still make it with
char/varchar.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[iceriver]

Erland,

Thanks for your reply. I am wondering if there is any sample DB schema
available on the web. I would like to see an example that how several
related-tables also need to be localized. Any suggestion to re-arrange
those existing relationship? I am kinda new to localization problem,
please give me some hints.

"After some discussion, we decided to take the big step: the names were
moved out specific name tables. For instance the countries table
would get a subtable countrynames with the key (countrycode,
languageid).
For simplicity we did keep a name column in the main table, so we
can use that as a fall back if there is no name in the current language
in the name table."



Erland Sommarskog wrote:

iceriver (hf***@hotmail.com) writes:

I already have a web site running with SQL Server as a backend (in
english)For future growth, I would like to make it localized. Regarding the database, I have come up with several approaches.

1) just simply add the column in those table which needs different
language.

2) add additional tables to do it.

3) create a new database to store different language's information

As mentioned, my database have already been implemented, so the minimum modification is preferred. Could you guys suggest me the best way to do it?
Adding multi-language support is a task with an impact. Without

knowing the nature of your database it is hard to give recommendations.

In the system I work with, we once faced this problem. At that time,
many our tables had two name columns, for instance countryname and
countrynamefor, holding the Swedish and English name respectively. As
we entered the Finnish market, we needed support for a third language, since in Finland, both Swedish and Finnish are official languages.

After some discussion, we decided to take the big step: the names were moved out specific name tables. For instance the countries table
would get a subtable countrynames with the key (countrycode, languageid). For simplicity we did keep a name column in the main table, so we
can use that as a fall back if there is no name in the current language in the name table.

Adding an extra column may be easy for the first language you support, but if you add specific tables, you have the infrastructre built for
your third, fourth language etc.

Another thing, if I alter my existing database into UTF-8 now, will it affect the original data (ie. English).
There is no support for storing data in SQL Server as UTF-8. To store
Unicode data, use UCS-2. (The same as UTF-16, but SQL 2000 does not
support surrogates.) That is the nchar/nvarchar datatypes. Note that
depending on which languages you plan to support, you can still make

it with char/varchar.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[andy]

iceriver wrote:

Erland,

Thanks for your reply. I am wondering if there is any sample DB schema available on the web. I would like to see an example that how several
related-tables also need to be localized. Any suggestion to re-arrange those existing relationship? I am kinda new to localization problem,
please give me some hints.

Why change the database, other than just for the different language?
I'd suggest that keeping table and field names in the original language
would be simplest.
It's the user interface really needs to have local name of fields and
(perhaps) localised error messages.
How you'd do this best depends on what the user interface is
specifically written in.

One method...
Stick these in an xml file distributed with the app or a table in the
database with a structure something like
Language,
Message_No,
Message
Use something in the app to specify the language your user has and look
up the entry in the message field for each text box/error.
Potentially a fair bit of work. The last app I wrote is multi-national
but the company's standard language for computer systems is english for
this reason.

You also have to remember to handle different formatting of dates,
numbers ( comma or full stop as decimal place ).

[Erland Sommarskog]

iceriver (hf***@hotmail.com) writes:

Thanks for your reply. I am wondering if there is any sample DB schema
available on the web. I would like to see an example that how several
related-tables also need to be localized. Any suggestion to re-arrange
those existing relationship? I am kinda new to localization problem,
please give me some hints.

I have no idea what is out there. But I have a strong feeling that the
answer depends on your business, and you are asking me question about a
database I don't know anything about. What was the right answer for us
may not be for you.

But there is one thing I forgot to mention in my prevoius reply: the
database is the easy part. The hard and expensive work is all the
translation. With a good architecture, you can easily add an other
language from a technical point of view. But the translation work will
be the same each time. And the maintenance of all languages will increase
for each new language you add.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[iceriver]

Andy: Thanks, I also heard about the XML stuff may help, but I don't
know too much details in it.

Erland: Thanks, how a architecture and language addition will increase
the maintenance load?

If you guys encounter any good book/web reference regarding the
localization issue, please feel free to let me know. Thanks so much.

[iceriver]

Andy: Thanks, I also heard about the XML stuff may help, but I don't
know too much details in it.

Erland: Thanks, how a architecture and language addition will increase
the maintenance load?

If you guys encounter any good book/web reference regarding the
localization issue, please feel free to let me know. Thanks so much.

[Erland Sommarskog]

iceriver (hf***@hotmail.com) writes:

Erland: Thanks, how a architecture and language addition will increase
the maintenance load?

You don't localize once. As you change your site, you will have localize
all those changes as well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp