Some applications need multilingual application data. Not just the user interface, but the actual data. There are different ways of handling this. Some people provide extra columns in their tables for translated values. Obviously, that's a bad solution. Other people create an extra translations table for each entity that has translatable columns. Better, but all those extra translation tables really clutter the data model. I prefer to store all translations in one table. This can be cumbersome to implement, but the following approach with NHibernate actually makes it pretty easy to work with.
First, create the following tables and sequences (I'm using Oracle btw...):
CREATE TABLE languages ( languageid NUMBER NOT NULL PRIMARY KEY, "name" VARCHAR2(50) NOT NULL, isoname VARCHAR2(2) NOT NULL UNIQUE, isdefault NUMBER(1,0) NOT NULL, version NUMBER NOT NULL ); CREATE TABLE translations ( translationid NUMBER NOT NULL PRIMARY KEY ); CREATE TABLE translationvalues ( translationvalueid NUMBER NOT NULL, translationid NUMBER NOT NULL, languageid NUMBER NOT NULL, "value" VARCHAR2(4000) NULL, version NUMBER NOT NULL ); ALTER TABLE translationvalues ADD CONSTRAINT translationvalues_uc_1 UNIQUE (translationid, languageid); ALTER TABLE translationvalues ADD CONSTRAINT translationvalues_fk_1 FOREIGN KEY (languageid) REFERENCES languages (languageid); ALTER TABLE translationvalues ADD CONSTRAINT translationvalues_fk_2 FOREIGN KEY (translationid) REFERENCES translations (translationid); CREATE SEQUENCE sq_languages; CREATE SEQUENCE sq_translations; CREATE SEQUENCE sq_translationvalues;
The classes look like this:
and these are the mappings:
<class name="Language" table="languages">
<id name="Id" column="LanguageId" type="Int32">
<generator class="sequence">
<param name="sequence">sq_languages</param>
</generator>
</id>
<version column="version" name="Version"
unsaved-value="negative" generated="never"/>
<property name="Name" column="Name" type="String"/>
<property name="IsoName" column="IsoName" type="String"/>
<property name="IsDefault" column="IsDefault" type="Boolean"/>
</class>
<class name="Translation" table="translations">
<id name="Id" column="translationid" type="Int32">
<generator class="sequence">
<param name="sequence">sq_translations</param>
</generator>
</id>
<set name="TranslationValues" inverse="true" lazy="false"
fetch="join" cascade="all-delete-orphan">
<key column="TranslationId"/>
<one-to-many class="TranslationValue"/>
</set>
</class>
<class name="TranslationValue" table="translationvalues">
<id name="Id" column="TranslationValueId" type="Int32">
<generator class="sequence">
<param name="sequence">sq_translationvalues</param>
</generator>
</id>
<version column="version" name="Version"
unsaved-value="negative" generated="never"/>
<many-to-one name="Translation" class="Translation"
column="TranslationId" not-null="true"/>
<many-to-one name="Language" class="Language"
column="LanguageId" not-null="true"/>
<property name="Value" column="Value" type="String"/>
</class>
Alright, now it's time to actually use this stuff... Suppose you have a Product entity and the Name of the Product has to be a translatable field. The Name property of the Product class would be mapped like this:
<many-to-one name="Name" class="Translation" column="NameTranslationId"/>
Creating a product with its translations is now as simple as this:
IList languages = session.CreateCriteria(typeof(Language)).List();
Product product = new Product();
product.Name = new Translation();
foreach (Language language in languages)
{
product.Name.AddTranslationValue(language, "Name in " + language.Name);
}
session.Save(product);
session.Save(product.Name);
session.Flush();
The Translation class has an indexer property which provides easy access to specific translation values so you could easily modify the translations like this:
product.Name["en"] = "English Name"; product.Name["nl"] = "Dutch Name";
This approach makes it really easy to work with multilingual data and NHibernate takes care of all the messy details for us 
