Here is the conundrum - We are building a multilingual website that needs to be translated into n languages.

1) How do we store the data?

2) How do we map NHibernate to our chosen schema?

3) How do we minimise maintenance e.g. measure of success = adding a new supported language should just involve inserting a new row in the language table.

4) How do we "black-box" the globalization so our developers don't have to know/think about it when they use the domain objects? e.g. measure of success = developer binds a column to User.Gender.Description and the correct language is returned depending on CurrentLocale

Surely this is such a common problem, somebody must have come up with a cunning "best practice"?

Forgot to mention, the schema we're considering is thus:

CREATE TABLE [dbo].[tblColumn] (
[ID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[tblName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[colName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblGender] (
[ID] [uniqueidentifier] NOT NULL ,
[sDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblLocaleString] (
[rowID] [uniqueidentifier] NOT NULL ,
[colID] [uniqueidentifier] NOT NULL ,
[langID] [int] NOT NULL ,
[val] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

We have also considered 1 localized table per table but this results in 2x tables in our db which is not ideal.

I'm afraid there is no such thing as a localization best-practice. It all depends on the context where you need the localization.

Fortunately we've got the same scenario as you are describing (data-localization) and also searched almost the entire web to find out there is no best-practice. But your solution looks a lot like the one we ended up with :).

Around the data-model that stores localized strings, we created NHibernate-mapped classes and a Singleton TranslationService class that uses those classes and can add, remove translations and offers some easy access for retrieving strings. For example, in our ASP.NET pages we can do things like <%# TranslationService.GetText(Container.DataItem, "PropertyName") %>.



This is very hard. You'll probably have to use some AOP techniques for this to make it really transparent.

Thanks Martijn, I assume we can take a peak at the Cuyahoga code to see how you've implemented this and presumably you won't mind me sharing code snippets in this thread so this becomes a definitive "How To Globalize" thread?

Perhaps an Interceptor is the way to go...

It's not in the Cuyahoga code. It was made for a customer.

Okay, how does this idea sound?

I'm using an OnLoad Interceptor to load up any records in my LocaleString table given the thread culture (thread culture is set when the user selects their language in the UI). The interceptor then injects the translated strings back into the state object.

My main concern with this approach is performance - onload is called when each persisted object is loaded, so if you are loading a collection this will result in a select for every row...

public bool OnLoad(object entity, object id, object[] state, string[] propertyNames, NHibernate.Type.IType[] types)
{

NHibernateDaoFactory DaoFactory = new NHibernateDaoFactory();
Core.DataInterfaces.ILocaleStringDao localeStringDao = DaoFactory.GetLocaleStringDao();
LocaleString example = new LocaleString();
example.ObjectId = new Guid(id.ToString());
example.Culture = System.Threading.Thread.CurrentThread.CurrentCulture.Name;
List<LocaleString> localeStrings = localeStringDao.GetByExample(example);

foreach(LocaleString localeString in localeStrings)
{
string propertyName = localeString.PropertyName;
int index = Array.FindIndex(propertyNames, delegate(string item){return (item == propertyName);});
state[index] = localeString.Value;
}

return false;


}

Depending on the amount of objects that are going to be translated, you might also want to cache the translations (after selecting the language in the UI).
We do this in a web application that has max. 30000 objects. No hassle with interceptors etc and super performance while memory usage is still acceptable.