Database design question with 156 possible data points

chris.charette241 · 2020-12-03T22:04:50.032Z

I am trying to track a series of percentage data and wondered what might be the best database design, more rows or more columns

In the image below you can see there’s 10 columns of data representing 10 years and 15 rows of individual data point categories. The data is basically static, and will be updated randomly and probably not very often. This set of data represents information that I might need to save for each property in my database, and would also need to be duplicated (default data) and unique for each user but only if the user did a review of this property and changed any of the default data points.

I’ve read various suggestions that one record per property/per user is good, but that means creating a data type with 154 fields just for the data shown, plus a field for the user name and property name.

There are two questions then, is the single row per user/per property efficient or one row per category with property name, user name and one year of data (for 10 fields) per record better?

Further, how difficult would it be to address or reference 154 fields of one record vs 15 records of 12 fields within the confines of Bubble elements?

image1192×331 9.29 KB

cmarchan · 2020-12-03T22:12:34.334Z

2nd option. You can have those records as static entries that you refer to when needed and it will be reliable and fast. (I build an online store where each product added has 4 different prices … where each of these prices is a datatype that I look up and use depending on the pricing level that a user has (meaning … which of those prices apply to that user)).

And you can update in the backend their fields directly when needed. Or … build a UI to update them in an admin page in a much easier way via autobinding.

chris.charette241 · 2020-12-04T20:02:34.134Z

Is it possible to create the record when using Autobind or does the record need to exist beforehand? I’m inclined to believe that I need the record to be created first, as in my testing records do not get created but they can be updated if I create them manually.

I’d like to reduce the total number of records and only create those that get changed.

jdiaz · 2020-12-04T20:41:00.505Z

To answer this question, I think it really depends upon your needs. If you need flexibility and may eventually add more categories, having more rows would be better versus more columns. If the categories were never going to change, more columns may be the better way to do it.

Now, if i were to do this, I think that I would take a different approach. I would create a thing in the data base that had a field for each of the categories, a field each for the user, the property, and the date (year) the record represents. By doing it this way, if you are collecting more data as time goes on, you don’t have to add columns every year, just one new record. You also avoid having to fill out 154 columns when you create a new record.

By doing it this way, you have one row of 13 fields for each year of data. This will allow you to sort/filter the data as well, like if you want to look at five years instead of ten.

cmarchan · 2020-12-04T21:14:42.698Z

Second this suggestion by @jdiaz. I would do it this same way.

I got confused when I read the opening post.

cmarchan · 2020-12-04T21:17:51.858Z

Please consider that the record needs to exist. Otherwise autobinding does not have anything to bind data into.

chris.charette241 · 2020-12-05T06:51:07.990Z

Thank you both, I think I have the idea now and I’ve implemented it and am testing it now.

For a follow up…I have couple related questions.
In this scenario, I would need to add 11 records for each property that a user reviews, one for each year and contains all the categories, user email and property ID. With 100 properties in the database, and 100 users reviewing each property that’s 110,000 records. Question 1: Is the Bubble database efficient enough to serve lookups quickly? For instance, In the image below, I have data that pulls from the database and I can watch it render the numbers and they are blank initially, and I watch the numbers fill in left to right. Some of the data is calculated, and uses the data from the source we discussed above.

Question 2: Is there a more efficient way to store just the data that changes? If there’s 154 data points stored and only a few of them are changed from a default setting, do I really want to store every bit of that data?

Question 3: Given that I need to create 11 records every time a user selects a property, what’s the quickest way to bulk create them? An 11 action workflow solution to create 11 records of 14 fields each does not seem like the right way to accomplish this. Am I missing some bulk entry methods?

image1084×352 78.4 KB

jdiaz · 2020-12-05T12:42:00.206Z

Answer to question 1: Bubble should be able to move through 110K records and load the data for you with no issues. Just make sure that your searches for data are lean so you don’t add unnecessary filters. To help the user understand that the data is loading, you can creating a loading screen to show that the system is processing the request and then show the data once it is all loaded.

Answer to questions 2: Look at the fields on the record object and identify which ones you can calculate using other fields. If it can be calculated, then remove it from the record and do the math/calculation on the front end. In terms of efficient, it really depends on your needs. Will users only provide data for the same ten year period for a property? Or is the time period moving? In my honest opinion, the method recommended will provide you the most flexibility and will still be fast.

Answer to question 3: Using a workflow to create 11 record objects in the workflow tab might not be the best approach. I think using an API workflow would be best to create the records. I would also do it as a recursive workflow so that the workflow calls itself so only one record is created but it runs 11 times giving you the records for the property. Just search the forum for recursive API workflows and you will see how its done.

chris.charette241 · 2020-12-06T04:17:52.461Z

Thank you, I’ve taken everything to heart and implemented it. I now have records that are created using the API and autobinding to update them as needed (per property/per user). For that, I think you both (jdaz and cmarchan).

I now hope to be able to use the data from the first image to recreate the data in the second and using a repeating group so that I don’t need to go through the difficulty of modifying 200+ fields.

dixontarave · 2020-12-07T18:46:51.690Z

You can update in the backend their fields directly when needed. Or … build a UI to update them in an admin page in a much easier way via autobinding

chris.charette241 · 2020-12-07T19:23:04.595Z

Thank you…I’m allowing the user to update as needed and I have an admin page as well. I am finding the only problem is that I’d like to be able to set the fields according to what’s on the page I initiate the backend workflow from but when you set up the backend workflow, there’s no way to reference your page data except to prepare in advance by setting up the Parameter fields in the backend workflow and fill them out when executing using the workflow actions from the page.

It’s a slow process since it’s not straightforward and I’m using a crapload of Input fields which each have their minor challenges.

dixontarave · 2020-12-11T15:28:29.692Z

I started making a web application - for example, an online store, a blog, or games - by the way, whatever you choose, you will come across a database. That’s what it is from a programming point of view, what are the basic concepts and what to do with them? How to understand what to look for, what are the nuances and all that? Maybe I should check out the free paper writing service to find out more? Maybe you can somehow explain it all to me? Thank you all in advance.