Survey type app for daily checkins by a group, how to structure DB

Hi, I am trying to figure out a way to make a daily checkin type app that has a few question users will input a number for. I then want to be able to show a chart for each question that shows the totals for each question.
I’d like the inputs to all be done on one screen, but would like to avoid having to make a collection for each question if possible (this is where I’m stuck mostly).
The users would come back each day and answer the same questions, so that on the chart we could see trends

ie: 5 users would answer these each day
Date: Jan 16, 2021 (likely this would be set automatically)
Question 1: How many apples did you pick today? A: 100
Question 2: How many were good? A: 80
Questions 3: How many were red? A: 40
Question 4: How many were green? A: 60

So the chart would then show the total of all 5 peoples answer. (Would also likely make a seperate view where you could select a user and see chart of only their answers).

So do I need make a Questions collection, and then a “Daily Responses” table?

Having a hard time with this one for some reason.


Having 2 collections will make this easier to implement.

Collection 1: Questions
Collection 2: Responses, Date Responded, Value (i.e. 60 Green Apple)

Relationship 1 Question: Many Responses

When you show the summary, you can rollup the total for each question group by dates.

This might get easier if you use Airtable for collections because the rollup and calculations are taken care automatically.

Thank bhanu,

Thanks for that. I have not tried to tie in Airtable yet, but if it helps with the math that might make sense.