You are browsing CUBA Platform website section. This is the previous generation of Jmix.
Choose any item in the top menu to return to Jmix.

Data Modelling: Many-to-Many Association

The many-to-many association implies that multiple records of one table are related to multiple records of another table. The joining table will store the primary keys of both related entities. Optionally, this table may contain additional columns.

Depending on whether additional fields in the joining table are needed, it can be implemented via a many to many association with an additional entity or without it. The following examples illustrate both approaches.

What we are going to build

This guide enhances the CUBA petclinic example to show different use cases of many to many associations. In particular, the following use cases will be covered:

  • Vet ←→ Specialty is modeled as a direct Many-to-Many association and the UI is updated accordingly

  • Pet ←→ InsuranceCompany is modeled as an indirect Many-to-Many association with an explicit entity to store the validity range

Final Application

Requirements

Your development environment requires to contain the following:

Download and unzip the source repository for this guide, or clone it using git:

git clone https://github.com/cuba-guides/cuba-petclinic-data-model-many-to-many.git

Example: CUBA petclinic

The project that is the basis for this example is CUBA Petclinic. It is based on the commonly known Spring Petclinic. The CUBA Petclinic application deals with the domain of a Pet clinic and the associated business workflows to manage a pet clinic.

The underlying domain model for the application looks like this:

Domain model

The main entities are Pet and Visit. A Pet is visiting the petclinic and during this Visit a Vet is taking care of it. A Pet belongs to an Owner, which can hold multiple pets. The visit describes the act of a pet visiting the clinic with the help of its owner.

Direct Many-to-Many Association

An example of direct many-to-many relationships is the following in the petclinic example:

direct many to many

A Vet can have multiple specialties and a Speciality on the other hand can also be linked to multiple vets.

On the database, it is represented as a Join Table, but there is no explicit entity defined as the joining entity.

In the Studio entity designer, set the following settings for the specialties attribute: Attribute type - ASSOCIATION, Cardinality - MANY_TO_MANY. Vet will be marked as the owning side of the relationship, and Studio will suggest creating the corresponding vets attribute in the Specialty entity as the inverse side of the association.

Vet.java - the Vet entity contains a many-to-many list of specialties.

Vet.java
@JoinTable(name = "PETCLINIC_VET_SPECIALTY_LINK",
        joinColumns = @JoinColumn(name = "VET_ID"),
        inverseJoinColumns = @JoinColumn(name = "SPECIALTY_ID"))
@ManyToMany(mappedBy = "")
protected Set<Specialty> specialties;

Specialty.java - the Specialty entity now contains the many-to-many list of vets: Attribute type - ASSOCIATION, Cardinality - MANY_TO_MANY.

Specialty.java
@JoinTable(name = "PETCLINIC_VET_SPECIALTY_LINK",
        joinColumns = @JoinColumn(name = "SPECIALTY_ID"),
        inverseJoinColumns = @JoinColumn(name = "VET_ID"))
@ManyToMany
protected List<Vet> vets;

Specialty will be also marked by default as the owning side of the relationship, which enables modification of the collections on both sides.

  • views.xml - the vet-with-specialties view of the vet editing screen contains the specialties association attribute with the _minimal view. The specialty-with-vets view includes the vets association as well.

  • vet-edit.xml - the XML descriptor of the vet editor defines a datasource for the Vet instance and a nested one for its specialties. It also contains a table displaying specialties and add and remove actions.

  • specialty-edit.xml - the XML descriptor of the specialty editor defines a datasource for the Specialty instance and a nested one for its vets. It also contains a table displaying vets and add and remove actions .

So, the Vet and the Specialty editors are absolutely symmetrical.

Indirect Many-to-Many Association with Joining Entity

The many-to-many association is always implemented using a joining table, but creating an entity to reflect this table is optional. The joining entity can be created when some additional attributes should be stored in the joining table.

An example of an indirect many-to-many association is the following in the petclinic example:

indirect many to many

A Pet might over the years be insured by different InsuranceCompanies and an InsuranceCompany on the other hand has multiple memberships.

On the database, it is represented as a Join Table and there is an explicit entity defined as the joining entity called InsuranceMembership. It also holds additional information about the membership: the validity range in which the Pet is insured by the insurance company. Therefore it has values validFrom and validUntil.

In the Studio entity designer, set the following settings for the insuranceMemberships attribute: Attribute type - COMPOSITION, Cardinality - ONE_TO_MANY.

Pet.java - the Pet entity contains a one-to-many composition of InsuranceMembership instances.

Pet.java
@Composition
@OnDelete(DeletePolicy.CASCADE)
@OneToMany(mappedBy = "pet")
protected List<InsuranceMembership> insurancesMemberships;
In the Studio entity designer, set the following settings for the memberships attribute: Attribute type - COMPOSITION, Cardinality - ONE_TO_MANY.

InsuranceCompany.java - the InsuranceCompany entity contains a one-to-many composition of InsuranceMembership instances.

InsuranceCompany.java
@Composition
@OnDelete(DeletePolicy.CASCADE)
@OneToMany(mappedBy = "insuranceCompany")
protected List<InsuranceMembership> memberships;

InsuranceMembership.java - thus, the InsuranceMembership entity contains two many-to-one references: pet and insuranceCompany.

InsuranceMembership.java
@NotNull
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "PET_ID")
protected Pet pet;

@NotNull
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "INSURANCE_COMPANY_ID")
protected InsuranceCompany insuranceCompany;

@Temporal(TemporalType.DATE)
@NotNull
@Column(name = "VALID_FROM", nullable = false)
protected Date validFrom;

@Temporal(TemporalType.DATE)
@Column(name = "VALID_UNTIL")
protected Date validUntil;

  • views.xml - the pet-with-owner-and-type-and-memberships view of the pet editing screen contains the composition of insuranceMemberships (referencing the InsuranceMembership joining entity) with insuranceCompany and the additional validity information: validFrom and validUntil.

    The insuranceCompany-with-memberships view follows the same logic: it includes the composition of memberships (referencing the InsuranceMembership joining entity) with pet and the additional validity information: validFrom and validUntil.

  • pet-edit.xml - the XML descriptor of the pet editor defines a data container for the Pet instance and a nested one for its insuranceCompany instances (through the InsuranceMembership relationship). It also contains a table displaying memberships and the custom action to pick an InsuranceCompany directly, bypassing the InsuranceMembership editor.

As a result, editing of an InsuranceMembership instance works as follows:

The Pet edit screen shows a list of insurance companies and the validity range of the membership.

A user can click Add Insurance, the InsuranceCompany lookup will be opened, and the user can either select an insurance company to add or create a new insurance company. When the user selects an insurance company, a new InsuranceMembership instance is created with the default validity range. This instance is not saved to the database directly, but added to the insurancesMembershipsDc data container of the Pet editor.

When within the above described creation of a many-to-many relationship from the Pet editor an InsuranceCompany is created or updated through the InsuranceCompany editor, the created / updated instance of the insurance company is directly saved to the database as the InsuranceCompany entity is fully independent. A user can create new insurance companies and delete existing ones. All changes will be saved to the database in separate transactions.

When a user clicks OK in the pet edit screen, the updated Pet instance together with all the updated InsuranceMembership instances is submitted to the DataManager.commit() method on the middleware and saved to the database within a single transaction.

Summary

In this data modelling guide the many-to-many association was shown. Many-to-many associations are valuable if both sides of the association should be linked to multiple items of the other side as shown for the Vet ←→ Specialty case. If required by the business case, this association can be also enhanced via additional information that describes the association further as it was shown with the validFrom & validUntil attributes for the InsuranceMembership entity.