I have a many-to-many relationship in my application (between Assessments and QuestionGroups) that I expressed in the following manner:
<list
name="questionGroups"
table="Assessment_QuestionGroup"
lazy="true"
cascade="all" >

<key column="assessmentID"/>

<index column="ordinal"/>

<many-to-many class="com.xxx.QuestionGroup"
column="questionGroupID"
/>
</list>

When I use the schemaexport tool my associative table looks almost exactly as one would expect:

create table Assessment_QuestionGroup (
assessmentID BIGINT not null,
questionGroupID BIGINT not null,
ordinal INTEGER not null,
primary key (assessmentID, ordinal)
);

The things that stands out to me is that instead of the key being made up of a composite of "assessmentID" and "questionGroupID", the schemaexport tool pairs up the "assessmentID" with "ordinal".

I have some experience in DB design but I'm certainly no expert. That said, this seems a bit odd to me. Obviously, in terms of being unique, assessmentID/ordinal pair will certainly work, but then again, if we're following that line of thinking, so would the ordinal alone.

Does anyone know why this pairing was chosen? If it is indeed the better way to do things, I'd love to know so that I can ensure that my non-hibernate data models follow a similar pattern.

-Matt Welch

ordinal is not unique on its own! There can be multiple lists, all with the same indexes.

You're right, of course. Please forgive my temporary lapse of intelligence, however, my main question remains. Why did the schemaexport tool use assessmentID/ordinal as the primary key instead of of assessmentID/questionGroupID?

-Matt Welch

because list semantics permit an object to belong twice to the list.

I had to really think about that one for a second to understand what you were saying, but I believe I 've got it.

Thanks.

-Matt Welch

[/code]