Correct NHibernate mapping for a specific scenario (one-to-many/one-to-one)

Question

I had a following structure:

User has many books in his history

which was translated to the following

class User { ICollection<Book> History }       // C#
User -> UserHistory (UserId, BookId) -> Book   // DB

---

Now I want to add a date to the history, creating the following class structure:

class User { ICollection<Read> History }
class Read { Book Book, Date At }

and keeping db schema almost unchanged

User -> UserHistory (UserId, BookId, At) -> Book

I want to map Read to UserHistory, the questions are:

1. What should I use as id in mapping of Read? UserHistory primary key is (UserId, BookId). Do I need an id for NH to work?
2. UserHistory -> Book seems to be a case of one-to-one. How to specify the BookId column name in UserHistory in this case? I do not see a column attribute on one-to-one (and there is a reason for me to be explicit about column name).

Answer 1

In your first scenario, the UserHistory was simply a mapping table for a many-to-many relationship and didn't have a proper object. Now, the UserHistory table/Read class is a separate entity so it will need an identifier of some sort. The easiest way is to add a primary key ReadId (or UserHistoryId) to the UserHistory table.

You don't have to add a separate key and could use a composite key on UserId and BookId -- but can a user read a book more than once? Assuming so, then you would also need to add the At column to the composite key to make it unique. This gets ugly and will lead to other issues when dealing with collections, so it isn't worth it.

The UserHistory to Book relationship is actually a many-to-one rather than a one-to-one. Many different users can read the same book (and perhaps the same user can read a book more than once). Think of many-to-one as an object reference.

Answer 2

Question 1: no, you don't need an id, you just can map it as component (or composite-element in this case, where it is in a list)

Question 2: User-history -> book is not one-to-one, many users could have read the same book over time, so it's many-to-one and should be mapped so.

Probably incomplete mapping looks as following:

<class name="User">

  <bag name="History" table="UserHistory">
    <key name="UserId">
    <composite-element class="Read">
      <property name="At" />
      <many-to-one name="Book" column="BookId" />
    </composite-element>
  </bag>

Note: Forget about one-to-one mappings. This is used very very rarely, when you have two tables which share the same primary key and are this way linked together really one-to-one. In most cases, you need many-to-one, even if in the real world it is actually one-to-one.