Database Design in Bubble – What you need to know (includes real-world example)

Differences between traditional database design & Bubble database design

We've noticed years ago that there are quite some differences between traditional databases & Bubble database and along the way, we have been discovering others on the go and we can sum them up as follows :

1. Object-Oriented vs. Relational

Traditional relational databases rely on « Foriegn Keys » to establish relationships between tables.
So for example :
if you have 2 tables (User (UserID, UserName), BankAccount (AccountNumber)) so the relationship between them is 1 to many which means that 1 user can have many bank accounts while 1 bank account could be owned by one and only one user. You would put the «UserID» field from the «User» table into the «BankAccount» table to represent this 1 to many relationship and this makes the «UserID» field here a « Foreign Key » in the « BankAccount » table.
On the other hand, Bubble’s database is Object-oriented which means that every « Data Type » (equivalent to Tables) is more like an object with fields and relationships so in Bubble you don’t need to manage relationships manually but rather than that they are managed through references between objects.
So if we take the same example above; in Bubble, you would put the object « User » as a field in the « BankAccount » data type so the data type « BankAccount » will look like this :
Data Type : « BankAccount » - Fields (AccountNumber (number), Owner (User) )

You see here the field « Owner » is of type « User » so we’re linking the « User » object itself rather than putting the primary key of the « User » as a foreign key in the « AccountNumber » data type.

2. Built-in List Fields

Bubble allows you to create fields that are lists of things, which is does not exist in traditional relational databases. If we take the above example, it could be represented like this in Bubble :
Data Type : « User » - Fields (UserName, AccountNumbers (List of AccountNumber))
Data Type : « BankAccount » - Fields (AccountNumber (number)

The « List of AccountNumber » you see above doesn’t exist in traditional relational databases.

3. Reverse Referencing / Inversed Linking / Child-Parent Linking

Whatever you name it. In Bubble you can design your DB so that the child refers to the parent and then search for all children belonging to a parent. By the way, this proved to be the better option in Bubble when it comes to scalability & performance.
So again the above example should look like this :
User (UserName)
BankAccount (AccountNumber, Owner (User))

In Bubble, this is better than doing like this : User (UserName, AccountNumbers (List of AccountNumber)) BankAccount (AccountNumber)

We always prefer to go for inversed linking rather than Lists for better scalability & performance.

4. No Table Joins, Just Searches

While in traditional databases, you’d join tables. In Bubble, you search for data and traverse relationships directly. You wanna use « Where » conditions, these are the constraints you add in your « Search » in order to narrow down your search results.

5. Flat Structure vs. Normalized

Traditional databases prioritize or embrace nomalization which is the process of organizing data in the database tables and establishing relationships between the tables according to rules that force you always to avoid redundancy.
Meanwhile, in Bubble you should denormalize data for faster reads. In other words, while traditional databases embrace a chained set of relationships and avoid redundancy, Bubble database design should be flat (i.e. no chains) so that you can access data faster and without the need to perform several nested searches or complex expressions. I will show a detailed example on what this means although it’s worth mentioning that this also serves « Privacy Rules » settings in Bubble.

How many times you wished there was the possibility to configure such a privacy rule : "W’s X’s Y’s Z’s field is Current User’s company" While in Bubble you find that this is not possible in the « Privacy Rules » settings ??

Although we used to see this previously as a limitation, but we believe it's a good sign & reminder that database design in Bubble is different and that Privacy Rules should be taken into consideration at the very beginning during the backend design. For us, This is a reminder that database in Bubble should be "flat" rather than "Chained" as this will not only make privacy rules settings easier but also searches more efficient with no need for nesting conditions in Searches (i.e. no complex expressions across your application).

Bubble database design should be flat (i.e. no chains) so that you can access data faster and without the need to perform several nested searches or complex expressions. This also serves « Privacy Rules » settings in Bubble.

6. Privacy Rules Over Joins

Unlike relational databases that have role-based access at the query level, Bubble uses privacy rules set at the data type level. This means that you should properly define Privacy Rules to prevent unauthorized reads/writes and protect sensitive data. Yes it’s your responsability as a developer and privacy rules are a cornerstone in your application security journey.
This means that the same « Do a Search for » expression could return different results based on the privacy rules settings or even might not return anything at all if the user’s rôle doesn’t allow him to access this data type at all.

You should know that if you write complex expressions to retrieve data or if there are some privacy rules that you can’t write because you need X’s Y’s Z’s expression which is not allowed in Bubble then trust me your database design is not optimized.

7. Option Sets vs. Lookup Tables

For fixed data like « categories » or « statuses », Bubble uses Option Sets instead of lookup tables while in traditional databases we used something called « Lookup Tables » which are simply tables that usually have an « ID » & « Value » columns to respresent different « categories » or « statuses »

Just always make sure that you don't store sensitive data in Option Sets, otherwise you're good to go.

Real-world Example

Let’s say that you are creating an application for project management where each company signs up on your application, create a company profile and then start defining projects and each project has tasks.
So it’s clear that you have these data types:
- User
- Company
- Project
- Task
Each user belongs to a company, each company can create many projects and each project can have many tasks.

So the question now is how to structure your database for scalability & performance and also put in your mind early at this stage the « Privacy Rules » ?

Based on the above concepts, your database design should look like this (We will focus more on relationships rather than every data type fields for simplicity) :
- User (userName (text), company (Company))
- Company (companyName (text))
- Project (projectName (text), company (Company))
- Task (taskName (text), project (Project), company (Company))

Note the following :
- We didn’t use lists although we could’ve used them
- The field company (Company) exists in both data types « Project » & « Task ». This shows that the relationship between the data types is flattened not chained and this would also help in configuring your Privacy Rules correctly and retrieve results more efficiently because there is no need for deep lookups.

Summary / Key Points

- Use inversed linking rather than lists for better scalability
- Avoid deep chain expressions in searches as they are slower and consume more WUs
- Add redundant shortcut field to make your database structure flat not chained as this simplifies privacy rules & searching
- Set privacy rules on each data type properly and consider them early during your database design phase. Don’t just build then configure privacy rules later.
- Maintain redundant field consistency. Although we don’t like redundancy but here you trade off write complexity for read performance
- If your design is correct, your privacy rules will match your business rules and will apply without any problems plus you won’t find any need in your application to do complex expressions or nested / complex searches.