Many to Many relationship

Need help Database
1

New to Bubble, just mappong out the data tables and am a little insure on how to set up the following. If i explain my app briefly it might help explain what i am having trouble with.

My app is financial services with a fact find for customers. Child Tables to the User (customer) include addresses, children, dependents which could have a many to many relationship.

Each customer needs to exists in their own right as they may be in a finance agreement soley, together or with another customer. there can be up to 4 customers on an agreement.

For example when adding details of the dependent to the finance agreement, how would i set up the table so that any number of the Customers on the agreement would be linked to that dependent. I guess Dependents: User as a List. but in my mind this goes against what i have learned this week in creating a 1:many relationship as would be expected when adding Dependents to a Customer. If this is correct, can the Dependents RG be filtered to show the addresses each individual Customer are linked to and this is an efficient way to return the result of the search.

Hope this makes sense. Thank you.

2

Hi, can’t quite follow your setup. Maybe explain a little about what your app does and core functionality.

Because Bubble allows list fields, in many-to-many situations you can connect one way (a car table with a field for each car’s list of owners), the other way (an owner table with a list of cars owned), or via a joining table. Each of these methods may or may not be optimal depending on the specifics of your app around functionality, search filters, scalability and privacy.

3

I always find it difficult to explain these things well. Hope this helps.

For each customer, we have a fact find of their personal details as child tables to the Customer. These include things like addresses, debts, dependents etc.

Each customer can have many of these but let’s use Dependents as an example - usually 1, 2 or 3 max i’ve seen is 7.
A dependent can have many Customers - in most cases 1 or 2 Customers e.g a married couple.

A married couple for example may have a mortgage together or one of them may have a mortgage on their own or with another person. an example of this would be a Buy to Let mortgage with a friend. This is why we add Customers to Deals in this way and they have their own Fact Finds.

Our app will manage our mortgage applications. We start by creating a Deal which houses all the records for a mortgage application. This is the Parent Table. We then link customers to it (Child table). usually 1 or 2, can be up to 4. We also add notes, tasks, products, call, sms etc as child tables to the Deal.

Privacy -

Customers can only see their dependents and those of people they are linked with on a mortgage application.
Employees can see any Customers records

Displaying Data

When we load a customers Fact Find, we load for one Customer at a time. We only want to display records of Dependents for the selected Customer.

We need to create a PDF with records for dependents for any customers linked to the Mortgage Application. I am conscious that the same dependent could be listed in the PDF more than once if there is more than 1 customer linked to the mortgage application.

4

Hi, a few thoughts…

I think you’ll find that the list field will be what you’re primarily using, since those list fields will only be holding a handful of records.

I assume a “Customer” is someone who can sign in, see their info and take actions, however a “Dependent” is not. So the “Customer” will be Bubble’s special User datatype (ie datatable), whereas “Dependent” is a regular datatype.

You can point your list fields one way or the other (or both ways if there is a reason to do so). For example, a Customer (ie User) can have a field with a list of Dependents, or vice versa. Both will work in terms of creating the relationships, searching and displaying data.

In terms of which way to point is best, I don’t have any definitive advice… in my app I do a mixture based on…
a) All else being equal, a shorter list field is better than a longer list field, because it makes the record “lighter”
b) Which is easier for Bubble to search and display for my specific use cases. I’m especially sensitive to this in repeating groups, since the load on Bubble will multiply
c) Which is easier to maintain as I’m editing records
d) Ensuring my setup will allow my privacy rules (a privacy rule can only look to the User datatype and the datatype you’re setting the rule on; it can look to list fields in either, but not beyond that)

I do use joining tables to create many-to-many relationships in some situations, mainly to avoid long lists, since the conventional thinking is that long lists aren’t good. For example, in my app, Users can favorite Items, and I store those relationships in joining tables rather than in a field on the Item or User record.

There has been a lot of ink spilled on which way to point list fields and when to use joining tables. Some reading I found helpful was:

This mega thread: Alternative approach to the Bubble’s recent tutorials for list of things

Great book (in my view, essential for anyone building a complex app that needs to scale): The Ultimate Guide to Bubble Performance - the new edition is out (now 210 pages!) - #73 by petter

Bubble’s documentation; Creating a Data Structure - Bubble Docs

4 Likes
5

Thanks for this Ed, I’ve read the post and bought the book. Seems i might have gone down the wrong route.

1 Like
6

You are on the right path in normalizing the data. The optionality presented by using the list field to establish relationships also creates complexity for those looking for the “optimal” approach. With smaller datasets, it really doesn’t matter; pretty much anything will perform fine. It’s when you get large that it matters.

I personally love the list field because I have a lot of many-to-many relationships. All of my list fields are fairly short, so it performs well. I’d suggest building some very simple test tables and searches to get a feel for what works best.

1 Like
7

