✨ NoCode database structures & the magical power of triggers - Issue #3
Hello there!
This week I’m going to talk about one of the most important parts of any ‘nocode’ build - structuring and storing your data.
Spending time to make sure you get your data structure right at the start of a project can help radically speed up your build and reduce the risk of costly refactoring down the line.
If you’re building a SaaS app, there’s a fairly common structure you may want to follow when setting up which can be broken up into the sections….
Users
Companies
Features
Let’s take a look at each of these in turn…
Users
These are the users who log into your app, who often will belong to a company. In my apps, I always use a company data type to group together users.
You want to be able to allow users who are connected to a company view specific data.
To achieve this we will create two data types.
Company. This is the data type which will hold information about a specfic company. Many users will be linked to this company.
User. This is a default entity in Bubble. We will add a new field to this data type called ‘Company’ which will link to the entity we created above. This means we can link many users to each company.
You will need to use a workflow to attach each user to a company during your signup process. The workflow will likely look something like this….
Create a new user
Create a new company
Attach users (created in step 1) to company (created in step 2)
Link many users to a company.
Companies
Once we have our users linked to a Company entity, we can start creating some of the other data types our app will need.
Its likely you will need to have Pricing Plans, Subscriptions and Payments records stored in your database.
Each of these will be linked to your company in the same way we linked users earlier.
This will mean we can easily keep track of all payments, plans and subscription items each company has.
Tip: Bubble has a new feature called ‘Option Sets’ which are very handy to use for data which is static in your app - like your pricing plans. You can use ‘Option Sets’ in pretty much the same way as any other data type in Bubble and they are available througout your application.
Link entities like Plans, Payments & Subscriptions to your company entity.
Features
This group generally contains tables linked to the specific nature of your app. For my app UserLoop, I have tables for Surveys, Responses, Emails Sent, Tokens, API Keys and Webhooks.
It’s likely each of these data types will be linked back to your main ‘Company’ entity - so you can grant privacy permissions to any user who is a member of that company to be able to view the data.
Ensuring you’re linking each data type up correctly at this stage is super important - spend a lot of time thinking about each table and what other things it needs to be connected too.
There is a fantastic resource here at Vertabelo which gives you more guidance and ideas on how to structure the SaaS database for your app.
Privacy
Once you have all your data types set up, the next big issue to think about is your app privacy settings.
These are what control who can see what data in your app, so they are very important to set up and get right.
As a general rule, if you have created a ‘Company’ data type to which every user is attached to, you will want to set up privacy rules for each data type which look something like this….
Decide which users can see each type of data - make sure you set this up!
Current user’s company contains this company.
If you don’t set up privacy rules it means all the data in your app can be read by anyone - so don’t skip this!
Triggers
An unbelievably powerful new feature in Bubble are Database Triggers.
It can take a while to fully get your head around how they work, but once you do they will revolutionize how you build and structure your app.
Essentially - database triggers work by watching for changes in your database, then triggering off a specific workflow when that data changes.
This means you can simplify your app by removing duplicated workflows - Database Triggers work whenever data is changed.
Whether it’s through your UI, the API or even the data inspector tab in Bubble.
I’ll share an example of where I use Database Triggers in my app to complete a very specific task.
When a customer signs up for UserLoop, they need to authenticate with Shopify.
I have a field in my database for ‘Shopify Token’ which is populated with a token when the user has finished signing up.
I use a database trigger to monitor for when a new token is detected using the following logic…
When a new token is detected in the database, this workflow runs...
This workflow then runs a bunch of API calls to Shopify using the token which is now in the database, all automatically!
Database Triggers are really handy for doing these kind of jobs, I’m going to be producing some videos explaining the magical power of database triggers in future so stay tuned!
SQL Connector
As well as the built in Bubble database, it’s also worth noting that Bubble supports connecting to PostGres, MySQL and Microsoft SQL.
https://manual.bubble.io/core-resources/bubble-made-plugins/sql-database-connector
This means you can just use Bubble to a build frontend for your app if you already have a database set up elsewhere.
Or - perhaps you have a database set up in Airtable - no problem! You can use the built in Bubble Airtable plugin to interface directly with your data in Airtable from Bubble.
❤️ Thank you!
Thanks to everyone for your amazing support and for subscribing. I hope you enjoyed this issue.
I’m excited to dive deeper on more subjects over the next few weeks, here are a few topics I’m going to be covering.
Building an API for your Nocode app & documenting it
Integrating third party services like Shopify & OpenAI using the API connector
Automaton - how to get your app to run on autopilot using automation and alerting.
What did you think of this issue, how have you structured the data for your app? Hit reply or drop me a tweet, I always love to hear from you!
And as always, please share this newsletter with your friends, I’d love to spread the word about building nocode software to as many people as I can!