Structure the database
Last updated
Last updated
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.
To start, let’s take a look at the core things you should know when building your database.
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:
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:
Registering for the platform - this would require “User” data type
Posting a tweet - this would require a “Tweet” data type
Following someone - this would require a “Follow” data type
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.
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 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.
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 I think about it:
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
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 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 them up right after you set up the database and before you start development because 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.
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.
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. In our Canvas template, we call it the Website data type and that data type only has one thing in it. That thing 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 type it in but instead link it to the 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.
Here are some other ways that using database objects instead of hardcoded values can be useful:
Pricing plans - let’s say that you 3 pricing plans for your SaaS platform. Each plan has a price and maximum number of users (1, 10, and 100). The best way to handle that 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.
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.
You’ll often need to record whether something is true or false. The default field that comes to mind is the boolean (yes/no) field type. However, in some cases it might make sense 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. 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, not just whether something was signed (field isn’t empty) but also when (value of the field). The downside of this approach, of course, is that a yes/no field is more straightforward to follow and remember (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).
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
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.
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.
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