Thanks for recommending the book @ed727. I’ve learnt a lot and it has made me re think parts of my DB design.

Do you know, if a table has 70 fields but only 5 of those fields are included in a RG, does Bubble load all 70 fields for the records it shows or just the fields in the RG. I have a table that has around 70 fields and some of them can contain up to 1,000 characters, i was thinking of splitting the table on a 1:1 relationship but not sure if this is the best way or just to keep it as one table. For th UI, i never need to display more than 20 - 30 fields in an input/edit form at any one time.

8

I’m a newbie too. I have great interest in Bubble. Therefore, I joined here. I would like to learn more. Thank you!

9

Bubble would load all 70 fields, regardless of whether they are displayed or not. However if certain fields aren’t needed for searching or displaying, you can block them with privacy rules and Bubble won’t download them.

There are some upsides as discussed in the book, especially in situations where you have a ton of data heavy records you’re having to search and display. Lighter records are quicker to search and faster to download. However there are also potential downsides, so you have to see how the positives and negatives balance out. Downsides in my opinion are:

  1. Adds complexity and is another thing to have to build and manage
  2. If you are pulling the related record information into a RG, I think it would be a little slower for Bubble to have to pull info from a related datatype instead of the RG’s source datatype
  3. If you move a field into a related record, and need to search that related field alongside searching the primary record’s fields, then you have to perform a nested search, which is slower and more resource intensive
2 Likes
10

You have shared some good information here. Thank you so much!

11

Bubble has given some information on this,plz go through.

Connecting types

“How should I structure my database” is a common - and important! - question. Luckily, your Bubble database is quite flexible and there are many right answers. It’s also easy to “fix” later on if you realized you made a wrong turn. This section walks through a few common patterns for connecting one data type with another, especially in cases where one Thing might relate to many Things of another data type.

Technical users might recognize some of this content as explaining how "foreign key" or "many-to-many relationships" work in Bubble. Bubble does not use these terms and these concepts don't exist in quite the same way in a Bubble database, but you can still easily achieve the same end results.

Use cases

When you define a new field on a data type in your app’s Data tab, you can specify whether it’s a text, number, yes/no, or even another data type. You can also specify whether that field should just have one value of that type, or a list of values of that type.

Examples of when a field is a (singular) value of another data type:

  • A BlogPost data type has a Creator field which is a User

  • An Event data type has a Venue field which is a Building

  • A JobCandidate data type has a Degree From field which is a School

  • A Community data type has a State field which is a State

Examples of when a field is a list of values of another data type:

  • A BlogPost data type has a field for Tag which is a list of Tags

  • A Project data type has a field for Follower which is a list of Followers

  • A User of your marketplace app has a field for Favorite which is a list of Properties that they starred

  • A Contractor in your dispatching app has a field for Job which is a list of Jobs, past and present

For those coming from a technical database background, in Bubble you don't have to declare whether a relationship is "one-to-many" or "many-to-many". If you have data type A with thing a and data type B with thing b, and A has a field that's of type B, Bubble doesn't care how many a's are connected with a given b. You just have to tell Bubble that the field is type B, and whether it's a single or list of values.

Example scenarios

Let’s run with the example of a Bubble app that’s a blog, with the Post and Tag data types. An individual Post can have many Tags, and of course a Tag can be used on many Posts. How do you set this up?

The answer is “it depends” - like other areas of Bubble, you have the flexibility to build this in different ways, but there are tradeoffs that we’ll cover here.

Here are the different major options you could use to create this relationship:

Option 1: Each Post has a Tag field that’s a list of Tags

This means that “Blog Post #123” might have Tags “Cooking”, “Travel” and “Fun”, all stored on the Tag field of Post.

The pro with this approach is that when you have a Post and want to look up which Tags it has, that’s easy! Let’s say you have a page with assigned data type of Post (each Post on its own page, for example) - to get the Tags would be Current Page's Tags . This is a quick query.

The con with this approach is that if you have a Tag and want to look up which Posts have that Tag, the query is a bit more roundabout. Let’s say each Tag also has its own page that lists all the Posts with that Tag. To find that list of Posts, you’d have to Do a search for Posts with a filter for Tag contains Current Page's Tag . This is a slower query (for most normal-sized blogs, this is probably not noticeably bad from a performance standpoint, but you can imagine parallel situations that have much bigger scale).

Option 2: Each Tag carries a list of Posts that have that Tag

This means that Tag “Cooking” has a field with “Blog Post #123”, “Blog Post #153”, “Blog Post #89”, and so on.

The pros and cons here are the inverse of Option 1 above. The query to get all Posts of a particular Tag is easy, but to find all Tags on a single Post is a slightly longer query.

Option 1+2: You could do both of the above!

Each Post keeps track of which Tags it has, and each Tag keeps track of which Posts have that Tag.

There’s nothing stopping you from doing both of the above options! This way it’s quick to get all the Tags on a given Post and also quick to get all the Posts of a given Tag.

