Multiple Many-to-Many relationships between two tables

[IvanDrag0]

I'm not sure if this is a bug or this has not been implemented. When trying to setup a second many-to-many relationship between two tables (with different join tables), I get an error that a relationship between the two tables already exists. As long as I'm using different join tables, I should be able to create more than one many-to-many relationship between two tables.

[linear[bot]]

BUDI-8746 Multiple Many-to-Many relationships between two tables

[ConorWebb96]

Hey @IvanDrag0,

Can you provide all the information from the bug template? This information is important whenever we try to replicate what's happening on your end. The bug template is attached below.

I haven't been able to replicate this on latest but I've only tried with a MySQL database.

Checklist

• [ ] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method:
  • Budibase Version:
  • App Version:
• Cloud
  • Tenant ID:

Describe the bug A clear and concise description of what the bug is.

To Reproduce Steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior A clear and concise description of what you expected to happen.

Screenshots If applicable, add screenshots to help explain your problem.

App Export If possible - please attach an export of your budibase application for debugging/reproduction purposes.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Additional context Add any other context about the problem here.

[IvanDrag0]

Hi @ConorWebb96,

Sure thing!

Hosting

• Self
  • Method: docker compose (single image)
  • Budibase Version: 2.33.2
  • App Version: 2.33.2

Describe the bug When trying to setup a second many-to-many relationship between two tables (with different join tables), I get an error that a relationship between the two tables already exists. As long as I'm using different join tables, I should be able to create more than one many-to-many relationship between two tables.

To Reproduce We'll create a database that lists tasks, where each task has a subtask that is composed other tasks. The schema should look like this:

Steps to reproduce the behavior:

1. Create a new database table called "Tasks" with the following fields: Name (Text) and Description (Text or Long Form Text).
2. Add several rows to use as data (or use the following CSV with sample data):

Tasks.csv

3. Create another table called SubTasksTasks (which will be the through/join table) with the following two fields: TaskId (Number) and SubTaskId (Number).

4. Got to the data source window and click on "Define Relationships".

5. Change both tables to Many rows to many rows, and add the TasksSubTasks table as the "through" table.

6. Select the "Tasks" table in the first dropdown.

7. When selecting the second table, Budibase should allow the user to select the "Tasks" table again. However, Budibase seem to remove the table that was selected in the first drop down from the second drop down list.

Theoretically, after selecting the "Tasks" table again, the schema should look like this:

The only issue is that a join/through table should have the two values as primary keys instead of having a separate ID key. However, Budibase does not allow the user to specify two columns as primary keys.

App Export

MTMDemo-export-1729614491668.tar.gz

Desktop (please complete the following information):

• OS: Ubuntu 22.04.5 LTS
• Browser: Firefox
• Version: 131.0.3 (64-bit)

Hope this helps!

[IvanDrag0]

@ConorWebb96 I just realized that I've submitted another issue that has to do with Many-to-Many relationships (#14800) and these reproduction steps apply to the other one. To make them apply to this issue as well, do the following (each task can have multiple assignees and approvers, and the same in reverse where a person can be assigned and the approver for multiple tasks):

1) Create another table for "People" with a column for FirstName and LastName. 2) Create a "join" table called "ApproverJoinTable" with two number columns for TaskId and ApproverId 3) Create a "join" table called "AssigneeJoinTable" with two number columns for TaskId and AssigneeId 4) Create the following relationship for "Approvers":

When trying to do the same for "Assignees", you get the following error:

[ConorWebb96]

Oh, I think I understand what's happening now. Thank you for all the additional information.

This is currently a limitation within Budibase. The screenshots you have shown tell you exactly what is happening here.

You can probably avoid this by restructuring your database. For example, you could use a single join table and just add an ENUM (select) for choosing the user's role.

CREATE TYPE role_type AS ENUM ('Assignee', 'Approver');

CREATE TABLE Tasks (
    TaskId SERIAL PRIMARY KEY,
    TaskName VARCHAR(255) NOT NULL,
    Description TEXT,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE People (
    PersonId SERIAL PRIMARY KEY,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    Role role_type NOT NULL
);

CREATE TABLE TaskPeople (
    TaskId INT REFERENCES Tasks(TaskId) ON DELETE CASCADE,
    PersonId INT REFERENCES People(PersonId) ON DELETE CASCADE,
    PRIMARY KEY (TaskId, PersonId)
);

I'm going to change this from bug to enhancement. Thanks for writing this up. I hope the above table is helpful for you.

[IvanDrag0]

@ConorWebb96 Thank you for the suggestion! How would Budibase handle that setup? Can it work with custom times/enums?

This is currently a limitation within Budibase. The screenshots you have shown tell you exactly what is happening here.

Is this true for this issue as well as #14800?(