This is an unusual many-to-many mapping question because it involves mapping four tables togeather instead of three. Interested? Read on...

Here is the problem:

Complaint
----------
complaintId (PK)


Reason
----------
reasonId (PK)


SpecificReason
----------
specificReasonId (PK)
reasonId (FK)



Vanilla right? Wrong. This is where it gets interesting!

ComplaintReasonAssoc
----------
complaintId (FK, Composite Key)
reasonId (FK, Composite Key)
specificReasonId (FK)
sequence


What is the logic behind this maddness? A "Complaint" can have 1:M "Reasons". These reasons are ordered by "sequence". Each "Reason" has a 0:1 relationship with "SpecificReason".

Any suggestions on how to map this? Because there is a 1:1 mapping between "ComplaintReasonAssoc" and "Reason", I thought it was an ideal candidate for a many-to-many mapping (from "Complaint" to "Reason"). The problem with this is that I am not sure how to pick up the specificReasonId and sequence number.

Your help is appreciated!

Regards,

Joshua