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.

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
        {          // 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
        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.

Related Post

One Response to “Internationalization – The Database”

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