My previous post on Redefining the Domain was brought about by NHibernate. I’m sure that if you’re familiar with NHibernate, you recognized the way in which I built up the domain classes (nicely making everything virtual, etc.). The post really started at 3 am the previous day with a WrongTypeException. And thus, the research began.
But now, time for some more cowbell NHibernate. Let’s start with how the database gets put together. I’m going to be using Fluent NHibernate, and I want to use Automappings so I can avoid both XML and code binding. However, that means that I’ll need to follow the Automappings conventions. By default, Automapping will use a table-per-subclass heirarchy for defining entity inheritance, so that’s what I’ll be using, too.
This post really isn’t meant to define all of NHibernate’s inheritance features, but I will briefly describe them. NHibernate has three methods for inheritance: discriminator, table-per-class, and table-per-subclass. I’ll use our model from the previous post to describe the tables.
public abstract class DomainEntity<TId> {
public virtual TId Id { get; set; }
}
public abstract class Role : DomainEntity<int> {
public virtual User User { get; set; }
public virtual DateTime CreatedAt { get; set; }
}
public class Client : Role {
public Client() { } /* Parameterless constructor required by NHibernate */
public Client(string clientName) {
ClientName = clientName;
CreatedAt = DateTime.Now;
}
public virtual string ClientName { get; set; }
}
public class Customer : Role {
public virtual string CustomerName { get; set; }
public virtual DateTime? LastBillingDate { get; set; }
public virtual decimal BillingAmount { get; set; }
public virtual int DaysInBillingCycle { get; set; }
}
In discriminator, you build one big table containing all of the properties for all of the classes. In our example, we would build one table with columns Id, User_id (FK to User table), CreatedAt, ClientName, CustomerName, LastBillingDate, BillingAmount, and DaysInBillingCycle. We would also add a column for the descriminator. Depending on the value of the descriminator, that would tell NHibernate the type of object to return when fetching the row.
Personally, I like the discriminator method, but I rarely (read “never”) get to use it. It works really well when you have a small number of total properties; however, it can quickly make your tables quite large. For example, I recently worked on an asset tracking application. There were 15 different types of assets. All assets could be assigned to an employee, and all assets had an asset tracking number. Many assets had a serial number; many did not. Some assets had as many as 30 properties just relating to that particular asset. That’s a lot of null columns. I don’t mind the nulls. NHibernate ignores them, so they don’t exist as far the code object is concerned, and I can write views on the database to help filter out just the columns that matter for a particular asset type. Those views can be used when investigating the database directly or when writing reports. In short, who cares about nulls?
If you follow many NHibernate threads or forums, be prepared to find the following question used mockingly and rather frequently when asking about the discriminator method.
Are null columns expensive in your country?
The table-per-class is the one I like the least. I don’t like it because of the replication involved. This creates a new table for each concrete child class in the system. In our asset tracking example, suppose the format of the asset tag changes from a 6-digit number to a 10-character alphanumeric value. Now, I must go make that change in two places because the duplicated work is stored in two places. Honestly, this is rarely an issue for me, because I will frequently just make everything in the database VARCHAR(255) unless there’s a really good reason not to do so (like a known DateTime or decimal value). But I work with some data purists, so there’s a big difference between person projects, and projects where I must reach a consensus with others.
That which makes me lazy, also makes me very, very fast.
Finally, we have the table-per-subclass inheritance method. In this version, we will have a table for the superclass and for each subclass. The properties of the superclass will go in the superclass table and the properties of each subclass will go in the appropriate subclass table. In my opinion, this is the most intuitive, but it takes a little trick on the database to get it to work.
CREATE TABLE [dbo].[User] ( [Id] [int] IDENTITY(1,1) NOT NULL, [Username] [varchar](255) NULL, [Password] [varchar](255) NULL, [LastLoggedOnAt] [datetime] NULL, [Active] [bit] NULL ) CREATE TABLE [dbo].[Role] ( [Id] [int] IDENTITY(1,1) NOT NULL, [User_id] [int] NULL, [CreatedAt] [datetime] NULL ) CREATE TABLE [dbo].[Customer]( [Role_id] [int] NOT NULL, [CustomerName] [varchar](255) NULL, [LastBillingDate] [datetime] NULL, [BillingAmount] [decimal](6, 2) NULL, [DaysInBillingCycle] [int] NULL ) CREATE TABLE [dbo].[Client]( [Role_id] [int] NOT NULL, [ClientName] [varchar](255) NULL )
Our Role object is abstract, but it still gets a table to store the information that is shared by all subclasses of Role. Both Customer and Client get their own tables as well, with only the properties that relate to them. In the Role table, the Id column is an identity, and should be a primary key. In each of the Customer and Client tables, Role_id is both the primary key for the table and the foreign key back to the Role table. Since it must FK back to the Role table, you should not make Role_id an identity column.
The good news is that NHibernate is really smart about retrieving data, and returns your subclasses to you. Notice that when I call .ToString() on the user’s roles, you see subclasses, instead of Role. This is exactly what we want.
