Can anyone point me to an example where a many-to-many relationship is mapped using two many-to-one relationships using an association entity (instead of a single many-to-many) with a composite key and an extra properties? I need to to be bidirectional. The DB looks like this:

_______
PARENT |
Id |
Name |
etc. |
_______'

______________
PARENT_CHILD |
ParentId (PK) |
ChildId (PK) |
Relationship |
-------------------'
_______
CHILD |
Id |
Name |
etc. |
---------'

We do this all over the place. A classic example is associating "person" with "company" -- it's many-to-many, and you need to keep a description of the relationship in the association table.

The only thing we do differently is is that the association table also has a single-column surrogate primary key. Its references to the "parent" and "child" tables are foreign keys only.

If possible I would change your association table to work that way, it will work much better with NHibernate. :)

So how do you enforce at the db level that the same person is not related to the same company multiple times? Do you manually apply a unique index to the columns?