Are many-to-many relations supported?

Need help Database
1

In an interesting twist, experienced database developers may be at a disadvantage relative to newbies. It’s hard to wipe your mind of the standard relational model. I suggest you write a primer for people like me: “How Bubble’s database relations work, compared to the relational model.”

That said, I have a specific question. It’s the classic many-to-many model. I have organizations, which each have a list of projects and a list of staff. Each project has staff members associated with it. So, Projects-to-Orgs is many-to-one. Staff-to-Orgs is many-to-one. Projects-to-staff is many-to-many. (Many staff per project; many projects per staff.)

I assume I create a data type for each of Organization, Staff and Project. Then I add a list-type field in Organization for Staff and another one for Projects. Then I create a list-type field in Staff for Projects, and a list-type field in Projects for Staff. In other words, I’m assuming I do NOT need to create a new data type that does nothing but link Organization, Staff Person and Project, as I would conventionally.

This would be a cardinal sin in the relational world. Will it work here, or is my model wrong?

Ideally, when I’m assigning Staff to a Project, I’d like to see a drop down list of only the Staff associated with the Organization that is the parent to the Project. On the Staff page, I’d like to see a sum of the dollar values of all the Projects he’s assigned to.

There are even more complexities, but I’d be thrilled just to know if this relation is supported, and if I’m going about it the right way, and if not, what my mistake is.

Finally, what is the syntax for traversing the relation?

I just discovered Bubble a couple of hours ago. Not sure if it’s worth doing the course, because I’ve been writing databases apps for about 30 years. I’m hoping Bubble is the sweet-spot platform I’ve been searching for.

8 Likes
2

Hey @matchen ! :slight_smile: Welcome to the Bubble forum!

I think you’re definitely setting this up in the right way, and it is absolutely possible in Bubble. After you create the new data types (Organization, Staff, and Project), you can then set up the fields to link everything together. If the Staff data type is just going to store a list of Users, you may not need the Staff data type and can use the built-in User data type instead. For example, if each Organization has a list of Users, and a list of Projects, the fields within the data type Organization could be “List of Staff” (Type: User, List: Yes), and “List of Projects” (Type: Project, List: Yes).

That List of Projects is filled with Projects through the workflow. If a User were to create a new Project and then add it to their Organization, the workflows could be:
Data --> Create a New Thing --> Thing: Project --> Fields to modify: [all of the fields you create within the Project data type]

Then you can set another action in the workflow which adds that Project to an Organization’s List of Projects using the “Make Changes to a Thing” workflow:
Data --> Make Changes to a Thing --> Thing to change: Current Page’s Organization --> Fields to modify: Projects add (Result of Step 1… Create a new Project).
Note: *Current Page’s Organization will be available as a choice if you define the page’s Type of Content to be Organization. Or, you can display an Organization’s data by defining a Group element’s type of content to be Organization, and the option to select in the workflow could be “Parent Group’s Organization”. You can also use a repeating group to display an Organization’s content - workflow option would be “Current cell’s Organization”. The sending and displaying data tutorials show how to do that more clearly.

If you’d like to display Staff associated with a Project in a dropdown, you can do this with a dropdown element, and then select “Dynamic Choices”, Type of Choices (User), Choices Source: Parent Group Project’s List of Staff.

If you needed a Staff page for each Staff person, the page’s type of Content could be User. To display a sum of dollar values for the Project she is assigned to, you can have a field within the Project for this: “Value” (Type: Number, List: No). Then in a text element you can use the dynamic expression: Do a search for Projects (search constraint, List of Staff contains Current Page User)'sValue:sum. Or, you could choose to have a field within the User data type which held the Projects they were assigned to. With that setup, the dynamic expression for the text element would be:
"Current page User’s Project’s Value:sum.

Please feel free to let me know if any of this was confusing of if you need any assistance setting this up in your app. The tutorials will absolutely make everything more clear on how to setup the database, as well as link everything together in the workflows. :slight_smile:

7 Likes
3

Thank you for that VERY detailed response. It was perhaps unfair of me to ask that question before I really began to play with the project. I will work through your response. I’m happy to hear there are ways to do it. I’m impressed so far. I need some pretty sophisticated database and programming tools. I’m waiting to run into a wall, but not so far.

Again, thanks for the reply,
mark

1 Like
4

Oh, no worries at all Mark! Happy to help. :slight_smile: Feel free to reach out with any questions you may have!

1 Like
5

There is a current limit to the number of items that are stored in a list (10,000), so keep that in mind. I elected to keep thing separate, and keep a reference to the parent object in a field.

If I wanted to then delete things, simply delete the child things first, then the parent etc. By making sure you have a reference to the parent, if for any reason you get orphaned data in the child collection, you should be able to tell which ones are the orphans for clearing out.

Edit, list limit is now 10,000

1 Like
7

I noticed an interesting effect, when a thing (table row) is deleted, it is automatically removed from any lists that reference it, making cleanup a little easier. A field (column) that references the thing (i.e. as a foreign key) becomes empty.

8

That is what i meant by “you should be able to tell which ones are orphans”, because they will be empty. I should have just said that! :slight_smile:

9

At the moment we don’t have access to the database other than via the Bubbble UI and the API. Perhaps if you ask nicely, you can get get some database tools connected too!

Great, between us we covered that! haha

10

It would be great if we could define a parent child relationship and enable cascade deletes. Maybe one day!

3 Likes
11

Ooh nice! I can imagine the “where did all my data disappear to?” posts. But yes a bit more flexibility in the database would be awesome.

12

Thanks for the tip @DaveA. Didn’t know there is a limitation of 1000 records / list :slight_smile:

