JPA: ManyToOne with Composite Primary Key

Had an issue with how to model some classes that were entities and therefore tables in the database.

I thought I'd write a little blog about it, to get my thoughts in order. The subject matter is not that difficult, once you see how it works.

A guild has ranks, and a person who is a member of a guild can be assigned to a certain rank. In the database that would look like follows. (Courtesy of Mysql-Workbench²)

As you can see there are a number of primary keys, and foreign keys.

• The guild is identified by its name.
• The guildrank is identified by its ranknumber as well as the guild to which it belongs. Therefore the primary key is a composite of both ranknumber and guild.
• The guildrank therefore also has a foreign key constraint to the Guild.
• The user is identified by its name.
• The user can be a member of a guild, though not mandatory. So there's a foreign key reference to the guild.
• The user can have a rank within the guild, though not mandatory. So there's a foreign key reference to the guildrank, by means of the two fields guild and rank.

Note: One of the things that are not currently modelled in this database schema is that it should be impossible to have a guildrank, without being in a guild. The combination rank with an empty guild should be impossible.

Aggregation and Composition

We see here an example of both Aggregation and Composition. User and Guild is an example of an aggregation, they are things of themselves but have a relation. Guild and Guildrank is an example of a composition. The guildrank cannot exist without the guild, and they form a parent-child relationship.

The guild

First the easy one, the Guild. This is the only entity that is standalone, i.e. not dependent on any other entity. This makes it quite easy. It just contains collections of ranks (One guild has potentially many ranks, so OneToMany) and members (One guild has potentially many members, so OneToMany).

These collections are not required, but are convenient.

A Guildrank

As a guildrank has a composite primary key, we require a separate object to store the primary key composite. This is the case for most (all?) ORMs as the 'findByIdentifier' method takes an object.

Once again, seeing as a guildrank is used in exactly one guild, it is the reverse of the relation in the Guild, so ManyToOne.

Many users are able to have the same rank in the guild, and therefore this is a OneToMany relation.

GuildrankPK

User

The User can be in a guild and can have a guildrank.

One user can have at most one guild, so it is the reverse of the relation in the guild, therefore ManyToOne.

One user can have at most one guildrank, so it is the reverse of the relation in the guildrank, therefore ManyToOne.

It seems the hard part is taken care of by the fact that there is a annotation @JoinColumns that is able to take care of table relations to more than one field at the same time.



Note: You might end up with a warning/error like the following:

Exception [EclipseLink-48] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DescriptorException
Exception Description: Multiple writable mappings exist for the field [User.guild]. Only one may be defined as writable, all others must be specified read-only.
Mapping: org.eclipse.persistence.mappings.ManyToOneMapping[guildrank]
Descriptor: RelationalDescriptor(User --> [DatabaseTable(User)])

In order to prevent this, either the guild or the guild-part of the guildrank will have to be read-only. This is done for the guildrank part, by specifying insertable = false, updatable = false in the JoinColumn.

Note

In general I dislike the use of composite primary keys, and I favour the use of identification by meaningless numbers. That way the name of the guild, for example, is not spread out all over the database in essence duplicating information and making it almost impossible to change. This is also called Database Normalization³.

References

[1] Wikibooks - ManyToOne

http://en.wikibooks.org/wiki/Java_Persistence/ManyToOne

[2] MySQL Workbench

http://www.mysql.com/products/workbench/

[3] Wikipedia - Database normalization

http://en.wikipedia.org/wiki/Database_normalization