Mapping Many-to-Many or Ternary Associations
Hello,
I'm falling into a problem that I would like to share and see what
could be a proper solution for it
I have the following tables
1) Network (Id, Name)
2) Country(Id, Name)
3) Group(Id, Name, NetworkId) -NetworkId is not null and references Id
column in Network table above-
4) NetworkCountry(NetworkId, CountryId, GroupId) -NetworkId &
CountryId forms unique key of this table. They references
corresponding Id columns in Network and Country tables. GroupId is
nullable and references Id column in Group table-
in short, each network should have at least one country listed under
it. Countries inside each network could be grouped into different
groups that's related to the network.
Below is my POCO classes
public class Network{
public virtual long Id{get;set;} public virtual string Name{get;set;} public virtual ICollection Countries {get;set;} public virtual ICollection Groups {get;set;} }
public class Country{
public virtual long Id{get;set;} public virtual string Name{get;set;} //I'm not interested in having navigation properties to groups or networks from country
}
public class Group {
public virtual long Id{get;set} public virtual string Name{get;set;} public virtual Network Network{get;set;} public virtual ICollection Countries {get;set;} }
Now to mapping, Successfully I made mapping for both Network and
Country. Having many-to-many relationship as Set specified:
HasManyToMany(a => a.Countries)
.Table("NetworkCountry")
.ParentKeyColumn("NetworkId")
.ChildKeyColumn("CountryId").Cascade.SaveUpdate()
.LazyLoad().AsSet();
I was successfuly able to create new and update existing networks.
Adding and removing countries with no worries.
Now when I come to groups, I was able to create and persist new
groups. But when trying to add countries to specific group I got an
exception that cannot insert null value in NetworkId in NetworkCountry
column!!
The mapping I made between Group and Country in Group class mapping:
HasManyToMany(a => a.Countries)
.Table("NetworkCountry")
.ParentKeyColumn("GroupId")
.ChildKeyColumn("CountryId")
.Cascade.SaveUpdate()
.LazyLoad().AsSet();
I need to know how to specify mapping between Group and Country?. And
what could be the best mapping option?
I think this is the database design is fine! but if you think it is
not please do let me know your point.
I really appreciate your help.
Cheers
2 Posted by Muhammad Mosa on 23 May, 2010 05:37 PM
I think I placed this discussion in the wrong place! It belongs to help and guidance I guess.