Database

Foundation of your app

The data structure is the foundation of a Bubble application and setting up the database should be the very first thing you do. In fact, most of the time it’s something that we at AirDev do before even opening the Bubble editor. Instead, we pull up a spreadsheet and create the entire database structure there first, thinking through the data types and the fields until most details fall into place. This ensures that we have a clean & robust set-up that’s ready for the logic to be built on top.

The alternative is setting up your database as you build functionality and, while it’s sometimes necessary (if you can’t anticipate everything you’ll need in the beginning, such as when the project evolves as it’s built), it should mostly be avoided because your app is likely to end up with a messier data structure. And that’s a big deal because as the application grows, the database structure really influences how easy or hard it is to evolve and maintain your application. With a mature application, it may mean a difference of weeks or months of development effort. That’s a pretty major impact of a few clicks that the Bubble database requires to set up.

The essentials

To start, let’s take a look at the core things you should know when building your database.

Data types and data fields

Data types are essentially tables of data where each row represents a single object that has one or more data fields. A data field is a single piece of data about that object. So, if you’re building Twitter, you may have a data type called “Tweet” that represents a single tweet and that data type would have a bunch of fields that represent information about that tweet - the text of the tweet, who created it, how many likes it has, how many retweets it has, when it was created, etc.

The first step in setting up your database is figuring out what data types you’ll need. There are a couple of ways you can think about that:

