The following is giving me a headache. Given the following tables:

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

ReasonAssoc
----------
complaintId (PK)
reasonId (PK)
sequence
specificReasonId(FK)

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

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


A "complaint" can have many reasons associated with it. A reason may or may not have a "specificreason" associated with it. In addition, the reasons are indexed using the "sequence" column.

Any suggestions on how to map this?

Regards,

Joshua

The desired object model would be:

Complaint
----------
Long complaintId
String complaintValue
Set reasons

Reason
----------
Long reasonId
String reasonValue
int sequence
SpecificReason specificReason

SpecificReason
----------
Long specificReasonId
String specificReasonValue