Many-to-many relationships with data attached in NHibernate

I'm such a nice brother. For Christmas this year, I got my older brother an MVC application to help him with some of his community work for his land surveying responsibilities. In return, I've got a nice little app that's got at least two more posts in it and probably just as many Dimecasts.

I'll describe the domain that led to this post so's I don't have to change any of the sample code I'll inevitably paste in here.

We have land surveyors. Each one, over the course of his or her career, will use "monuments". Which as far as I can tell are those big sticks with orange paint on the end you often see stuck into the ground. Why are they called something as grandiose as monuments? My brother likes to say it's the second oldest profession in the world (and given what some of them charge, they aren't far removed from the first). So in ye olde dayes, instead of using sticks, they'd erect monuments. See, the Pyramids and the Eiffel Tower aren't actually the grand constructions you think they are. They're just the result of diligent land surveyors trying to find the property line.

Anyway, I'm getting off track (though to my credit, I took longer than usual to do so this time). A surveyor will use monuments/sticks and they each one has an official label. These days, the label appears to be related to the company you work for. So all surveyors currently working for Tango & Cash Geomatics will use monuments labelled TC. If a surveyor jumps ship over to Rowan & Martin Land Surveyors, he'll then use a monument labelled RM. And so on and so forth.

Here are the tables involved:

A pretty standard many-to-many relationship. But where this differs from almost all of the NHibernate examples you see is that there is data attached to the relationship. I.e. it's not enough that we know Sandy McLean used monument TH, we want to know the period in which he and/or she used it.

Now, I'm using Fluent NHibernate here because, well, it's my app. I don't to answer to you people. And the question arose, how do I map this to my domain?

I've skipped an obvious precursor question though. Namely: what does the domain look like. This may not be crazy obvious at first glance. Of course, there is a Surveyor object and a Monument object (sparse as it is at the moment). And you might think that a surveyor has a collection of Monuments but that's not the case.

In fact, as was pointed out to me by a few people (acknowledgements are at the end), the relationship 'twixt Surveyors and Monuments is a domain concept in its own right. That is, we need another object to represent that relationship. A MonumentAssignment, for example, which contains the properties you might expect: Monument, Surveyor, YearStarted, and YearEnded.

Mapping the MonumentAssignment with Fluent NHibernate was relatively easy ("relatively" being the key word; this *is* NHibernate we're dealing with):

public class MonumentAssignmentMap : ClassMap
{
    public MonumentAssignmentMap( )
    {
        WithTable( "SurveyorMonument" );
        Id( x => x.ID, "SurveyorMonumentID" );
        References( x => x.Surveyor, "SurveyorID" )
            .WithForeignKey( "SurveyorID" )
            .FetchType.Join( );
        References( x => x.Monument, "MonumentID" )
            .WithForeignKey( "MonumentID" )
            .FetchType.Join( );
        Map( x => x.YearStarted );
        Map( x => x.YearEnded );
    }
}

The References calls required some Googling. They basically allow you to map a class to multiple tables.

Now to the Surveyor map. At first, I thought this required the use of HasManyToMany because, y'know, this is a many-to-many relationship. That's not quite true. Yes, in one sense, a Surveyor has many Monuments and a Monument can have many Surveyors but in fact, a Surveyor isn't directly related to Monument. It is related to MonumentAssignments. In a many-to-many relationship, this intermediate object is usually left out because it doesn't have any meaning other than to relate two objects. In our case, it has a temporal meaning outside of just the relationship itself.

So now, our Surveyor has a many-to-one relationship with MonumentAssignment. Monument, in turn, also has a many-to-one relationship with MonumentAssignment. With that in mind, we can map our Surveyor class thusly:

public class SurveyorMap : ClassMap
{
    public SurveyorMap() {
        WithTable("Surveyor");
        Id(x => x.ID, "SurveyorID");
        
        Map(x => x.CommissionNumber);
        Map(x => x.Surname);
        Map(x => x.GivenNames);
        Map(x => x.YearCommissionGranted);

        HasMany( x => x.Monuments )
            .Cascade.AllDeleteOrphan( )
            .WithKeyColumn( "SurveyorID" );
    }
}

That's it. In the end, it wasn't nearly as complicated as it first seemed. Of course, two key ideas led to this (again, relative) simplicity:

• MonumentAssignment is a domain concept in its own right
• Surveyor has a many-to-one relationship with MonumentAssignment, *not* a many-to-many relationship with Monument

This post was brought to you by Colin Jack, Chad Myers, Shane Courtrille, James Gregory and the number 12.

Merry Christmas!

Kyle the Monumental