Internationalization – The Database

Over the last several weeks we’ve been examining the various aspects of internationalization using ASP.NET. But it doesn’t help to have your resources and images set up for internationalization if your database has all of its data in one language.

The following are several solutions you might consider when setting up your database.

One possible solution would be to have a separate database for each language. You might have your core database be “DatabaseName” and the multiple languages post fixed with the language and culture. For example DatabaseName-en-US.

For this to work you will need to change the connection string on the fly by using a multi-tiered architecture as we’ve discussed earlier in this post.
http://blog.dmbcllc.com/2008/02/29/datasets-objectdatasource-and-3-tier-aspnet/

As I mention in that post, having a separate adapter method is useful because we can use it to change the connection string if needed. You might consider using a base class for setting the connection string:

public abstract class Controller
{
    public Controller()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public static string ConnectionString
    {
        get
        {          // Code that computes the connection string here
        }
    }

}

Then our child classes can use this code to set the connection string in the adapter

private static DataSetTableAdapters.TableAdapter Adapter
{
    get
    {
        DataSetTableAdapters.TableAdapter adapter =             new DataSetTableAdapters.TableAdapter();
        adapter.Connection.ConnectionString =             ConnectionString;
        return adapter;
    }
}

It’s a pretty simple solution really, but it will mean we have to replicate our entire database twice, which may not be what we need.

You could do something similar but just modify the table names that are impacted. But even then, you still have an issue with making sure that IDs stay in sync.

The best solution I’ve seen so far simply adds a new ID value to each table that needs to be internationalized called “MasterID” and another field that specifies the language. The ID points back to the parent record. The rest of the structure stays the same.

ID MasterID Language other fields…
1 1 en-US english
2 1 ca-FR french
3 3 en-US spanish
4 3 ca-FR english
5 5 en-US french
6 5 ca=FR spanish

Then in our stored procedure that retrieves the row, we pass in the ID we want to retrieve along with the language code.

Using the ID, we do a lookup for the MasterID and then retrieve the row WHERE the MasterID equals the ID we found and the language matches the code we passed in to the stored procedure.

Obviously, it is not necessary to pass in the ID if you know the MasterID, but I find that most internationalization projects are not something we plan up front, but are something that becomes a requirement over time. After the core language site has been up for a while. The method of passing in the ID and looking up the MasterID handles this situation best.

Thanks to my buddy Eric for teaching me this method.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • ASP.NET MVC – Model != BLL or DALASP.NET MVC – Model != BLL or DAL Last week I introduced the ASP.NET MVC framework by talking a bit about what the model, view and controller are. In the comments, John Meyer said, I respectfully disagree with your […]
  • ASP.NET Internationalization – ThemesASP.NET Internationalization – Themes A couple of weeks ago I started a series on ASP.NET Internationalization where we started by covering the basics, that is, using RESX files to supply language-specific strings in our […]
  • Internationalization – Resource FilesInternationalization – Resource Files Today I'm going to start a series on Internationalization.  Today's post will start with the basics, but I plan to cover such issues as: Using Resources Detecting the language of […]
  • DotNetNuke – Data Access Layer AlternativeDotNetNuke – Data Access Layer Alternative Now that I've explained the standard way of creating a Data Access Layer (DAL) for DotNetNuke, we can address the alternative method of providing this same functionality.You see, the only […]
  • Silverlight – RESX Files and InternationalizationSilverlight – RESX Files and Internationalization One of the toughest things to get your head around in Silverlight is Internationalization and Localization.  But once you figure out the quirks and the parts that are missing in […]

About Dave Bush

Dave Bush is a .NET programmer and Certified ScrumMaster who is passionate about managing risk as it relates to developing software. When he is not writing or speaking about topics related to Application Lifecycle Risk Management (ALRM), he is an example to his peers as he develops web sites in the ASP.NET environment using industry best practices.

One Pingback/Trackback

  • http://codehill.com Amgad

    Thats a good solution, specially if you dont know how many languages will be added later. I developed some windows and and web apps that needed to have 2 languages. Since I knew from the start that it will only be 2 languages I only needed to have 2 fields for every varchar value. For example a personnel table will look like this:

    PersonID, NameEn, NameAr, DateOfBirth

    That way the values that do not need to be translated are not duplicated. Your solution is much more scalable, but I think you can make use of this idea if you know you know you are going to use 2 or 3 languages only.

  • Pingback: Dew Drop – June 26, 2013 (#1,573) | Alvin Ashcraft's Morning Dew

Awards & Certs