A common question when developing with NHibernate is: when is it appropriate to use a many-to-many relationship vs. two many-to-one relationships? And does an intermediary domain object need to be introduced to reflect a many-to-many relational table?
As a rule, when using NHibernate, you should rarely, if ever, have to model your domain to accommodate the constraints of relational model, and vice-versa. For example, suppose you have a Customers table and an Addresses table. Each Customer can have multiple Addresses and each Address can be associated with multiple Customers; e.g., the same address could be associated with multiple family members in the same household. Accordingly, you'd have a Customer_Addresses relational table in the DB to hold the many-to-many relationship entries; this table would simply have two foreign key columns: CustomerFk and AddressFk.
At first glance, this might imply that you'd have to create a CustomerAddress object in your domain model to reflect this relationship table in the DB; that's not the case with NHibernate. Instead, you'd simply add a many-to-many association to the Customer.hbm.xml which indicates that the CustomerAddresses table should be used as the lookup for managing this many-to-many relationship. (See the NHibernate documentation at http://www.hibernate.org/hib_docs/nhibernate/html/collections.html for implementation details of mapping a many-to-many relationship.) This many-to-many mapping assumes that the Customer class contains an IList<Address> property (and you could optionally have the inverse relationship from the Address class). Consequently, the domain model maintains a clean, domain-driven design, while the DB reflects a normalized relational model. But keep in mind that a many-to-many relationship should only be used for a true many-to-many relationship table wherein the relational table only contains foreign keys to other tables.
Now suppose, down the road, we add a bit column called IsCurrent to the Customer_Addresses table. With this added, each row in the Customer_Addresses table is now an entity object with state, masquerading as a many-to-many relationship. When this occurs, steps must be taken to "upgrade" this many-to-many relationship to a domain object with two many-to-one relationships to Customer and Address, accordingly:
- If not done so already, an identity column (or other unique identifier) needs to be added to the Customer_Addresses table; e.g., CustomerAddressId. This is now the primary key of the table and will be the ID property of our domain object.
- A CustomerAddress.cs class needs to be added to the domain model having a many-to-one relationship to Customer as "public Customer Customer {...}", a many-to-one relationship to Address as "public Address Address {...}" and, of course, the "public bool IsCurrent {...}" property.
- CustomerAddress.hbm.xml needs to be added which reflects the same property and many-to-one relationships as indicated in step 2. This HBM would map to Customer_Addresses table.
- The IList<Address> collection property within the Customer class should be modified to be IList<CustomerAddress>.
- Finally, the Customer.hbm.xml needs be modified to replace the existing many-to-many declaration with a one-to-many to the CustomerAddress object.
- (If your Address.hbm.xml contained a bi-directional reference back to IList<Customer>, you'd then have to take the same steps to replace the "other side" of the many-to-many with a one-to-many to CustomerAddress.)
With this done, the many-to-many has been removed, a domain object has been introduced, and proper associations have been established. Many-to-many(s) are handy, but as soon as they starting doing more than just being a relationship, action should be taken to promote the relational object to the domain.
Billy McCafferty