Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
When to use many-to-one(s) vs. many-to-many with NHibernate

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:

  1. 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.
  2. 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.
  3. 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.
  4. The IList<Address> collection property within the Customer class should be modified to be IList<CustomerAddress>.
  5. 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.
  6. (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


Posted 07-11-2008 12:04 PM by Billy McCafferty
Filed under:

[Advertisement]

Comments

Thomas Vochten wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-11-2008 6:20 PM

Finally someone that explained this clear to me ;-)

jack wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-12-2008 12:54 AM

thanks for the article billy. my question is bit out of the topic; i hope u will forgive me for that ;)

say i have 1 to many relation between Customer and Adreess. and address is polymorphic; i.e., Address can be HomeAddress, ShippingAddress, BillingAddress. for arguments sake lets say Customer can have multiple instances of each type of address.

since Customer has 1-to-many relation with Address, by default it would return all types of address when we say Customer.Addresses. now is it possible to execute a query from Customer which fetches only specific Addresses? please note relation is unidirectional.

sorry billy if this question is out of context.

appreciate ur contribution to the community

Cassio Tavares wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-12-2008 8:22 AM

Hi Jack,

I think you should have an enumerator (eg AddressType) representing a fields in your address table. Then you make a method in a DAO to get it by it's address type.

public Address GetAddressByType(AddressType type)

You have other ways but this seems to be one of the best for me.

Dew Drop - July 12, 2008 | Alvin Ashcraft's Morning Dew wrote Dew Drop - July 12, 2008 | Alvin Ashcraft's Morning Dew
on 07-12-2008 9:31 AM

Pingback from  Dew Drop - July 12, 2008 | Alvin Ashcraft's Morning Dew

jack wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-12-2008 9:32 AM

@Cassio,

in my case Address can be entity. all the addresses will be stored in a single table, hence table per class inheritance. but customer will be the aggregate root. (note: this eg. is just for arguments sake). so addresses will be accessed/updated/deleted through Customer.

now my question was is it possible to fetch lets say only ShippingAddresses only for the customer? i can filter it out in the server but was wondering if it can be done in a single query.

thanks

Billy McCafferty wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-13-2008 10:06 AM

@jack,

For the specific example you provided, I'd create a "protected IList<Address> Addresses" on the Customer and then add wrapper properties; e.g., "public IList<HomeAddress> HomeAddress" which would use LINQ to return HomeAddress objects from the Addresses collection.  IMO, for the example you're discussing, I'd more likely forego having polymorphism and simply add an AddressType enum to the Address object.  Polymorphism is typically only used if the inheritance structure has different behavior in each object; otherwise, a simple type enum should adaquately express the difference.

With that said, there are certainly other situation, when inheritance is very warranted, wherein you can use a couple of NHibernate tricks.  The "discriminator" and occassionally the "where" clause are useful for these situations:  www.hibernate.org/.../mapping.html (Setting the "polymorphism" property to explicit is usually needed in these cases as well.)

jack wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-13-2008 7:33 PM

@billy

thanks for taking time to respond.

using wrapper properties means filtering it in the application. right? so does that mean i wouldn't be able to say get Cutomers with their HomeAddresses only? as i said the relation is uni directional from Customers to Address.

thanks

Billy McCafferty wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-13-2008 8:15 PM

Ah, now I understand what you're asking.  If you want to query for one or more customers based on the address type, you'd have to do a join query.  here's an example:  devlicio.us/.../performing-join-queries-with-nhibernate-createalias.aspx   what you're looking for would require one less alias than the example shown.  let me know if that's not what you're looking for.

jack wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-13-2008 8:43 PM

i think what i am trying to achieve is not quite right semantically ;) using join queries i could filter out customers based on criteria in Address but when i do Customer.AddressList i would still get all the addresses of the customer. what i want is just specific type of Addresses not all types of Addresses.

i think i have to use different collection for each type of address and use where in the mapping. i guess that's the only way to go right?

Billy McCafferty wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-13-2008 8:50 PM

Yes...I think that would probably be the way to go for what you're trying to do.

jack wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-13-2008 9:10 PM

thanks for clearing out my doubt billy

Reflective Perspective - Chris Alcock » The Morning Brew #135 wrote Reflective Perspective - Chris Alcock &raquo; The Morning Brew #135
on 07-14-2008 1:59 AM

Pingback from  Reflective Perspective - Chris Alcock  &raquo; The Morning Brew #135

El Guapo wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-14-2008 9:12 AM

OK, that's a good post, but here is my dilemma.

I want to add a couple columns to the Customer_Address table which are meaningful only in the relational model and not in the domain model. Let's say these are audit fields with user and timestamp info (for example). It doesn't really matter what they are.

As far as I can determine, I am still stuck in the situation where NH is forcing me to create a 3rd entity for this association, even though I don't want or need it in my domain model.

Is this true? Is there a way to hook in to manage extra columns on the association table? It seems that interceptors do not work - they are not invoked if you don't create an entity for this table.

Thanks

Billy McCafferty wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-14-2008 9:17 AM

Outside of using a trigger or default value, I'm not sure what you can do to avoid the intermediary object.

El Guapo wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-14-2008 6:01 PM

That sucks.

Billy McCafferty wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-14-2008 6:32 PM

Fair enough.

So you want to learn NHibernate? - Part 1 of 1, The Links « HSI Developer Blog wrote So you want to learn NHibernate? - Part 1 of 1, The Links &laquo; HSI Developer Blog
on 07-31-2008 6:27 PM

Pingback from  So you want to learn NHibernate? - Part 1 of 1, The Links &laquo; HSI Developer Blog

Emanuel Pasat wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 09-01-2008 2:07 PM

