A while ago I sat down and talked with a colleague about the Entity Framework, he raved about how important the separation of the logical model from the physical one is. I don’t really buy into that, but that is  beside the point.

Last week, on the Progressive.NET NHibernate workshops, I setup, quite accidently, to create a single domain model and map it to several physical data models. I promised to share the code, and I think that this form is as good as any.

Let us start from the following domain model:

I am going to present three things for each physical data model manifestation. The mapping, the database schema and the result of the following query:

s.CreateQuery("from Owner owner where owner.Name = 'ayende'")
    .List<Owner>();

We will start with the classic, table per class, which looks like this:

<class name="Company">
    <id column="Id"  type="System.Int32">
        <generator class="hilo"/>
    </id>
    <property name="Name"/>
    <property name="CompanyRegistrationId"/>
    <set name="Horses" table="CompanyHorses">
        <key column="Company"/>
        <many-to-many class="Horse" column="Horse"/>
    </set>
</class>

<class name="Individual">
    <id column="Id"  type="System.Int32">
        <generator class="hilo"/>
    </id>
    <property name="Name"/>
    <property name="Email"/>
    <set name="Horses" table="HorsesBelongToIndividuals">
        <key column="Company"/>
        <many-to-many class="Horse" column="Horse"/>
    </set>
</class>

<class name="Consortium">
    <id column="Id"  type="System.Int32">
        <generator class="hilo"/>
    </id>
    <property name="Name"/>
    <set name="Horses" table="HorsesBelongToIndividuals">
        <key column="Company"/>
        <many-to-many class="Horse" column="Horse"/>
    </set>
    <set name="Owners" table="ConsortiumOwners">
        <key column="Consortium"/>
        <many-to-any id-type="System.Int32" meta-type="System.String">
            <meta-value class="Individual" value="Individual"/>
            <meta-value class="Company" value="Company"/>
            <meta-value class="Consortium" value="Consortium"/>
            <column name="OwnerType"/>
            <column name="OwnerId"/>
        </many-to-any>
    </set>
</class>

The database schema is:

You might want to pay some attention to the association between Consortium and its owner, using <many-to-any/>.

Trying to execute the aforementioned query will give us an error, NHibernate is not aware of any persistent class called Owner and HQL queries are not polymorphic over unknown types. Criteria API, however, are. And we can execute the following query successfully:

s.CreateCriteria<Owner>()
    .Add(Restrictions.Eq("Name", "ayende"))
    .List<Owner>();

Which result in:

Let us look at another scenario, which would keep the same data model, but let NHibernate know about the inheritance association between the classes. NHibernate call this type of association union subclasses. Here are the mapping:

<class name="Owner" abstract="true">
    <id column="Id"  type="System.Int32">
        <generator class="hilo"/>
    </id>
    <set name="Horses" table="OwnerHorses">
        <key column="Owner"/>
        <many-to-many class="Horse" column="Horse"/>
    </set>
    <property name="Name"/>
    <union-subclass name="Company">
        <property name="CompanyRegistrationId"/>
    </union-subclass>

    <union-subclass  name="Individual">
        <property name="Email"/>
    </union-subclass>

    <union-subclass name="Consortium">
        <set name="Owners" table="ConsortiumOwners">
            <key column="Consortium"/>
            <many-to-many class="Owner" column="Owner"/>
        </set>
    </union-subclass>
</class>

You might notice how simple the mapping looks like. For that matter, look at how we could simplify the mapping between horses and owners.

The resulting query is… interesting:

And now let us move to the more classic inheritance schemes, first, let us look at table per hierarchy:

<class name="Owner" abstract="true">
    <id column="Id"  type="System.Int32">
        <generator class="hilo"/>
    </id>
    <discriminator column="Type" type="System.String"/>
    <set name="Horses" table="OwnerHorses">
        <key column="Owner"/>
        <many-to-many class="Horse" column="Horse"/>
    </set>
    <property name="Name"/>
    <subclass discriminator-value="Company" name="Company">
        <property name="CompanyRegistrationId"/>
    </subclass>

    <subclass discriminator-value="Individual" name="Individual">
        <property name="Email"/>
    </subclass>

    <subclass discriminator-value="Consortium" name="Consortium">
        <set name="Owners" table="ConsortiumOwners">
            <key column="Consortium"/>
            <many-to-many class="Owner" column="Owner"/>
        </set>
    </subclass>
</class>

With the following schema:

In this case, Owner.Type is the discriminator for the Owner hierarchy. And the query that is generated is:

And finally, we have table per subclass, which we use the following mapping:

<class name="Owner" abstract="true">
    <id column="Id"  type="System.Int32">
        <generator class="hilo"/>
    </id>
    <set name="Horses" table="OwnerHorses">
        <key column="Owner"/>
        <many-to-many class="Horse" column="Horse"/>
    </set>
    <property name="Name"/>
    <joined-subclass name="Company">
        <key column="Id"/>
        <property name="CompanyRegistrationId"/>
    </joined-subclass>

    <joined-subclass name="Individual">
        <key column="Id"/>
        <property name="Email"/>
    </joined-subclass>

    <joined-subclass name="Consortium">
        <key column="Id"/>
        <set name="Owners" table="ConsortiumOwners">
            <key column="Consortium"/>
            <many-to-many class="Owner" column="Owner"/>
        </set>
    </joined-subclass>
</class>

This gives us this schema:

Using this approach, we query them using:

I would like to point out that this is just a sample of the type of things that we can do with NHibernate, and that I took the easy path doing so. There are actually more complex inheritance option available (discriminator with joined classes, for example) and it is pretty easy to change things even further.

And, of course, it is entirely possible to take things in the other direction, and have a single physical data model that can map to several domain models.

Have fun…