Mapping a localized string with NHibernate

Question

Most of our lookup tables have corresponding localization tables.

Something like this for example:

create table GENDERS (
    GENDER_CODE char,
    DESCRIPTION varchar
)

-- Localization table:
create table LC_GENDERS (
    LOCALE varchar,
    GENDER_CODE char,
    DESCRIPTION varchar
)

In this example the default (en-US) values are stored in the GENDERS table. All localized strings are stored in the LC_GENDERS table in such a way that the following query will return all the gender descriptions localized to en-UK (or en-US if there is no en-UK translation):

select
    GENDERS.GENDER_CODE,
    COALESCE(LC_GENDERS.DESCRIPTION, GENDERS.DESCRIPTION)
from GENDERS left outer join LC_GENDERS
    on GENDERS.GENDER_CODE = LC_GENDERS.GENDER_CODE
    and LC_GENDERS.LOCALE = 'en-UK'

What is the simplest way to map this database schema using Fluent-NHibernate? Also, there may still be opportunity to change the design, so any alternatives will also be considered.

Answer 1

You can see an example of doing this here