Maintaining multiple one-to-many

问题:

Following on from NHibernate one-to-one vs 2 many-to-one

Is there an easy way to maintain multiple one-to-many relationships which are being used as a pseudo one-to-one.

E.g.

If I have 2 entities, User and Contact, which are related by a FK on each (User.ContactId and Contact.UserID).

What is the best way to maintain that each reference points at the other. It would be wrong for the system to update User with a different contact, but the Contact still references User...

回答1:

Most likely you don't need to maintain this at all if you remove one of redundant foreign keys. Your database schema should not allow anomalies like that (userX references contactX but contactX references userY). Conceptually you have one-to-one relationship between user and contact. Why not have one-to-one in NHibernate mappings? If this is because of lazy loading that is not supported for nullable one-to-one in NHibernate? There is a solution to this problem that does not involve redundant foreign keys in the database.

1) In User mapping define a bogus list. List can have only one or zero items. Zero is treated as NULL (no Contact).

<bag 
  name="_contact" 
  table="UserContacts" 
  lazy="true" 
  inverse="true" 
  cascade="all-delete-orphan" >

    <key column="UserId" />
    <one-to-many class="Contact" />
</bag>

In Contact mapping define one-to-one:

<one-to-one name="_user" class="User" constrained="true" />

In the database you need to have PK Users.Id and one (!) foreign key Contacts.UserID.

2) Another option is to simply have many-to-one in User mapping and one FK Users.ContactId

<many-to-one 
   name="_contact" 
   column="ContactId" 
   cascade="all-delete-orphan" 
   unique="true" 
   lazy="proxy"/>

Either way the maintenance that you asked about is not needed and anomalies are not possible.