I was hoping to get your thoughts on a problem I'm having with defining mappings for a table with a composite natural key. I've designed my core schema according to Wiley's "Data Model Resource Book" and it looks like this:

Party
- PK: PartyId

Person : Party
- Name
- BirthDate, etc.

Organization : Party
- Name

RoleType
- PK: RoleTypeId
- Description

PartyRole
- PK: PartyId (FK: Party)
- PK: RoleTypeId (FK: RoleType)

PartyRelationship
- PK: PartyIdFrom, RoleTypeIdFrom (FK: PartyRole)
- PK: PartyIdTo, RoleTypeIdTo (FK: PartyRole)
- PK: FromDate
- ThruDate, Status, Priority, etc.

I've mapped Person and Organization as joined subclasses of Party which is working well. I've mapped PartyRole to Party with a cascading set of RoleType objects related by the join table "PartyRole." Where I'm having trouble is with the relationship class.

Right now, I'm mapping PartyRelationship separately as:



I've also created a trigger to add PartyRole records to meet the foreign key constraints but I still have problems saving relationship objects (SQL update or delection failed - row not found). What's odd is that, when I do the same insert in Query Analyzer, it works fine. When hibernate does it via sp_executesql, it fails.

Do you see a simpler way to map this schema?

Thanks,
Jim

I'm not a big fan of multi-column PKs - so if it is all possible I would just make a PartyRole.PartyRoleId pk and a PartyRelationship.PartyRelationshipId pk. All the pk's you have defined with multi columns I would change to a unique constraint.

It looks like your mapping is okay (complicated because of the multi-column pks, but okay).

Your PartyRelationship has to override Equals and GetHashCode (http://nhibernate.sourceforge.net/nh-do ... ompositeid) and that could be problematic.

If you've got a trigger - make sure to add SET NOCOUNT ON. Triggers mess with the row count that is returned by insert/update statements.

Mike,

Thanks for your comments. After struggling with the relationship mapping for a while, I'm leaning towards your suggestion of a combination of an internal ID and a unique constraint on the current PK columns.

Your suspicion regarding my trigger was spot on. The trigger ensures that the Party/Role combinations are maintained in a separate join table (and also manages reference counts). I had set NOCOUNT to ON, but I had turned it back off for the actual INSERT (it's an INSTEAD OF trigger). Thanks for the tip on that.