1. Think of the actions that take place in your platform. There is a good chance that your data types correspond pretty well to those actions. If you’re building Twitter, here are some actions that may come up:

   1. Registering for the platform - this would require “User” data type

   2. Posting a tweet - this would require a “Tweet” data type

   3. Following someone - this would require a “Follow” data type

      1. (Note: we'll explain more of the motivation for these design choices in the next section.)

2. If you’re familiar with Excel, think of how your data would be represented in a spreadsheet. If you’re building Twitter, you’d probably have multiple tabs, one that has all of the users who’ve signed up, one that has all of the tweets that have been created, and so on. In the Excel example, each tab represents a data type, each column represents a data field, and each row represents a thing.

Linking objects together

When creating your database, it’s very important to properly link data types to each other. This ensures that you can look up a user’s friends, a listing’s reservations, or a product’s purchases.

A field vs. a separate object

A choice you’ll be faced with is whether a link should be set up as a field on a data type or a separate data type altogether. For example, if you’re creating the concept of “following” in a social media app, you might have a field called “List of follows” (of type list of Users) on a User OR you might have a new data type called a “Follow” that would have fields that link the two users together. In this situation I would think about the future and whether you’re likely to want to capture other data about that relationship in the future. If the answer is yes, then you’ll want to have a separate object and if the answer is no, you can keep it to a field. So in the following example, the answer is likely to be yes because you may want to record when the user started following someone and you won’t be able to do that without a separate object.

One-way vs two-way data linking

If you’re building AirBnB, you will probably have a User object and a Property object. The decision you’ll be faced with is whether you should have a “User” field on the Property, a “List of properties” field on the User, or both. Here’s how we think about it:

1. As the first step, always start by having a single link on the object that is subordinate to the other object. In this case, the Property is subordinate to the User, so it’s worthwhile to start with having a “User” field on the Property

2. Then, the question to answer is: will the relationship be used frequently AND will the list remain relatively small if implemented? If the answer is yes, then you should also set up the reverse linkage. In this case, the list of properties that the user has will be used frequently in the application and also the list will likely not get that big, which is why I would advise setting up a two way linkage. If we were setting up Messages for example, I probably would only do one-way linkage because the user might have thousands of messages linked to them and lists of that size get unwieldy in Bubble.

Privacy rules

Privacy rules determine what data is able to be sent from the server to the client (your computer) and thus are very important for making sure that your user data is secure. You should set privacy rules up right after you set up the database and before you start development. It’s much more difficult to set them up after the development is done, especially if you determine that you need to adjust your database to create appropriate privacy rules.

You will sometimes need to create extra fields in order to have proper privacy rules. To illustrate:

Let’s imagine that you’re building project management software. You may have a data type called Project, a data type called Task with a Project field on it, and a data type called Message with a Task field on it. It’s a pretty straightforward set up where projects consist of tasks, which consist of messages. If you want to make sure that messages aren’t seen by users who aren’t on the project, you may want to construct an expression like “Current User’s Projects contains this Message’s Task’s Project”. However, that expression isn’t fully supported by the privacy rules, so instead you may have to also store a Project field on the Message object. That way your privacy rule changes to “Current User’s Projects contains this Message’s Project”, which is fully supported by privacy rules.

See also:

Anticipate the future

As you’re setting up your database, it’s important to think ahead to the functionality that you aren’t building yet but are planning to build in the future and set up your database to be well positioned for that functionality. For example, if we are building a SaaS product, we will often start by building it for individuals to start but will anticipate that in the future we’ll need those individuals to be part of organizations and share access, permissions, etc. In that case we will create an “Organization” data type in addition to the “User” data type. Each User will have an Organization attached to them and each organization will have only one User to start but this seemingly redundant set up will make it 10x easier to build out the organizational functionality when it becomes necessary.

Tips and tricks

Database object(s) instead of hardcoded values

A really useful practice is creating a data type that will store information about the platform that would normally have to be hard-coded instead. This way, we avoid using "Magic numbers", or numbers that are hard coded (i.e. manually typed) into hard-to-find places of an app.

An example from the Canvas template

In our Canvas template, we have a Website data type which contains hard-coded data used across the application. A Website has multiple fields that contain info about the platform - the name, the logo, etc. Then, anywhere we have to reference things like the name of the platform, we don't hard code the name, but instead link the display to the Website object. That way, if we ever have to change the name, it becomes as easy as going into the database and updating a single field. Each app should only have one Website in the database, although if you come up with your own solution for hard-coded values, it may look different depending on your use case.

Creating static data types

Static data types are data types that are set by Admins or by the platform developers, and which are likely to change very infrequently.

Here are some other ways that using database objects instead of hardcoded values can be useful:

• Legal docs: for documents like the Privacy Policy and Terms of Service, we store these in variables on our database (in addition to documents in Google Docs). These documents are then displayed as text in the application.

• Pricing plans: let’s say that you have 3 pricing plans for your SaaS platform. Each plan has a price and maximum number of users (1, 10, and 100). One way to represent this is to have a data type called “Plan” that has fields that include the max number of users, so that later on, if you want to change the number of users that are supported by a plan, you can just update a single field in the database.

• Permissions - if your application has multiple user types, each with different permissions, it may make sense to create a Permission data type that will capture all of the different permission types that exist and all of the permissions that each type has. If you’re building Slack and want some users to be able to join any channel and some other users to only be able to join public channels, you would have a Permissions object with field called “Can join any channel” and set that field to “yes” only for the Permissions that allow that action. Then you can refer to “Current user’s permission’s Can join any channel” to see whether that user can join a non-public channel. This is much cleaner than the commonly used alternative of just referring to the name of the user type and hard-coding their permissions.

Yes/no fields vs texts or dates

You’ll often need to record whether something is true or false - whether a user is an admin or not, whether a user has verified their email or not, whether a blog post has been published or not, and so on.

One data type we can use for this is the yes/no (boolean) type. It is usually better to use a text field type or a date field type in order to be able to get richer data and have more future flexibility, rather than a yes/no field type which can only record 2 different values.

For example, if you want to record whether or not a User has signed an agreement, it might make sense to record a “Date agreement signed” of type date instead of “Agreement signed” of type yes/no because the former will provide you with more information in a single field. Both whether the agreement was signed (date isn’t empty in Bubble-ese) but also when it was signed (value of the field).

The downside of this approach, of course, is that a yes/no field is more straightforward to follow and remember. Additionally, if you use a text field, you have to make sure that you’re always using the exact values of the options that can go into that text field.

Naming conventions

It’s a good idea to name your objects and fields in a consistent way. Specifically, here are some things that you may want to do:

• If there are fields on an object that can be grouped somehow (for example fields on a Tweet that record stats about that week), you can add a prefix before each of those fields, so that they appear next to each other on a list

• If there are fields that you’re no longer using, it may make sense to add a “DEP” (for deprecated) or a similar prefix to the name. That way it will be apparent that the field isn’t used anymore

“Created by” field

By default every Bubble data type comes with a “Created by” field, which records the user who created the object. However, it often makes sense to create a separate field to record that same thing, so that you have control over it. For example, at some point you may want to create those objects through the API and attribute them to people.

Commenting on fields

When creating the database you should comment on the fields where the purpose of those fields isn’t immediately clear, to make it easier for yourself to maintain the application in the future. This is especially important for text fields that represent a multiple choice option. For example, if you have a “Type” text field on the “User” object, you should record all of the different types that can go into that field.

Option Sets

There is a feature in Bubble that allows a set of data to be loaded when any page in the app is loaded. This is good for small amounts of data (e.g. categories of Users: Buyer, Seller and Owner) that will be used all over the app. it eliminates unnecessary requests and helps the app's logic look a bit cleaner, too, as you can select a specific app option instead of manually typing in a text or other value stored on database objects.

We have an app with a few premade option sets that you can copy into your app.

Example database

Facebook (click here to see example in a spreadsheet)

This is a sample database structure for a simple, early version of Facebook. This assumes the following functionality:

• Users from certain approved universities can join the platform after verifying their email address

• Users can search for other users at their university and add them as friends (requires approval)

• If friends, users can view each other’s full profiles and write on each other’s “walls”. Users can also write on their own walls.

• Users can poke each other

• Users can message each other

• Users can create and join groups

• Users get notified when important activity occurs on the platform