Forum Academy Marketplace Showcase Pricing Features

Efficient database set up (best practice questions)

Hi all!

I’m using for some tips on setting up the database efficiently. As I understand, following some sensible rules when setting up the database can shave time off loading and general responsiveness, so I’d like to adhere to a good set of practices early on. Here are a few questions.

Feel free to add more questions that are relevant if you want

Saving data to sub-data types, instead of in main data type
I hope I’m not getting the “database dictionary” too mixed up here and misusing terms. But here’s an example: You have a user saved (let’s call him Petter). He has a lot of settings saved, such as email, nationality, etc. I make a decision to save a large set of extra settings to Petter, and because there can be a lot of them, from a “human reading” perspective, it makes sense to set them up in an individual data type. Such settings can be “Has-finished-user-profile” (yes/no), “has-read-terms-and-conditions”, “has-received-welcome-mail”… etc. Over time it can be hundreds of different settings that makes the application more user friendly.

So, to sum up the dilemma: it seems easier for me to set up data types that are connected to the user, instead of saving hundreds of fields on the user itself. From a computer perspective, however, it may be quicker to save it all to the user. What is the recommended best practice here, to maintain an efficient database and minimize loading times? Keep in mind that I’d like to discuss choices that affect loading time and responsiveness in an observable manner (so if one choice over the other shaves a theoretical 1 millonth of a second of loading, it may still make sense to choose the “slower” one, if you get my point.) Let’s assume that this system would have thousands of users in the database, to allow for growth,

Illustration:
Data type: user
Data type: user_finished_pages_booleans
user_finished_pages_booleans data fields:
HasFinishedProfile: Yes/No
HasReadTermsAndConditions: Yes/No

Thoughts?

I don’t have inside information, but I assume that if you have a lot of fields, and most of them are only occasionally used, you may benefit from putting the extra fields in a separate table. Every time you search or display a list of users, all the users must be loaded. If you can make each record 1/10 the size, that load process will be faster. (It’s possible Bubble only sends as many records as will be displayed on the current screen. That would be smart – I bet they do that. But the point still holds, if less so.)

If you’re displaying a list of users, you will be displaying just a few fields. I don’t know if Bubble is smart enough to send only those fields to the user. If not – if it sends the whole record, even though it’s not displaying the whole record, then making the record much smaller will speed the download, especially if you have many records.

Maybe someone who knows how Bubble does this can chime in.

2 Likes

I recall Emmanuel mentioning this point a while ago, not sure which topic it was in.

@emmanuel or @josh would be able to answer this question.

I am interested to see where this discussion goes. Very relevant.

Thanks for the input so far! It would be interesting to get a reply from someone with inside information indeed, as I’m sure it can help all of us create more efficient applications – and save resources for the Bubble servers too.

I am asking myself the exact same thing as well. Saving about 25 values on one type, and asking myself if this is the most efficient way to go.

Futhermore, I have four different Repeating Groups which filter down everytime a yes/no is changed (12 things in group 1, 6 things left in group 2 etc.).

I was wondering whether it would be more efficient to leave everything in the same list and just filter on the repeating group, using the same data source, or if it´s more efficient to move a thing from list to list

My logic would be that every list per Repeating Group becomes smaller and it´s easier to search through for the database, but not really sure, would be great to get some cnfirmation.

Thanks!

I would say that even if you had a contraint that only users with a yes/no field being searched bubble would still have to search the entire list to return a result. So i would assume they have an indexing system of sorts to assist the searching. But even an index gets slower as it grows.

My thoughts though point to not what bubble can do about indexing data but what we could do to index our data. No logic is going to be one site fits all and be the most efficient for you.

Doing things like a putting products into category based data set then calling that set via dropdown is pretty standard.

But things like a times shown count and background workflows with move data >< counter to a priority 1, 2 or 3 dataset and show a “show more” button in searches that adds the less shown database as you progress could work too.

Ultimately i dont think there is too much need to go much more than constraints for most of us but its cool too see just how far we can push an idea.