How to structure databases

The most common and frequent question people ask - “How should you structure database?” and I am 100% agree that’s is the most important thing after app security.

Look at the database structures of beginner and some expert Bubble apps. You'll see why it's important. If you've ever built a production-level app, you know how tough it is to change the app's structure later in the development process.

Here's why the right database structure matters:

1. Databases are the foundation of any good app and aren't something you can change often. A messy database structure makes scaling your app difficult, leading to many workarounds.

2. An optimized database saves on Workload units, which translates to cost savings. The more efficient your database structure, the less it will cost to retrieve data.

3. A well-structured database allows for better privacy rules, improving app security.

4. Maintenance becomes easier. If a client or stakeholder wants new features that require database changes, a poorly optimized database means more changes, more testing, and delayed bug fixes.

Now, let's dive into how to structure your database, with practical advice instead of technical jargon.

1. In Bubble, each record automatically gets a unique ID, so you don't need to create one.

2. Consider Table Partitioning: Don't put everything in one table. Use multiple tables based on their purpose and connect them. Often, people dump too much data (columns) into one table and only use less than 20% of data at time. This wastes workload units because fetching records includes unnecessary data.

   Follow this principle - Each table should represent one Entity.

   For instance, if you're developing an e-commerce app and need to manage "product" information, including details like the price, discounted price, product name, category, subcategory, average review, thumbnail, multiple images, product description, color options, sizes, and rating details, it's better not to cram all this information into a single table. Instead, you can organize it across several tables. Here's how:

   
   

   

   
   

   This way, you only fetch what you need, reducing workload and speeding up queries.

   Pros of creating multiple table-

   1. Lower workload units (WU) usage: Fetching less data from the server means it will cost fewer WU.

   2. Quicker search queries: The less data you need to fetch, the faster the query will be.

   3. Easier debugging: Since each table contains data related to a single entity, it's simpler to debug the database.

3. Don't always store public data in the database - Often, we need to keep public data that doesn't change often, such as lists of countries, gender types, certain business figures, and dropdown list options. This data is public, so there are no security issues if someone accesses it. Therefore, it's better to save this information in option sets. Option sets are downloaded and stored in the browser cache when you first time visit the website and reload only when there is changes into Option Sets. It's more efficient to keep this public data in the cache instead of in the database.

   Pros of storing data into option sets-

   1. Since it already stored into cache, you don’t need to call the db to fetch every time you required- that saves the WU.

   2. Option sets are loaded into the browser on the initial page load, which can make accessing this data faster.

4. Cautiously use the list property of data type - In Bubble, we can store a list of data into a column when this feature is enabled.

   

   However, it's best to use it for small lists. The next question you might have is, "How long can the list be?" This largely depends on the "field type" - the simpler the type, the more data you can store. Use this feature for things like storing lists of colors or sizes, which usually won't have more than 10 items.

   
   

5. Use of satellite table: Satellite Data Types offer special storage spaces to keep some details apart from your main data. This setup makes it quicker and easier to access what you need.

   For instance, if you want to search across various product details like the description, ID, and name, but these details are split between two tables – "Product" and "Product Description" – you can make a single table called "Product Search." This table would include columns for the product name, description, ID, etc. This is what we call a satellite table.

   

   Pros of using satellite table:

   1. Quicker data access because these tables are designed for a specific purpose, making data retrieval faster.

   2. A more efficient database since these tables only contain specific data, they tend to be quicker.

      
      

6. Using Default Values: Bubble offers an easy method to set default values that are automatically assigned to a column when no data is present. This feature is only available when creating new items, not when modifying existing ones. This is particularly useful for new users to whom you want to give free credits. You can set a default value for the credits field, and it will be automatically assigned when a new user is created. No additional workflow is required.

   
   

7. Implement Consistent Naming Conventions: Use easy and clear names for tables and columns through out your app. This helps make your database easy to understand and take care of.

   For table names: Use single names, like "User," "Order," "Product" instead of "Users," "Orders," etc.

   For column names: Choose one way to name them and use it everywhere. For example:

   • "Order ID"

   • "Orderid"

   • "Order_id"

   • "order_id"

   Pick one style and stick with it in your app. You can create your own style, but make sure to use it consistently.

Conclusion:

To sum up, keeping your database structure is simple - if you keep following the above mentioned advice from the start of the app. Use multiple tables wisely, avoid overloading data, and keep naming consistent. This simple approach saves costs, makes your app run smoother, and makes future changes easier.

FAQ

Q: How do I check if my database structure is correct?

A: There isn't a single correct answer to this question. However, you can check a few things to ensure your database is set up well. Make sure you're not pulling unnecessary records, use a consistent naming convention throughout your app, ensure records are retrieved quickly, and check that adding new features doesn't require significant changes to the existing database. These are some of the checks you can perform.

Q: I've set up my app's database structure but have some questions. How do I solve them?

A: ChatGPT can help you out. Use the "Bubble Buddy" Chrome extension to generate your app's database schema, then copy and paste it into ChatGPT and ask your questions.

Q: Are there any privacy concerns with my data?

A: Make sure to set up privacy rules for each table, and you should be fine. Also, consider reading our article on security, which covers everything you need to know about data security.

Q: Can I join tables together?

A: You can't join tables in the traditional sense, but you can use table reference data types to access data from other tables.