Question regarding many-to-many relationships... problem

Need help
1

I’m currently having an issue with deciding how to structure my app. I found below mentioned documentation from @NigelG on the Forum, but I don’t understand it completely, so therefore I opened this topic.

In the below mentioned topic there is a unique link between an Employee and a Project and how to adress this matter in Bubble. However, I have some issues with setting up the structure with my databse. So here I’ll explain what I am aiming:

Employees
Alice
Bob
Claire

Projects
BigProject
LittleProject

1)Create Assignment - Alice + BigProject

And then we add
2) “Alice + BigProject” Assignment to Alice’s list of Assignments
3) “Alice + BigProject” Assignment to BigProject’s list of Assignments

Fair enough, but let’s say I have three roles types which are possible within an assignment.
The roles are “manager”, general member", “client member”
The aim is that, depending of the role the user has within each assignments, he/ she is allowed to modify things.

So, how to set this up?
Employees
Alice
Bob
Claire

Projects
BigProject
LittleProject

And then we add
2) “Alice + BigProject” Assignment to Alice’s list of Assignments
3) “Alice + BigProject” Assignment to BigProject’s list of Assignments

QUESTION
How can I adress the roles to the Assignment, where the aim is that Employees can have different roles in each assignment (Example: Alice is manager of Big Project, and client member on LittleProject.

Is the solution to setup the app like:
Assignment
General member: List of Employees
Project: Project
Manager: List of employees?
Client member: list of Employees?

However, when I structure my app like this, I don’t understand how to create a repeating group with just ‘all’ the users (Irrespective of their role). That’s because the repeating group will request that it will be either a list of general members, or managers of client members…?

2

You could extend the Assignment table so it contains …

Employee type Employee
Project type Project
Role type … could be a text, but you could also have a Role table as well

So Alice can be Manager on one Project and General Member on another…

Assignment Table …

Employee = Alice
Project = Big Project
Role = Manager

Employee = Alice
Project = Little Project
Role = General Member

Then your searches can constrain on Employee, and list out Alice’s 2 roles.

Or they can constrain on Project and list out Alice.

1 Like
3

Wow, that’s a quick reponse from you @NigelG :slight_smile:

Ok, let’s reassure that I understand this completely now. The only thing that’s different between your example and my app is that Assignments can contain more than one employee… So, I will set this up as :slight_smile:

Assignment Table:
Project: Type Project
Employee: Type List of Employees
Assignment Role: Type of Role

Type of Role:
Assignment: Assignment
General: Type text
Manager: Type text
Client: Type text

So,
Outline:
ProjectBig has Alice as a manager, Bob as a general member and Claire as a client member
Project Small has Bob as a manager and Claire as a general member.

Create assignment
Project = BigProject
Employee= add Alice as an employee to the list of Employees
Employee:= add Bob as an employee to the list of Employees
Employee= add Claire s an employee the the list of employees:

And when creating the roles:
Assignment: result of step 1
How to create the roles on the assignments with the above mentioned datatypes?

And ofcourse, I also need to create a List of “User Roles” on the Employee type right, in order to track how many times an Employee is a manager, general member or client member?

4

The way I would suggest doing it is instead of having multiple employees on a single Assignment, you have multiple Assignments.

That way you can have the role on each one.

ProjectBig has Alice as a manager, Bob as a general member and Claire as a client member

3 Assignments created.

Project Small has Bob as a manager and Claire as a general member.

2 Assignments created.

1 Like
5

Ok, thanks, but I have then have an issue regarding with my app. In order for you to understand, I have an app where Freelancers (type User) can collaborate with Employees from Companies. (e.g.chat, share files etc) within closed communities. The aim is to let Employees from Companies share Projects with Freelancers, within a Community (still with me?:))

Example:
So, the communities are setup based on a Company. Within the respective community, some Employees (of the Company) are added and some Freelancers are added. Within the community, the employees have a role of Client member. The freelancers can be Manager member, or general member. So, it’s an example of a many-to-many relationshop, since Freelancers can be among multiple communities, but the role of the Freelancer can be different depending on the Community… :slight_smile:

So, regarding the question how to setup the roles, let’s adjust the aforementioned examples a little bit:

In order to structure the app, there are a few data types:

Data type: Company
Name: text
Location: adresss,
Description: text

User:
First name: text
Last name: text
List of Communities: List of Communities.

Then the issue regarding the link table comes in:
Community:
Company: Company
community_members: list of Users
Assignment role: Type of role? or type of user? Since each community_member will have different roles within different communities, so I cannot store the role on the User type.

Assigning the role within the communities is key, since when a Freelancer is a Manager within a Community, he/she is able to view more specific details then Freelancers who are just a General role.

When I started this topic, I thought I would be easier to discuss it based on your mentioned example of Employees, Projects and Assignments, since then I would just replace the Employees, Projects and Employees with my Data types. But since my app is based on different roles, within different communities, the proposed solution of creating an extra Assignment, is unfortunately not enough.

6

The relationship sounds like it should be …

User has a List of Assignments
Community has a list of Assignments

Assignment is Role/Community/User

A Company can have many Communities

1 Like
7

Yes, I think I’m nearly there, but I don’t know how to assign the roles. I’ve put everything in a scheme, which displays the structure of the app.
I’m struggling with how to setup the data types for the yellow cells. The link table will be community_roles_part.