The downside here is that you need to do double the work. Each time a Post gets assigned a new Tag, you need to remember to add it to the Post’s list of Tags and also the Tag’s list of Posts. Not too much extra work, but it can be easy to forget to do this, leading to data consistency problems.

Option 3: A new PostTag data type that has one field for Post and one for Tag

This means that you create a new PostTag thing to handle each Post + Tag pairing. So one PostTag for “Blog Post #123” + “Cooking”, another PostTag for “Blog Post #123” + “Travel”, and another PostTag for “Blog Post #123” + “Fun”.

(Technical folks may recognize this as a “joining table”.)

This is kind of the “compromise” solution between Options 1 and 2. To find all the Tags of a Post, you’d have to Do a search for all PostTags where Post = Current Page’s Post, or the opposite to find all the Posts of a Tag. This makes the search in both directions a bit slower than the fast search offered by Option 1 or 2. Also, note another small disadvantage is that it’s possible to create duplicate PostTag entries, whereas if you were using a list in Option 1 or 2, Bubble automatically de-dupes entries in a list.

Why would somebody choose this option then? The answer is that it’s much more scalable. If a data type has a list of things for a given field, that list maxes out at 10,000 entries. This may seem like a lot, but imagine, for example, how many Pinterest posts one User might heart over their lifetime! Option 1 or 2 would break at that scale, but Option 3 would handle it fine.

In practice, if you’re creating this kind of relationship and expect one thing to have a list of more than 100 values of another thing, we recommend going with Option 3 .

To summarize, the option you choose will depend first on the scale you need for your Bubble app (which might determine if Option 1 or 2 is even an option) and second on which direction(s) you might want to make queries super fast. Note that you could also start with Option 1 or 2 then later migrate to Option 3).

Special example: when friendships are complicated

Many apps might want to build a ‘mutual’ relationship, like a User being able to friend another User. The same options generally apply, but things just get a little confusing because everything is done with the same data type! In this case, you could go with Option 1 or 2, but if Users Alice and Bob are friends, it will get confusing if Bob is in Alice’s list of Friends and not vice versa. Option 1+2 is a better way to go. For Option 3, you may want to modify the setup a little bit - instead of one field for one User and a second field for the other, instead you may want to have one field for “Friendship Members” that’s a list of Users - this puts Alice and Bob “on the same footing” so to speak, so you don’t have to worry about who’s “Friend1” vs “Friend2”. (Since social networks tend to have a lot of such relationships, we recommend trying Option 3 for this case.)

7 Likes
12

Great answer - so glad I found it!! I’m new to Bubble, but not to dynamic websites build from SQL dbs and many-to-manys was a big part of a website CMS I built years ago for complex legal sites. All of our many-to-many relationships used a joining table. I was looking for best practices for doing this in bubble. Very similar approach/options to what we did in the day!

There is one other advantage to a joining table “IF” relevant to your app that we often used and that is storing data unique to one record (thing) in the joining table. Using your above example about user friends. Maybe you want to capture the type of relationship for a particular “friend”… example, family, colleague, etc… A great place to store this information is in the joining table. Another example from my WCMS days was storing the date a lawyer joined a practice group - which was often NOT the date the joining record was created.

1 Like
14

This is great @vaibhav.malhotra1221 , thanks for sharing!

Following-up on option 3 , once you have set the joining table , I have on specific question on the best way to do the searches to render the information. In my case I have a knock-out tournaments app, and as the tournaments can get up to 128 players (for now, it can get bigger later), I have decided to make a joining table for the positions within the Knock-out chart (different to the “tournaments” data type I already have), so that this new datatype includes the tournament position, the related tournament, and the player in the position. For example, for an 8 player KO tournament to make it simple, it starts at quarterfinals, so I have position QF1, QF2, and so on until QF8. QF1 and QF2 dispute for the SF1 position (semifinal 1), so I have have the players in each position with this type and be able to expand players if needed in the future. Give this, the joining table is basically full of players based on their positions related to the tournament , so it gets bigger as the tournamnets advance. The question is, for each position within the KO chart that I render in each tournamnet page, what’s the best way to pull the players in each position? I have some options:

  1. It can be to have a “do a search for” for each position within the chart, this would work well if bubble does a parallel search of all positions in the page’s tournament, otherwise not sure if it’s the best option.
  2. If on 1 the searches are not in parellel, then pulling all those positions of the page’s tournament to a RG and then reference each position in the chart to the RG I pulled
  3. Forgetting about the joining table as it will definitely never going to get to more the 300 players, and just adding 2 list fields where I deposit all the positions and the players in the same order after each tournamnet match is played and therefore just pushing those players in the list to each of the positions in the chart by referencing the positions list and the index of that list where each position is located to be able to use that index on the other players list.

Any insight or idea? Appreciate your help.
Diego