Internationalization Data Storage in .NET (Part 1)

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

.NET has many built in structures for setting up your web application to support multiple languages. Most of which reside in the System.Globalization namespace. Within the System.Globalization namespace you get access to the CultureInfo class. This class defines how datatypes such as Dates, Currency and other culture related objects are displayed.

For example if the CultureInfo object is set to “mx-es” (spanish) the date would be displayed dd/mm/yyyy instead of mm/dd/yyyy. You can manage all the output settings by setting their respective properties in the CultureInfo class.

Now that we have a very basic idea of what the built in .NET tools provide us in terms of displaying internationalized data lets take a look at the solutions we have for storing globalized data.

So lets say we have a database table called Product with a field called “Description” we want to internationalize:

ProductID Name Description Date Added
1 Vista If you own or run a small business, you’ll want Windows Vista Business. 6/22/2009 3:57:07 PM

To store the multiple langages we could add a columns for each language to the table structure and subsequently update all related dependencies. Storing values in this manner is something we want to avoid here at LANIT.

Another option would be to build a look up table that stores translated values and replaces our description with a reference. This method would allow for efficiently searching and indexing but would increase the complexity of our database structure.

In our situation we decided to store every language into an XML document. The benefits of doing it this way is it allows us to add infinitely many translated versions of the product name without affecting are database structure. The draw back of using this method is the inability to efficiently search through the database on the internationalized column(s).

Let’s say we wanted to store English/French/Spanish/German translations of our product description we could do so by generating an XML file that looks similar to this.

<?xml version="1.0" encoding="utf-8" ?>
<value language="en-US"> If you own or run a small business, you'll want Windows Vista Business.</value>
<value language="es-MX">Si usted es dueño o ejecutar un pequeño negocio, usted desea que Windows Vista Business.</value>
<value language="fr-FR">Si vous êtes propriétaire ou de lancer une petite entreprise, vous souhaitez que Windows Vista Business.  </value>
<value language="de-DE">Wenn Sie selbst oder ein kleines Unternehmen, Sie wollen Windows Vista Business.</value>

Each value element represents a language and has an attribute called language which is set to a culture name. A list of culture names can be found here MSDN Culture Names

Updated Table Data:

ProductID Name Description Date Added
1 Vista <?xml version=”1.0″ encoding=”utf-8″ ?> 6/22/2009 3:57:07 PM

This provides us with a data storing model that will fit very easily into the current database structure and also provide us the ability to scale to as many languages as we see fit in the future.

That is it for part 1 . In part 2 I will discuss building a class/datatype that will get,set and build the above XML document for storing our translated values.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: