"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 article 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.
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.
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.)