Foliotek Developer Blog

Internationalization Data Storage in .NET (Part 1)

.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:

ProductIDNameDescriptionDate Added
1VistaIf 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.

[sourcecode lang="xml"]

If you own or run a small business, you’ll want Windows Vista Business.
Si usted es due?o o ejecutar un peque?o negocio, usted desea que Windows Vista Business.
Si vous ?tes propri?taire ou de lancer une petite entreprise, vous souhaitez que Windows Vista Business.
Wenn Sie selbst oder ein kleines Unternehmen, Sie wollen Windows Vista Business.


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:

ProductIDNameDescriptionDate Added
1Vista6/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.