I have this abstract Answer class that has subclasses like AnswerDate,AnswerNumber and AnswerOption depending on the type of the answers.

The Answer class has an id (QuestionnaireId, QuestionId)
All tables except for AnswerOption can be uniquely identified by this id.

AnswerOption needs a primary key of (QuestionnaireId, QuestionId, OptionId) as there could multiple options selected as an Answer. I am having difficulty creating the hibernate mapping for this.
the following mapping that we have doesn't work.


<hibernate-mapping>
<class name="model.Answer" abstract="true">
<composite-id name="id" class="model.QuestionAnswerId">
<key-property name="questionId" type="long">
<column name="QUESTION_ID" precision="6" scale="0" />
</key-property>
<key-property name="questionnaireId" type="long">
<column name="QUESTIONNAIRE_ID" precision="6" scale="0" />
</key-property>
</composite-id>

<property name="lastModifiedByUserId" type="string">
<column name="LAST_MODIFIED_BY_USER_ID" length="10" />
</property>
<property name="lastModifiedTimestamp" type="timestamp">
<column name="LAST_MODIFIED_TIMESTAMP" length="23" />
</property>
..
..
..
<union-subclass name="model.AnswerText" table="QS_ANSWER_TEXT">
<property name="value" type="java.lang.String" >
<column name="ANSWER_TEXT"/>
</property>
</union-subclass>
<union-subclass name="model.AnswerOption" table="QS_ANSWER_OPTION">
<many-to-one name="option" class="model.Option" fetch="select" insert="false" update="false">
<column name="OPTION_ID" />
</many-to-one>
</union-subclass>
</class>
</hibernate-mapping>

I doubt if you can have a inheritance mapping if the super class and sub class are having the same identifier. So I think you will have to have a restructure of your associations. The relation should be 1 question can have only one answer ( and vice versa) but in case of AnswerOption, 1 answer can have many right options and 1 option can be related to answers of different questions (ie many answers).

ie. All the Answer tables will have same primary key (QuestionnaireId, QuestionId). But an AnswerOption have a many-to-many relation to Option.

Thanks Litty!! that is indeed the right way to go about this, considering the design aspect.. I did a quick prototype and it worked well...