Database design

App Building
1

Hi. I’m building an app to help my company track our monthly electricity consumption. We have historical monthly data since 2018.

I want the user to be able to add in data for each year since 2018 and every new year moving forward.

At the end of the monthly data for that year, I want it to be automatically summed up as the year’s total.

This is what I am experimenting with:

  1. The columns will be the months (Jan - Dec)

  2. The rows will be the years

This way, I can add a new row for every new year.

I am using the Retool database to store the data.

Is this the best way to structure the database?

If so, I am having trouble with the ‘Total’ column at the end of each row:

a) do it code it in the database itself to sum up all values across the columns in each row?

or

b) do it in the Table and then write it back to the database? Is so, how?

Appreciate the guidance.

Thank you.

2

This actually sounds more of a usecase for excel or google sheets than a retool app?

But if you want to do it in retool I would calculate the total Sum when fetching the data from the database. The dataset is so small that you don't have to write it back to the table.

3

Hi, thank you for the reply.

I do want to expand the data entry to include a lot more data points.

I've worked out how to sum up the total. I added up all the CurrentSourceRow data as per below:

image
image1325×530 59.6 KB