Designing your database is a fundamental step for all Bubble apps, and as BL already has most of the design aspects done for you, this is the best place to start.
Furthermore your database design will likely guide the required UI of your app. For example, if you're making a project management tool which has teams, projects and tasks, then it's likely your dashboard will need a separate view for each of those three data types.
Database design is one of those things that some people find easy but most find hard.
Below is a video I made on database design. It's worth watching!
If you're stuck on your database design, ChatGPT does a half-decent job of designing a database structure for you.
Here is a prompt I compiled which gives fairly good results. It contains some information and examples to help ChatGPT with its task.
To use this prompt you also need to add a description of your app to the end of the prompt (see below).
You're a software engineer and your task is to design a database structure for a Bubble app including privacy rules.
You should specify all the data types and option sets required for the app, and list all the fields within each.
The database structure should be designed to enable effective privacy rules to be put in place.
The app will be built on a template which already has the datatypes User and Entity. Each user is tied to an Entity. It is not necessary or advisable to add a list of users to the Entity data type.
Key things to know about Bubble:
To create a link between two data types, you do not need to reference an ID. You just need to reference the data type. For example, a User can have a field Entity (type: Entity).
Two-way data linking is almost always not advisable as it is more work to maintain. Usually you should have a link on the child data type to the parent item. Lists should only be used where the list is likely to be less than 50 items in length. Otherwise it is better to just have a link on the child data type. If it is a many-to-many relationship, a linking data type may be necessary.
Do not create two-way links unless absolutely necessary.
Do not create lists of child items on a parent unless the list is guaranteed to stay below 50 items in length, and even then only when absolutely necessary.
Privacy rules: With privacy rules, you don't prohibit access, you grant it.
Bubble features a privacy rule editor that lets you control the privacy settings on all your data types in one central place. You access this by going to the Data tab and then clicking Privacy.
Privacy rules protect your data types in the following ways: You can stop specific fields from being viewed You can stop the data type from being found with Do a search for You can stop users from viewing uploaded files You can stop users from making changes with auto-binding Example 1:
Data type: Product
Title: Logged in
Rule: When Current user is logged in
Users who match this rule can: View all fields, Find this in searches, View attached files
The rule above says "When the current user is logged in, users who match this rule can view all fields". Since we are working on the data type Product (visible in the header of the form), we could extend that sentence to "When the current user is logged in, users who match this rule can view all fields on the product".
Example 2: Shopping cart
We have a custom data type called Shopping Cart. This type should only be found in searches and viewable by the person who created it. But wait: if no one else can see the order, how are we to deliver the product?
Someone else needs to be able to see it, but it must be restricted. We'll set up a custom field on each user called Admin (yes/no), which grants access to see anyone's cart. On the cart, we can use the already existing and automatically populated Created by field.
This setup requires that we set up three privacy rules:
1. The first gives access to search for and view fields on the cart if you are the one who created it
2. The second gives access to search for and view fields on the cart if your admin field is set to yes
3. The third is the automatically generated Everyone else rule: this states that everyone else should have no access
Data type: Shopping cart
Title: Own cart
Rule: This cart’s creator is current user
Users who match this rule can: View all fields, Find this in searches, View attached files
Title: Admin user
Rule: Current user’s admin is “yes”
Users who match this rule can: View all fields, Find this in searches, View attached files
Title: Everyone else (default permissions)
Users who match this rule cannot: View any fields, Find this in searches, View attached files
Example 2: Sharing tasks
In this example, we'll look into how properties on the data type to which the privacy rules are applied can further grant access under certain circumstances.
Let's imagine we have a task management app, where access to tasks follow two simple rules:
1.The creator of the task has full access to their tasks 2.The creator can invite other users into a specific task, granting them access
In that case, we need some way to determine whether a user has been invited to a specific task. Keep in mind, we want to add that permission only to certain tasks, not all of them.
We can solve this by adding a new custom field on the Task data type that contains a list of all Users who have been invited to it. Whenever we want to give someone access to the Task, we add their name to the list using a workflow.
Since the Invited users field is set to be a list, Bubble lets us use the add function that will add that single user to the list of Invited Users (if we wanted to add a list of users we would use the add list function).
Now, to set up our privacy rule, we will again need three rules:
1.The first states that if the current user is the Creator of the task, you should have access 2.The second states that if the current user is in the list of invited users, you should have access 3.The third (everyone else) states that no one else should have access
In the privacy rule editor it will look like this:
Data type: Task
Title: Creator
Rule: This task’s creator is current user
Users who match this rule can: View all fields, Find this in searches, View attached files
Title: Invited
Rule: This task’s invited users contains Current User
Users who match this rule can: View all fields, Find this in searches, View attached files
Title: Everyone else (default permissions)
Users who match this rule cannot: View any fields, Find this in searches, View attached files
Again we can see that the two rules grant access under special circumstances, while the bottom does not grant the permission to everyone else.
Below this prompt you should add a description of your app.
Below is a basic example description. Try to be as detailed as possible in your own description. Describe all the different types of users, and all the different functionalities of your app.
My app is a multi-tenanted SaaS CRM for veterinarians.
Vets can set it up for their own practice and invite other staff to join their practice.
Multiple users within the practice can log in to that vet's account.
They can add patients (pets) to the system.
They can add customers (pet owners) and record their personal details.
They can record treatments
They can record surgeries
They can record insurance plans.
They can create invoices and bill customers.
Once you get your output from ChatGPT you'll need to set up the database and option sets in your Bubble app.
Don't stress too much about nailing your database structure from the outset, Bubble makes it quite easy to make changes later on!