I’m working on an app that shows locations. The location page shows “location amenities,” “things to do,” “quick tips,” and photos.
Location amenities are things like restrooms, stroller friendly, locally owned, etc.
Things to do differ by location but can be things like try the cheese pizza, order the garlic knots, visit the gift shop, etc.
Quick tips are things like bring sunscreen, park across the street, get there early, etc.
This is my current database structure (simplified), and I’m wondering if I can make it more efficient or if this is the best way to save on workload units.
Data Type: Location
Data fields:
name - text
address - text
location details - Location Details Data Type
Data Type: Location Details
Data fields:
location - Location Data Type
location amenities - Location Amenities Data Type
Data Type: Location Amenities
Data fields:
location - Location Data Type
location amenities - List of Amenities Option Set
Data Type: Location Things To Do
Data fields:
location - Location Data Type
things to do text - text
Data Type: Location Quick Tips
Data fields:
location - Location Data Type
quick tip text - text
Data Type: Location Images
Data fields:
location - Location Data Type
location image - image
When a user searches for and selects a location, the workflow animates the location page and does a search for the location, and sets it to a state on the page called “current location.”
On the location page, I have multiple repeating groups that all do a search using the index’s state current location as the condition inside the do a search.
I show all location amenities for the location, but only the first 3 things to do, quick tips, and location images.
I hope users search for multiple locations per session, so thinking ahead, is this the most efficient way to structure my database and search for each data type on the location page?
The best way to save on workload units would be to implement a Hybrid Data Structuring approach. My plugin Data Jedi enables that easily, which when implemented into an app can reduce WU costs for search by 95% and improve content download speeds by 30-50% on average. It is an advanced technique that enables immense workload unit savings.
If however, you want to use a legacy data structuring approach, do some of the following
Location Amenities should be an option set, not a data type, and so too should location quick tips. This will save WUs and be faster than having them as custom data type
Instead of having the user select a location and then searching for the location again, just set the state on the page called ‘current location’ using the dynamic expression of ‘current cells location’ from your repeating group displaying search results
Get rid of location details and just add the same fields to the location data type because it will save workload units on having to search for them and create and modify them, so instead of having two data types that are basically the same thing (ie: location details is the location)
Depending on the number of images the location will have, you may just make it a list field on the location data type because unless you are storing other details besides the image and location data type on the data type of location images, such as image meta data, image alt text etc., there is no real reason to have the separate data type unless the list is maybe larger than 20 images or so, and even in that case, you may just change the field from image and make it text and just save the uploaded images URL.
You’ve structured the data in a way that resembles traditional relational database design, which isn’t ideal for Bubble. Bubble doesn’t handle joins well, so overly granular structures become inefficient quickly.
Recommended Data Structure
1. Location
Purpose: The main object used in searches and filters.
Fields: Include anything used in listings, searches, or filter operations.
Link: Add a field to reference the Location Detail.
2. Location Detail
Purpose: Stores all additional information not required for search/filter.
Fields: Rich text, long descriptions, embedded content, internal notes, etc.
Linked From: Location.
3. File (Only if using external storage)
Purpose: Metadata for externally stored files (e.g., S3).
Fields: File name, size, URL, type.
Note: If using Bubble’s built-in storage, just use the file field type directly in the relevant data type.
It’s not the most WU efficient, but that should be okay. The best option for your app depends on what you’re looking for most. Your database design is a little overkill because generally you only need it to be a separate data type if you need to store metadata about specific things.
For example, in the location things to do, if you want to store reviews for each thing to do, that would need to be a separate data type. Fundamentally, all of your different types just have one field, which implies they can all just be a list of primitives on the main location data type.
I had no idea that Bubble didn’t handle joins well. That’s good to know!
@mitchbaylis - My goal is to offload bulky data to secondary data types, which is why my database is so granular. I was taught that adding lists to a data type would slow down the app and make it harder for Bubble to search the database.
For example, if a location can have 25 amenities, should I put this in the Location data type as a list or should I create a new data type called location amenities and add two data fields
location - Location (Data Type)
location amenities - list of amenities (Option Set)
@georgecollier - I plan to allow users to add things to do and quick tips for each location which is why they are both data types. Whereas the location amenities is an option set so users can select amenities but not add to the list.
Ideally, in the long term, I would like to have hundreds of things to do, quick tips, images, and reviews for each location. In this instance, would my database design work well, or is it still overkill?
I’ve built a couple of apps that have gotten around a thousand users each, so I’d like to learn how to design a database for the possibility of scaling past that point.
The concept of not putting a field as a list of ‘things’ (ie: related to a custom data type) as being slower is when we think about the difference between the speed to fetch via a “Do a Search for” versus the ‘current things field list of related things’ and the speed difference is such that if you have a field with 50 or more things in the list, it is most often faster to just perform “Do a Search for”, but if the list is less than 50 things, it is usually faster to just reference ‘current things field list of related things’.
But that doesn’t really matter in your database structure as an Amenity for a Location should be an Option set and not a database custom data type. This is because Amenities are values that should be available for Location owners to choose from and not to create, as those Amenities are your filters your users will use to filter locations by Amenities to choose the right Location for them.
And since Amenities should be an Option Set, if you have a field on location called “Amenities List” which is related to the list of Option Sets the Location has chosen as having, it is not searched as Option Sets are already loaded on the page, and so it doesn’t matter if you have 25, 50 or 1,000 Options on that list field, the performance would be the same as just called “Get Options”.
I highly recommend following the advice to make Amenities an Option set and just use a field on the Location that is a list of Amenities that it has. My advice comes not just from many years of building database for Bubble apps, but years as a Hostel Owner working with multiple OTAs and so having knowledge of how those large players (Expedia, Booking.com, Agoda etc) have their databases structured for such data.
No, on the one data type called Location put a field that is a list of Amenities that is an Option Set. No matter where it is in your app that you are going to display a list of Locations, you will ALSO NEED the amenities list, be on the search results (need for filtering), on the location card (show top amenities) or on the Location Page (show all details).
Again, I recommend checking out my Plugin. What it does is a lot and one of the things it does is solves problems and makes things possible in Bubble that are not with a legacy data structuring approach. One of those things applicable to your use case is the creation of quick tips and things to do for each location. Those are similar to Option Sets in the sense that they should be cheap and easy to load and always be available as they would be used for filtering (filter by things to do). The problem though is Option Sets in Bubble can not be created or modified by a User.
But when using a Hybrid Data Structuring approach and the Data Jedi plugin, you can essentially create an system by which users are able to create, modify and delete what would act just like Option Sets in that they are loaded on the page (for the cost of individual data request).
Then you should really consider speaking to me about how the plugin makes it possible. The idea of scaling in my mind is making sure that your app is able to perform well, be maintained easily, new features added in easily, and be affordable to operate. My plugin and a Hybrid Data Structuring approach does just that.
But of course, my idea of scaling is more focused on Bootstrapping and having an accurate idea of my business expenses (like how much does it cost to operate the app) and how much runway do I have based on the expenses of the business and my ‘savings/investment’ allotted to the business.
A lot of times people would argue that if your business is good, it can afford anything to operate, or that just 10% of revenues is an okay expense to bear to operate, but for me, I’d rather not overpay for something if I don’t have to. Like, I’m not going to pay my landlord 10x my current rent if my efforts result in 10x more people coming through my doors. I’m really only willing to pay my landlord more when I have grown so much that I need to take over the unit next door.