13

I have seen it in a couple of posts from emmanuel, here is one;

In one of the other posts, they said they might change this in the future, but at the moment the limit exists.

14

Also another tip
I have tested 2 hypothesis, using lists in order to access different kind of data is slower than using
“Do a search for”.
For example

  1. we have 2 types, User and Cars. You could access Current Users Cars by having under the User field “List of Cars”. Easy to access but slow loading
  2. the same 2 types, but using “Do a search for” Cars- created by current user. This one works much faster

regards

2 Likes
15

The list limit is now 10,000 according to another message that has appeared in the forums.

16

Hi Faye,

After you wrote this, Nigel wrote to say he’d do it the conventional way, using a join table. I don’t know why I thought you couldn’t do that in Bubble. If it works, I’d much rather use that approach than what I described. Is Nigel right? Can I use a join table and then traverse it using Bubble’s access language?

17

I don’t have any coding/programming experience (aside from Bubble); so I definitely have more of a beginner’s understanding of structuring/querying data. With that said, I learned most of what I know about building in Bubble from reading many of Nigel’s posts, so I’d highly recommend going with Nigel’s suggestion!

@NigelG how would you set this part up in Bubble?
"I tend to use link tables to manage many-to-many, particularly when you can easily think of the name of the association e.g. staff <> project could be Assignment. Bubble data access work’s best when you can write fairly natural English to query the data So “Employee’s Assignment’s Project’s Total Spend.”

24

I’m gratified that my little question has generated such a fruitful discussion. I imagine it will be useful for later arrivals. I’m also thankful to all of you for the thoughtful replies.

Nigel has reassured me that I don’t have to forget everything I know about relational DBs in order to use Bubble. I very much prefer to use join tables. I’m still trying to reassure myself that it will never be necessary to create lists between the “manys” in such a relationship. User cm1, above, says that lists are slower to load, which makes sense. Another good reason to avoid them.

I’m now struggling to wrap my head around the traversing of relationships. I get a syntax like employee’s organization’s listOf projects:latest. (I don’t know if “latest” is a legal term: I’m using it as an example. That would be how to find an employee’s latest project without having a direct link between employees and projects.) I’m still not clear where a search is required, and why. I’m not totally clear on which data-access specs are placed in the page/group and which are placed in the display element. I’m not totally clear when what is included in the terms of a search, and what is included in constraints. And I’m not clear if Bubble is a bit loose this way, or if I need more coffee.

It would be nice to have a real tech note from a Bubble engineer that explained what’s going on behind the scenes, and what the best approach is for each scenario. It would also be nice to have an explicit statement of what gets specified where. That’s just how my head works. I come from the ancient land of RTFM.

On top of all this, I’m still trying to grasp what can be placed straight on a page, and what needs to go in a group.

But, I’m just a day or two into my Bubble experience. Still watching videos and learning. Again, I really appreciate all the effort people are putting into answers here. When I get my head wrapped around it, I’ll try and answer at least as many questions as I ask.

5 Likes
26

Avoiding lists will certainly make your life harder as your app gets bigger and more complex.I don’t see any particular reason to avoid lists in general. For the majority of elements I don’t think any difference is noticeable over a search.

In the early days of Bubble we had “lists” and we had “things”. And they didn’t play nicely, or rather they didn’t play at all. Even if you knew the unique id of something … and you did a search… it still came back as a list (of 1). And bubble would not let you do “thing” type actions on it. And if you had a single “thing” on your page (maybe it was passed in, or it was a single cell in a repeating group) Bubble would not allow you to do “list” stuff on it.

The underlying concepts still exist. Searches give you lists (even if there is only 1) and page “type of content” or group “type of content” or a cell in repeating group… give you a thing. But now you can convert things to a list using : converted to a list and vice-versa with :first item in a search.

If you have a thing … Bubble let’s you do the “apostrophe language” from the start.

In the former … doing …

Pasted image709×130 9.22 KB

…instead seems quite long winded. And is no faster.

In the latter example, I have a group that is set to have content of Question. This means that everything inside the group can refernece this particular Question, rather than having to do a search each time. You will have seen this in the tutorial, List > Detail - you pass the thing from the list.

Within the search, the Constraints are the fastest and simplest way to get what you want. But they don’t offer complex queries. For that you use a filter (maybe with a constraint as well) but that brings back everything from the database and then filters it. It can be slower. And then you have advanced filters as well. Doing advanced filters on large sets of data where it is referencing other tables can be pretty slow.

5 Likes
27

Phew! Once again, it’s going to take me a few passes through your answer to absorb it all. But I’m really not being fair: I’m learning as I go, and haven’t been through the tutorial. I’ve done the first sections of the Udemy course. As it happens, I’m going to have to take a hiatus from Bubble for about two weeks, and then I’ll return full-force. I still wish there was a more formal documentation of the product, rather than snippits of each little thing, which provide little context.

1 Like
28

Bubble lets you do a many-to-many (two lists) but as long as you recognise that it will be limited - so you can’t sort by an Employees most current project assignments (no created date on a list), or add any extra data

@NigelG , great information here! Two quick questions on the topic of project “status”, as you have mentioned some limitations around many-to-many:

  1. Is it possible to have a Repeating Group for each project, that sorts and displays all of the assigned employees based on their status (i.e. whether or not they have accepted the assignment), or is this currently out of the scope of Bubble?

  2. From my understanding, is it currently not possible to display in a repeating group, an employee’s upcoming projects sorted chronologically by assignment ‘due date’ (due to the inability of having a date assigned)?

Thanks in advance.