Interfaces for many-to-many relationships

In designing applications that rely on many to many relationships, there is a trick that will make everything much easier:

Where possible, build views of the joint table

Let's return to the employee project matching application from the many to many section in Modelling relationships. Here are some of the things we want to be able to do with the join table:

  • Show project: list employees working on it
  • Show employee: list projects they work on
  • Add/remove employee to project
  • Add/remove project to employee

We will accomplish these things using the ListShowList view template, which allows you to tie together (optionally) a list view that selects rows from a table with single row views of that same table, followed by related views of different tables - that is views of a table that is joined by a key to the row being shown as the single row view. I will walk through this with the first example, where we see a list of projects, select one of them to get a close-up view of the one project, followed by a list of employees assigned to this project with options to add and remove employees.

The first list view of projects will simply contain the name of the project. Likewise, the single row view of the project will simply show whatever details you have about the project on a standard Show view. The "trick" is to build list views of the join table which will contain the action to remove the employee from the project (that is, delete the row in the join table) and below the list a button to add employees to the project (that is, insert a new row in the join table).

This application setup including the mirror views see a list of projects an employee is working on, is available in the "Project-employee assignment" if you want to study it in more detail.

The "badges" plug-in is useful for displaying or editing labels that originate in many to many relationships.