Globalization architecture

Question

I need to store products for an e-commerce solution in a database. Each product should have descriptive information, such as name, description etc.

I need any product to be localized to x number of languages.

What I've done so far, is to make any column that should be localized and nvarchar(MAX) and then i store an XML string like this:

Super fast laptopSuper hurtig bærbar

And when I load it from the database, into my business logic objects, I parse the XML string to a Dictionary where the key is the culture/language code.

So when I want to display the name of a product, I do this:

lblName.Text = product.Name["en-us"];

Does anyone have a better solution?

Answer 1

Rob Conery's MVC Storefront webcast series has a video on this issue (he gets to the database around 5:30). He stores a list of cultures, and then has a Product table for non-localized data and a ProductCultureDetail table for localized text.

Answer 2

resource files

Answer 3

I saw the video, and it looks OK - I thought of splitting localized columns into table, and joining - but I think it is way to many joins. I'm mainly thinking about performance.

Would an indexed view, solve the performance issue?

Answer 4

You should store the current language somewhere (in a singleton, for instance) and in the product.Name property use the language setting to get the correct string. This way you only have to write the language specific code once for each field rather than thinking about languages everywhere the field is used.

For example, assuming your singleton is defined in the Localizer class that stores an enum corresponding to the current language:

public class Product
{
  private idType id;
  public string Name
  {
    get
    {
      return Localizer.Instance.GetLocalString(id, "Name");
    }
  }
}

Where GetLocalString looks something like:

  public string GetLocalString(idType objectId, string fieldName)
  {
    switch (_currentLanguage)
    {
      case Language.English:
        // db access code to retrieve your string, may need to include the table
        // the object is in (e.g. "Products" "Orders" etc.)
        db.GetValue(objectId, fieldName, "en-us");
        break;
    }
  }

Answer 5

This is basically the approach we took with Microsoft Commerce Server 2002. Yeah indexed views will help your performance.