Map column to different table based on column value

Question

I have the following database structure (which can't be changed):

Users
- UserID (int)
- UserType (varchar)
- UserDetailsID (int)

Individual
- IndividualID (int)
...

Organisation
- OrganisationID (int)
...

The UserDetailsID column in the Users table refers to either an Individual or an Organisation, based on the value stored in the UserType column ("individual", "organisation"). Definitely not the best database design, but this is what we have to work with for now.

This is the start of our mapping class:

public class UserMap : ClassMap<User>
{
    public UserMap()
    {
        Table("Users");
        LazyLoad();
        Id(x => x.UserID).GeneratedBy.Identity().Column("UserID");

    }
}

What I'm trying to do is to map the UserDetailsID in the User entity to the correct table/entity based on the value in the UserType column. So it should map to Individual if UserType == "individual", and to Organisation if UserType == "organisation". Is it possible to do this with Fluent NHibernate? And how would that be done?

Answer 1

I came up with one solution myself:

My entity model looks like this:

public class User
{
    public User() { }

    public virtual int UserID { get; set; }
    public virtual string UserType { get; set; }

    public virtual bool IsIndividual
    {
        get { return UserType == "Individual"; }
    }

    public virtual bool IsOrganisation
    {
        get { return UserType == "Organisation"; }
    }

    private Individual _individual;
    public virtual Individual Individual 
    {
        get { return (IsIndividual ? _individual : null); }
        set { _individual = value; } 
    }

    private Organisation _organisation;
    public virtual Organisation Organisation
    {
        get { return (IsOrganisation ? _organisation : null); }
        set { _organisation = value; }
    }
}

And then I mapped the UserDetailsID column twice, but using lazy load:

Map(x => x.UserType).Column("UserType");
References(x => x.Individual).Column("UserDetailsID").LazyLoad();
References(x => x.Organisation).Column("UserDetailsID").LazyLoad();

And in the entity it will then call the appropriate reference to Organisation or Individual after checking the UserType first.

This seems to work fine for me but if anyone has a better solution, please do add your answer.