Photo by james_in_to

Previously I blogged about specifying one-to-many relationships in ATG. Turns out I could have done it much more simply.

Using the same example of players belonging to a team here are the simplified SQL repository definitions.

And here is the SQL.

CREATE TABLE team (
  id VARCHAR2(40) NOT NULL,
  name VARCHAR2(3) NOT NULL,
  CONSTRAINT team_p PRIMARY KEY (id)
);

CREATE TABLE player (
  id VARCHAR2(40) NOT NULL,
  name VARCHAR2(40) NOT NULL,
  team_id VARCHAR2(40),
  CONSTRAINT player_p PRIMARY KEY (id)
);

You can set the team in the player repository item.

player.setPropertyValue("team", team);

Or add the player to the team repository item.

Set players = (Set) team.getPropertyValue("players");
players.add(player);

What gets tricky is if you require a player to be on a team.

team_id VARCHAR2(40) NOT NULL,

In this case you cannot remove a player from a team by doing this:

Set players = (Set) team.getPropertyValue("players");
players.remove(player);

This is because when you remove the player from the team the player will no longer have a team. This violates both the repository definition and will result in a SQL error because team_id cannot be null.

The only way you can change a player’s team is to do this.

player.setPropertyValue("team", newTeam);

To help enforce this you can make the team’s players property unwritable.

I hope this makes sense. If you have any questions please leave them in the comments.