Sorry if I'm offtopic, I don't know if is stupid or not, but here is my question:

Does anybody see any problem by using a decorated Address instead of using the intermediary object (IList<DecoratedWithIsCurrentAddress> instead of a IList<CustomerAddress>)?

Basically, from the relation table I would get a decorated Address instead of the intermediary object. Does it makes sense (I'm very new to NH)? Does this hides some logical flaws?

Billy McCafferty wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 09-01-2008 3:48 PM

Emanuel,

If returning a decorated object meets your development needs, then that is the appropriate approach to take.

Emanuel Pasat wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 09-02-2008 12:16 PM

Problem is solved, I was needed it as part of a  Composite design pattern implementation. Now the relation object is behaving like the child object through decorator.

Thanks

Laura wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 09-03-2008 11:53 AM

Thanks a lot for the article.

DDD & Many to Many Object Relational Mapping wrote DDD &amp; Many to Many Object Relational Mapping
on 01-24-2009 3:15 PM

Pingback from  DDD &amp; Many to Many Object Relational Mapping

Marco Mendonça wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 02-13-2009 7:23 PM

Ok, what can i do in the following case?

- Customers table with a composite primary key (assume something like "CustomerID" and "CreationDate");

- Addresses table with a single column primary key (assume "ID");

- Customers_Addresses could have a composite key (assume "CustomerID", "CreationDate" and "ID") and other columns.

So, Customers_Addresses would need, at least, a composite foreign key to Customers ("CustomerID" and "CreationDate").

Testing this kind of model with NHibernate generates this kind of error "NHibernate.FKUnmatchingColumnsException: Foreign key (FK93718F6774F142F5:Customers_Addresses [CustomerID])) must have same number of columns as the referenced primary key (Customers [CustomerID, CreationDate])".

Even if the Customers_Addresses mapping file contains this:

"<many-to-one name="IDCustomersLookup" class="Customers"  update="0"  insert="0">

<column name="CustomerID" />

<column name="CreationDate" />

</many-to-one>"

So, what's the best option to model this example?

Regards.

Iain Holder wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 03-02-2009 12:23 PM

Billy, you *are* a hero.

Dan wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 06-04-2009 8:56 PM

thanks for this post.  i was using the where clause method before i read this post and i feel better about it now :-)

Caleb Cohoon wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-11-2009 1:50 AM

Thanks a lot. This article really helped! :)

L Klein wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 11-27-2009 6:58 PM

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.

If not done so already, a data modeler needs to be identified who will allow a surrogate identity primary key to be added to the legacy database. If the identity column cnat be added; the solution remains elusive...

Joe wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 11-27-2009 9:13 PM

How do you delete a customer when you have a mapping like this?  In a similar mapping, NH seems to be trying to save the mapping entity with a null Customer reference, which my db schema doesn't allow.  I can't seem to get it to simply delete the mapping along with deleting the customer and address  entities.

Billy McCafferty wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 11-29-2009 7:04 PM

Joe, what you'll want to look at are "cascade" options.  Google for "NHibernate cascade delete" to find more details about this.

iain kay wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 02-11-2010 10:18 PM

cool, i like your article,

BUT

I get a problem when the following scenario applies:

My 'Customer' class is actually an inherited class of the class 'Person; (in which case it is mapped as a joined sub-class in the 'person' mapping file)

every time i attempt to insert into the intermediary table (Customer_AssociatedDude) table, i get error stating that 'cannot insert NULL value' for AssocuiatedDudeID. Even though i have set associtead dude as follows  Customer.AssociatedDude.AssociatedDudeID = 'Whaterver'

please help explain where i'm going wrong....

Billy McCafferty wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 02-16-2010 3:26 PM

iain, if you would, please post your question on the NHibernate forums at forum.hibernate.org/viewforum.php for more expedient support.

Thank you.

David Mc wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 03-01-2010 4:05 PM

First, great article!  It has helped my understanding of NHibernate a good bit.

Now, something I am having trouble understanding.  If I have a Customer Object with a IList<ContactAddress> and the ContactAddress has a  Customer Object on it, wouldn't this create a circular look up that would go forever?

Using WCF trying to return a Object that contains a ContactAddress object, to access the Address object on it as well as some other Properties on it (ContactAddress), is giving me fits in the form of "The underlying connection was closed: The connection was closed unexpectedly.".  If I remove the Contact object from the ContactAddress it works perfect.

Do I need the Contact Object on the ContactAddress in order to do an "Inverse" type of delete or update?

Thanks,

David Mc

Billy McCafferty wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 03-02-2010 2:25 PM

Hi David, I expect that this post should assist you in pinning down exactly what the issue is:  devlicio.us/.../frequent-cause-of-wcf-exception-quot-the-connection-was-closed-unexpectedly-quot.aspx

Nestor Rodriguez wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-08-2011 11:29 AM

Regarding your post.  When the Address becomes an entity with state it does mean that you required an identifier.  An identifier introduce more problems like concurrency, generator algorithms and so on.  One way to avoid it is to map such Address  entity like a composite-element.

Krokonoster wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-12-2012 8:05 AM

Lol.  I just posted in the S#rpLite group on how to implement data on a many to many relationship and then searched on, running across this post.

Old article, but makes perfect sense.  

Ryan Bosinger wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 07-27-2012 5:24 PM

I set-up a many-to-many relationship and as soon as I was done and happy I realized I needed to store a "SortOrder" and now I'm going to have to go back and set it up like this.  Errr!  Thanks for the info!

social bookmarking service wrote re: When to use many-to-one(s) vs. many-to-many with NHibernate
on 03-24-2013 7:30 AM

jZdTXc Great, thanks for sharing this blog post.Really thank you! Keep writing.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)