Database structure and calculations

telaholcomb · 2020-07-16T16:34:05.090Z

Brainstorming how our database needs to be structured and how to perform calculations on that data. I’m new to Bubble and trying to wrap my Excel brain around the right way to do things.

Let’s say we will be tracking daily temperatures. User clicks on calendar to see the change in temperature from 10 - 16 July for the past 10 years and the average change of those temps.

Database
Initially, I thought I should just have a field for date (date) and temp (number). But we should probably have additional fields to store results of the calculations. Am I correct in thinking I should really have the following fields?

City
Date
Temp
1 Day Change*
7 Day Change*

Coming from Excel we have one spreadsheet per city, one tab per change type to calculate temps over the past 10yrs. Moving this info from the spreadsheets, would I need a database for each city or will connecting the Temps data type with a City data type through the City field be the most efficient way?

Calculations
I need to perform calculations and find patterns on the temps before displaying it to the user. There will be no input from the user. Am I correct in thinking I need to use a repeating group (groups? one each for date, temp, 1 day, 7 day) on a hidden/restricted page to do those calculations (difference between first day and second day; difference between first day and 7th day) then store those results to the respective database fields (marked with an * above)?

I’ve been reading through the forum and found this thread with feedback from @NigelG:

Structuring time-series data in the database Database

I would just have … Time Series Timestamp (date) Client metric 1 metric 2 etc Then the change in metric is current metric 1 - search for time series (timestamp < current timestamp) sorted (timestamp) :firstitem

and I got stuck at:

Structuring time-series data in the database

(timestamp < current timestamp) sorted (timestamp) :firstitem

Any guidance you could provide would be greatly appreciated!

robert · 2020-07-16T16:59:24.826Z

Why couldn’t you just do these calculations on the page, rather than saving back to the database?

telaholcomb · 2020-07-16T17:19:24.641Z

Hi Robert! I guess at this point I don’t know what I don’t know. Still learning.

I wouldn’t want all calculations displayed on the users page/calendar. Only the ones that display a pattern (temp increases or decreases each year during particular range). So I would need to save data points to database daily, somewhere to calculate/check for pattern, then display on page/calendar when city is selected from calendar. No?

robert · 2020-07-16T17:57:31.538Z

Yes, you can use conditions to show/hide elements on the page.

I’ve done a lot with calculations, and have done it both with and without saving back to the database. The best advice I can give is to do them on the page (do not save back to database) if at all possible, because it will be much simpler.

telaholcomb · 2020-07-19T01:37:10.877Z

I read up on showing/hiding the elements and I think it would be great for what I’m trying to do. Thanks for the feedback, Robert!