nhibernate help mapping entity with multiple foreign keys

Question

Hi, how would I map this relationship in NHibernate (I'm using the simple idea of the auction example to demonstrate the problem)

Database Tables / Entities:
User - contains the users
Item - an item for sale (like ebay)
Bid - records a user's bid on an item

I was imagining the bid table to look like this with:
Id (PK), ItemId (FK), UserId (FK), Amount, TransactionDate

with a mapping file something like:

<class name="Bid">
  <id name="Id" class="guid">
    <generator class="guid.comb" />
  </id>
  <many-to-one
    name="Item"
    column="ItemId"
    class="Item"
    not-null="true" />
  <many-to-one
    name="User"
    column="UserId"
    class="User"
    not-null="true" />
  <property name="Amount" type="Double" not-null="true" />
  <property name="TransactionDate" type="DateTime" not-null="true" />
</class>

In the code (vb.net sorry) if you added a simple convenience method it would look like this which seems no good:

Public Class Item

  '...

  Public Sub AddBid(ByVal bid as Bid, ByVal user as User)
      bid.User = user
      bid.Item = Me
      Bids.Add(bid)
  End Sub

End Class

But i'm thinking I must have this design wrong...i thought of using a composite key, but that seems more wrong...any help greatly appreciated!

Answer 1

I don't see the problem. A user may make multiple bids, and there may be multiple bids on a single item. It's pretty common in any real-world database for some tables to have more than one FK/relationship, and your mapping code looks right to me.

(P.S. I'm assuming you also have your bags [or similar] in the Item and User maps... if that's what you're having a problem with, you should mention that specifically.)

Edit: Actually there is one thing I would probably change, and that is the utility method itself. Normally you wouldn't have a reason to put something like in the Item class, instead you would use the repository pattern, which wraps an ISession and has a method signature like:

public void AddBid(User user, Item item, Decimal amount) ...

Or maybe even:

public void AddBid(Guid userID, Guid itemID, Decimal amount) ...

You get the idea - the point is that the method isn't attached to a specific entity, it's attached to a unit of work. I find it to be a better design for the DAL, but it is just one way, and there's really nothing wrong with yours.