title: Nhibernate design and composite keys
body: <p>Ok I'm in a situation where I have the following tables</p>
  
  <p>User (UserId ....)
  UserFavourite (UserId, OtherUserId, AddDate)</p>
  
  <p>If you "favourite" another user it adds a row into userFavourite. Now I have referential integrity between both UserId and OtherUserId columns and the User table with two foreign key constraints.</p>
  
  <p>My question is, what is the best practice for accessing this through NHibernate? The basic Get&lt;> method takes an id, which is the ID column. In this case the unique identifying characteristic of the table is a composite key of UserId and OtherUserId, so this leads me to think I should have a composite key. I read everywhere that they are bad though and if the DB is designed right I shouldnt be using them.</p>
  
  <p>So I thought of how else I could design the DB, and I suppose I could have</p>
  
  <p>UserFavourite (UniqueID, UserId, OtherUserId, AddDate)</p>
  
  <p>And make UniqueId the PK and ID for the Get method, and put a unique constraint on the UserId/OtherUserId columns. However this does not solve my problem, as when I want to delete the row through code or get it, I still need to pass the UniqueID, which I will not have access to. I want to be able to say</p>
  
  <p>"Remove this user (User Id 142) from your favourites". So I only have access to the UserId, and your own UserId, so somehow I need to be able to run an NHibernate query using those two ID's, not the unique ID.</p>
  
  <p>Can someone point me in the direction of how this case should be handled in NHibernate via ideal practises so that I can easily and efficiently do these queries without using composite keys?</p>