Localization Question

[Matthias S.]

Hi everybody,

I'm starting to design a Portal-Style application which should support a
couple of languages. What I figured is that one could use various resx
files to localize more or less "static" information such as labels and
short text blocks.

But what about Lookup-Table-Data stored in the SQL Server. Say I have a
table containing Priorities ("High", "AboveNormal", "Normal",
"BelowNormal", "Low"). This table is used to fill a DropDown in some
place. How would I go about localizing this? Sure, I could use a table
containing a StringID column and one Column for each Language supported.
But this seems a bit awkward to me, since I would have to add a new
column for each new language and besides that, when adding a new
language, resource files would have to be maintained as well as the
database table (adding a new column and translating the strings).

And what about pages, that largely consist of text (html formatted)?

How is this done in real-world applications? Best thing would be to have
all localization in one place, so that the effort to add a new language
can be minimized.

Any suggestions are very welcome!

Matthias

 

[Karl Seguin]

Matthias, take a look at this two part tutorial:
http://openmymind.net/index.aspx?documentId=3
http://openmymind.net/index.aspx?documentId=4

the 2nd one deals with database structure. However, reading the article
you'll see I'm still talking about a 2 store approach (XML and database).
You can work this both ways....instead of using XML you could use a
database for your localized information (a number of people have written me
and asked what I thought aobut it (I think it's a great idea). Or, in your
database you could simply store a sinlge "ValueKey" column which you use to
fetch the real value form the XML value via ValueKey...

Karl

 

[Matthias S.]

Hi Karl,

thanks for your posting. I've read the articles and found them very
helpful. I think I'd go for the single store attempt (for maintenance
reasons), that is, storing all values in one centralized table, e.g.

Table [Priorities]
- ID (holds the Priority ID, auto)
- ResourceStringID (relating to the ResourceStringIDs Table)

Table [ResourceStringIDs]
ID (id of the resource)
Locale (e.g. en-US)
Value ("High")

In this case I could store all string-resources in a single table. You
mentioned limited rownumbers in sql 2000... well, I have to look this
one up.

Besides that, I'm concerned about performance issues. But maybe I could
come up with a wrapper object, which would fetch all strings related to
a certain page (and certain CultureLCID of course) and cache it away. Do
you think this approach would make sense?

Anyways, your article was an excellent starting point.

Best wishes and keep up the good work!

Matthias

 

[Karl Seguin]

If your application is large enough, there's no doubt that caching could
become a challenge. I'd call it a problem, but there are certainly caching
methods you'll be able to use....I mostly write french/english applications
where english is the dominate language..as such it would make sense for me
to cache just the english content (remove the hit from the most frequently
accessed stuff). Or you could slice it your way, per page, where some pages
might get seen very rarely so why bother caching those?

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

Hi Karl,

thanks for your posting. I've read the articles and found them very
helpful. I think I'd go for the single store attempt (for maintenance
reasons), that is, storing all values in one centralized table, e.g.

Table [Priorities]
- ID (holds the Priority ID, auto)
- ResourceStringID (relating to the ResourceStringIDs Table)

Table [ResourceStringIDs]
ID (id of the resource)
Locale (e.g. en-US)
Value ("High")

In this case I could store all string-resources in a single table. You
mentioned limited rownumbers in sql 2000... well, I have to look this
one up.

Besides that, I'm concerned about performance issues. But maybe I could
come up with a wrapper object, which would fetch all strings related to
a certain page (and certain CultureLCID of course) and cache it away. Do
you think this approach would make sense?

Anyways, your article was an excellent starting point.

Best wishes and keep up the good work!

Matthias

Matthias, take a look at this two part tutorial:
http://openmymind.net/index.aspx?documentId=3
http://openmymind.net/index.aspx?documentId=4

the 2nd one deals with database structure. However, reading the article
you'll see I'm still talking about a 2 store approach (XML and database).
You can work this both ways....instead of using XML you could use a
database for your localized information (a number of people have written me
and asked what I thought aobut it (I think it's a great idea). Or, in your
database you could simply store a sinlge "ValueKey" column which you use to
fetch the real value form the XML value via ValueKey...

Karl

 

[Erik Nyquist]

Well, What I have done is to have a site-wide property that is set
either automatically by URL or manually by the user. This property
(SiteId) together with a key is used to find the correct value in a
lookup table. To speed up the access I have the whole table in the
cache. Each value for the site-wide property could represent either
one language or one web site (ASP-scenario)
I do not use language as the only way to customize the site.

The code for finding the value is made available to the pages and
controls through inheritance and I set the localized properties in the
Page_Load-method. Not extremly efficient but this is working for me.
lblPageTitle.Text = GetString(lblPageTitle.Text);
lblPageHelp.Text = GetString(lblPageHelp.Text);

A trick that I am using is to pass the current value of the control as
key and if the lookup is unsuccessful, I simpy use that value.
This allows me localize the site step by step and I do not have to
remember cryptic key names.

I also created a trace uility that allows me the load the page and get
a list of keys for that page. This speeds up the conversion process.

One thing that I have not implemented yet, is to allow multiple
languages in one site. I also want the context to determine what value
that is displayed. My idea is to have a couple of categories each
representing one dimension of customization (language, URL, ...) that
when set on the page/control also determines what value that is shown.
The important thing is to have a flexible way of setting the key. This
makes it possible to avoid changing the database when new languages
must be supported.

/Erik Nyquist