Hi,

I've been wrestling with a modelling issue for a couple of days and could really use some insight / tips from others. Here's the situation:

I've got three entities in an online game: Club, Player and Round. A Player can play for a Club in one or more Rounds. The tables look like this:

TABLE player:
- id INTEGER(10) unsigned PRIMARY KEY
- name VARCHAR(25)
- etc.. (no foreign keys)

TABLE club
- id INTEGER(10) unsigned PRIMARY KEY
- name VARCHAR(25)
- etc.. (no foreign keys)

TABLE round
- id INTEGER(10) unsigned PRIMARY KEY
- number INTEGER(10) unsigned
- etc.. (no foreign keys)

TABLE clubmembership
- clubid (foreign key to the club table)
- playerid (foreign key to the player table)
- roundid (foreign key to the club table)
- (clubid, playerid, roundid) PRIMARY KEY
- some other attributes (no more foreign keys)

Now, what I'd like to do is use code like this:

List players = club.getPlayers(round3);

Therefore I figure I need to set up a map in the club mapping, something like this:

<map name="players" table="clubmembership">
<key column="clubid"/>
<index-many-to-many class="Round" column="roundid"/>
???
</map>

But I can't figure out how to get a List (or Set) of players for each round. I've read in the documentation that it's actually not possible to set up a map that returns a collection of values for a key.

Is there another approach to achieve the desired results, without changing the database tables (legacy data, changes to the datamodel should be avoided if possible)?

Any tips / insights would be greatly appreciated.

(using hibernate2)


Thanks, Michael