18
18791×271 16.7 KB

Also, One of the features within the app is that a User has an overview of alle the communityes where he is a member of. Prior to using the link table, this worked fine (Search for Data Type Community–> search all communitys where Current User is a Member.

However, this repeating group doesn’t work, since now it’s a list. I can’t let the repeating group show an overview of all the Communites where Current User is a member off… since it’s a List?

8

Hi hawkeye,

I’m almost lost in your description :grinning: I would use a separate table (=new type) for roles, as Nigel already mentioned.

For those who don’t have any experiences with data modeling I suggest reading or doing some tutorials about entity-relationship models and database normalization. This might look like a theory, but it really helps, especially when you ‘translate’ the concepts (ER model or definitions of entities and relationships among them) into a database scheme, so you can avoid various traps - you can be easily mislead with user interface elements. It should be the other way around :grinning: The traditional process is usually ER model (entities and attributes) -> database scheme (things and fields) -> user interface.

Why is this important? In ERM you can have many-to-many relationships. Traditionally, in relational DB schemas you should break these into two one-to-many connections (i. e. foreign keys). In User interface, the connected thing types are usually displayed as dropdowns (many-to-one) and repeating groups (one-to-many).

So, if I understand correctly, you could have the following entities:

  • Companies
  • Communities
  • Users
  • Assignments
  • User types (Freelancers, Employees)
  • Assignment Roles (Client, General, Manager)

Relationships:

  • Communities to Companies (many-to-one; one company can have many communities, one community belongs to only one company)
  • Communities to Assignments (one-to-many); one community has many assignments, one assignment belong so a particular community)
  • Users to Assignments (one-to-many; a user can have many assignments, one assignment ties one user)
  • User Types to Users (one-to-many; a user can be a freelancer or employee, and this doesn’t change)
  • Assignment Roles to Assignments (one-to-many; each role can be used in various assignments)

If this makes sense, a typical UI can be constructed then. Just use pages or groups for things, and “connecting” things an be followed in DB schema easily by using repeating groups (if the connection is one-to-many) and dropdowns (when connection is many-to-one). This is typical, but either of these UI elements can be replaced with almost anything in bubble.

I hope this helps to think about it in more systematic way.

2 Likes
9

Hi @eftomi

thank you for your feedback and recommendations. As you may have noticed, the trap of working with Bubble and having nog previous coding and database modelling experience, is that you tend to work from UI to database instead of the other way around ;)… Based on your post, I’ve rearranged some things and types and I managed to resolve my isse…
So, big thanks to @NigelG and you for calmly responding to my question(s) which are caused by working the way as mentioned above :slight_smile:

Martijn

10

Morning NigelG,

I just saw this post, and I am struggling with the same issue, many-to-many relationships.

in my case I am trying to built a courier forwarder…its a shipping site but we ship orders to the customers.
I have agents that will be assign to offices in different part of the Caribbean countries. so I need to create a way that I can create employees (agents) and assign countries and offices within that country to them where they can hire staff for the assign office.

customer can send messages to a staff that the messages also seen by the agent.

can you please help me, I am lost on bubble.

so far I created, the account type data, contact type data, user type data add account, and contact as field in user, employees as list of employees in user data type.
its even confusing explaining it, please help.!

11

I just need an understanding of how to structure the database?

12

Hawkeye84

do you mind share how you solve this problem, i have something similar to yours and its kicking me ass:sweat:

erlande

13

Hi @erlandetheart,

ok, without going to much in detail on your current setup data, I think it’s best to start by creating the different data types (entities). So each user has credentials, and is assigned to a (list of) Locations, and a (list of ) Offices.
Each customer is also related to an Agent, so you need to create a new data type, to combine this data (message, sender, receiver, time, date, list of messages, content) and make sure that only the assigned customer can send a message to the assigned agent.

Since I’m also working every day on my skills on working with Bubble, I don’t know if this the best solutions. But keep in mind that you need to create a new datatype to combine data which you can’t store on each indivudual type.

14

OH, Thank you!

You awesome man, thas exactly what I been trying to put together. my confusion is do I create a data type: agent - field, name etc. different from user data type.

you see my thoughts was to create employee with a job title that is agent and assign them to location, office only carried the details such as contact email, phone, website, and staff for that office.
an agent can assign to multiple country and only as access to offices and customer within that country.
how you suggests i can worded the database to do this?

15

@erlandetheart,

Yes, Maybe it’s an idea to setup the workflow just like in the Excel screenshot in this topic. Because, then you can see the relationshop:

  • Each User is related to a (list of location) and (list of countries).
  • Location contains, name, adress, email, contact etc)
  • Country consists of multiple locations.
  • Each Shipment contains a product, sender (User), or receiver (User).
    etc…

What I would also suggest is that you have a look at widgets.airdev.co, and look at the editor files of the Inbox (email) or group messaging ap. Because the workflows of these apps are consistent with that you are trying to build.
Send an email to (group of) user(s) is actually the same flow of information as your shipment app.
It helped me a lot when I was building my product, because then I could learn how something needed to work, and then I could convert it to my own app…

2 Likes
16

Thank you so much sir, I am gonna try that.

17

This topic was automatically closed after 70 days. New replies are no longer allowed.