Hi all - have been struggling with data load times in my app, so posting this in the hope of getting some ideas and thoughts on better approaches out there that might exist.
I have a nested data structure in my database, which I’ll describe in basic form below:
Cuisine
Name
[List of Recipes]
Recipe
Name
Some other info
[List of Ingredients]
Ingredient
Name
Nutritional values [Object]
Nutritional Value
Some data on protein, carbs, fat, calories etc
When i want to show a summary of a cuisine or recipe, I need to do something like:
Search for Cuisine… each item’s Recipes…
::count (- total recipes for cuisine)
… each item’s Ingredients… each item’s Nutritional Value… calories::sum (total calories in recipe
This seems to be leading to very slow load times for some of the page elements.
Is there a better way to approach this?
Perhaps in terms of the data structure of the underlying data?
Or is there a way to create some kind of a method which would take in a recipe (for example) and return some summary metrics (total ingredients, total calories etc)
Do not put a list of recipes on the Cuisine data type, instead on the Recipe data type put a field for Cuisine. Then when you want to show all Cuisines, you are not also loading the Unique IDs of all recipes that are listed. When you want to show all Recipes of a particular cuisine type, then you search for recipes and use the cuisine data field as the constraint.
Thanks @boston85719 - I guess the primary issue comes not when loading all cuisines (that runs pretty fast), but more so when loading complex recipe statistics based on the ingredients contained in the recipe - any ideas for how to speed that up?
Or any thoughts on how to generate some summary statistics using an approach similar to functions/methods on the recipe object?
Hey there! I’d love to help out. Let’s dive into a few concepts that might boost your understanding and help you optimize things.
First off, the way you’ve structured your database would actually work well in a NoSQL database (like Firebase). NoSQL databases are great for hierarchical data storage, allowing for more flexibility and handling large volumes of data efficiently. However, Bubble uses a SQL (relational) database, which is a bit different.
Given that Bubble uses a relational database, it’s important to structure your data to make the most of it. Here’s a suggestion to improve your current setup:
Cuisines Table
ID
Name
Recipes Table
ID
Name
Cuisine_ID (Foreign Key)
Total_Calories
Total_Protein
Total_Carbs
Total_Fat
Ingredients Table
ID
Name
Protein
Carbs
Fat
Calories .
Since ingredients and recipes have a many-to-many relationship, we should use an intermediate table to handle this efficiently.
Intermediate Table for Recipes and Ingredients
To handle the many-to-many relationship more efficiently, consider having an intermediate table:
Recipes_Ingredients Table
Recipe_ID (Foreign Key)
Ingredient_ID (Foreign Key)
This table links recipes to ingredients, allowing each recipe to have multiple ingredients and each ingredient to be part of multiple recipes.
Example Relationships
A Cuisine can have multiple Recipes.
A Recipe can have multiple Ingredients.
An Ingredient can be part of multiple Recipes.
Performance Tips - Bonus
Preloading Data in Invisible Elements: Use invisible elements to preload data. For example, create an invisible repeating group to load data beforehand. This way, when you need to display it, it’s already loaded.
Summary Metrics Calculation: Instead of calculating things like total calories on the fly, calculate and store these metrics when the data is updated. This way, you just fetch pre-calculated values, which is much faster.
For example, in your Recipes table, you could have columns like:
Total_Calories
Total_Protein
Total_Carbs
Total_Fat
Whenever you add, update, or remove an ingredient, you recalculate these values and store them in the Recipes table. This avoids having to calculate them every time you load a recipe, speeding up your load times significantly.
I hope this helps! If you have any more questions or need further examples, feel free to reach out. We’re all in this together, and I’m here to help!
A better way is to keep track of statistics in another data type and update this information every now and then (maybe every night) using a scheduled backend workflow.
For example, you can have a new data type called Recipe Stat and fields might be:
Recipe
Number of Ingredients
Total calories
Any other stat you want to keep track of.
You can even add this as a field to the Recipe data type.
And when you are displaying any statistic, just refer to this field.
Doesn’t seem to be a reason to have two data types there…why not just store the Nutritional value on the ingredient datatype itself?
You could create a data type that might be called ‘ingredient details’ which would be with a data field for Recipe, another for Ingredient, so that you can do various approaches with searches and constraints.
So if you want to show a summary of a recipe, you will be able to Do a search for Ingredient Details and constrain by the Recipe, so that you will end up with a list of all ingredients that are in the Recipe…but the problem there is that you do not have the data for the Ingredient nutritional value associated with the quantities used in the Recipe.
So, one thing you need to do is add to the Ingredient Details data type are fields for Quantity and unit of measure for the Ingredients that are used in the Recipe. You will need to make sure that when uploaded the nutritional values of the Ingredients that you always use the same unit of measure for the recipe information as what you should store on the nutritional value, since the nutritional value details will need a unit of measure to state how many protein or carbs etc. there is per the unit of measure.
I’ve been working with a startup for 18 months as a sort of Fractional CTO, that does in home chef experience. The client requested a new feature for shopping list, and as they did not heed my advice in the beginning of the project and upload recipes with various units of measures for the same ingredients, this become a much more time consuming task than otherwise necessary. So maintaining consistency with your unit of measure is important.
On the Ingredient Data type, put a Unit of Measure field (make an option set for this). Put a field for each type of Nutritional info (ie: protein, carbs) and each will be a number…so you will see the Name of Ingredient, the Unit of Measure used for the Ingredient and the numerical values for each type of nutritional value.
Then on the Ingredient Details data type, you will have the Recipe, an Ingredient and a field that will be a number type which represents the amount used for the recipe of that specific ingredient. Which might be something like Pecan Pie, Pecans, 2 - the number 2 representing two cups, and on the Pecan Ingredient data type, there would be a unit of measure of cups with the nutritional values associated with a single cup of Pecans.
Now you can easily and quickly calculate the nutritional values of the 2 cups of pecans used in the Pecan Pie recipe.
In whatever element you want to show these summaries, you will have a content type of Recipe, then have a repeating group that is set to search for Ingredient Details with constraint equal to the Recipe of the container element. In the RG, you will have a text field that will display the Ingredient Name, which would come from the ‘current cells ingredient details ingredient’…then put into the RG a text to show each of the nutritional details (protein, carbs etc). This will display as ‘current cells ingredient details Quantity multiplied by current cells ingredient details ingredient nutrition field’ (ie: the field on the ingredient data type that is a number that represents the total protein or carbs for the specified unit of measure used for that ingredient).
To calculate the total Protein of a recipe you could use JS with the toolbox plugin, use the Orchestra plugin to run actions using values from within a repeating group (if doing that, display your values of each ingredient with input elements set to number that way you can just use each of them in each cell to sum them all together - which is not slow, Bubble very quickly can perform calculations). Alternatively, you could store these total values on a data type like Recipe and when creating a recipe run a backend workflow to perform the calculations and save them, which if you do not expect to make changes often, will not consume too much WUs.
Disclaimer ChatGPT was not used to create this response.
I really thought about writing this response without using ChatGPT, but I decided to use it again to make things easier for the reader. Although I can read and understand English clearly without translation tools, I still struggle to produce well-structured texts. The topic we’re discussing is already complex for our colleague, considering how they structured their database and their knowledge of databases. Writing short explanations about intermediate tables and different types of relationships (one-to-many, many-to-many) would require even more effort from the reader if the text lacks cohesion. Obviously, the chances of learning these concepts here might not be high, but I’m presenting them in case they want to study and understand them better.
For your information, I spent an hour writing this post. Every detail was thought out by me, including the introduction and discussions about relational and NoSQL databases. Even though Bubble.io isn’t strictly a relational database, the user created a nested database structure, which is typical of NoSQL databases. Maybe this information can help them understand the differences between database types and better structure their database in the future.
Regarding the database schema, I created the image on dbdesigner.net. It wasn’t generated by AI; the schema was designed by me. After explaining the database structure, I also gave an extra tip about preloading data to speed things up. Like you, I mentioned that there’s no need to calculate data every time; we can store this information. I also gave an example without using an extra table.
In summary, I spent over an hour writing this post. I believe the intention of the forum is to contribute and help each other. I’ve been using Bubble for over three years and am just starting to contribute to the forum now. I must say this is my first contribution, but I hope to see more technical comments rather than indirect remarks in the future.
I really believe you want to help. The tip about standardizing measures is invaluable; some people might overlook it. I’ve worked in the food service industry for three years, so I could discuss many nuances as well. However, I didn’t want to delve into that here.
i had actually presumed that the Bubble DB was of a noSQL type due to the nested structure etc (I should have read the docs…) - do you know how they handle these nested fields behind the scenes?
I’m also trying to get my head around when you would ever need to use nested fields when just adding the parents id to the child results in such faster performance.
This is not the case. When you have a repeating group that is displaying a data type of say Type A which has a data field that is related to data type of Type B, if you never reference within the repeating group a field value from Type B (ie: current cells Type As Type B name field), all Bubble is doing is returning from the server the unique ID of Type B, so that performance should be exactly the same as if you only used a text field to store the unique ID of Type B on a data field for Type A.
BUT, the moment you reference inside of the repeating group any field from Type B, then Bubble will fetch and load all of the data fields from Type B for each Type A being retrieved via the search. This is where there are performance issues, which is why it is recommended that if you need to only reference a single field from Type B inside of the RG that displays Type A to have a field that stores just that one value as text.
Anytime that you do not want to perform a Do a Search with constraints of ‘this things parents ID’ so that you can access the data fields of the related data type.
They are stored and returned as a Unique ID, until you reference them via the main data type as explained above in regards to showing results in a repeating group.
