How do you do a many to many relation?

Ask for Help
1

Hello,

How do you make a many to many relation ?
anybody got a tutorial or the info i’m looking for ?

I have Trainings.
I have Participants

A Training can have many Participants and a Participants can be inside many Training.

I made a table Participant and a table Training.

I did make a Many to One (many Training inside a Participant) but I don’t find how to do many to many.

Participant :

image
image1509×219 24 KB

Training :

image
image1464×203 23.4 KB

Thank you

2

You could simply use a separated string (“,”) in your ids and use a query column instead of relation, ans then use the “Is included in” condition.

1 Like
3

In your Trainings table, the Participants column can be a comma-delimited one. You can have a choice component pointing to the Participants table, allow multiple select, and select the rowIDs from Participants, whilst showing their Names on the front end for readability.

Then, use a split text column, split the Participants column by a comma. Use that to create a multiple relation to the Participants table’ RowID to show participants in a training session.

in Participants table, create a multiple relation from the RowID column to the split text result in Participants. You can show the training sessions the user is a participant of.

2 Likes
4

I don’t know how you structured your app, but strictly speaking there is a slight difference between a User and a Participant. A user is an object (someone, an email) that has access to your app. A participant is an object (someone, an email or User ID) that has been associated with a training. So in theory, a participant is a user but a user is not necessarily a participant.

So here is another idea for the many-to-many relation:

  1. Create a Users table (already done)
  2. Create a Trainings table (already done)
  3. Create a “Trainings↔︎Users | Participants” table. This is exactly how I would name that table. “Trainings↔︎Users” describes with the table is, i.e. a joint table that joins users to trainings and trainings to users, this is the many-to-many relation. And “Participants” is the descriptive name of the table.

In the “Trainings↔︎Users | Participants” table, include the following columns:

  1. Row ID: identify a unique participant
  2. Timestamp of the last update (optional)
  3. User ID
  4. Training ID
  5. “User” group of columns: Relation to the Users table, then lookups for user columns
  6. “Training” group of columns: Relation to the Trainings table, then lookups for training columns

Each row will represent a participant, a join between a user and training. This structure does add additional rows to your app. If you are on a paid plan, this should not be an issue.

With this approach, I appreciate that the join table allows me add attributes to the relation (timestamp, any extra information) and I find the structure easy to understand and work with.