Hi,
i am trying to use airtable to store the results of multiple surveys, all that have slightly different questions. I also want to be able to generate reports and summaries of the responses to these surveys.
The schema I came up with so far (the core of it) is:
Client = (id, contact_email, name)
Survey = (id, client, name, start_date, end_date)
Category = (id, name, parent : Category | null)
Question = (text, categories: Category)
Response = (survey : Survey, email, question : Question, response)
response could be agree/disagree/strongly agree/strongly disagree/neutral. (5 point likert)
I also add another field to response called numeric_response that converts response into a number which can be used for averages, histograms, etc.
This is great for flexibility, matches what I know of SQL database design, minimizes the number of tables etc. It also makes sure that questions are part of the records instead of being metadata hidden in the column names (so I can later track and have a system for creating and managing the questions). The big downside is the number of records in the response table (which also isn’t an issue except that Airtable limits the number of records in a base. The amount of data in that table is very small since it’s just some ids and some enums).
Using rollups, I’m able to compute overall scores for the survey on the survey object. That’s great. Now, I also want to compute the following table:
Summary =(survey: Survey, question: Question, avg_response, percent_agree, percent_disagree)
And a very similar one: (survey: Survey, category: Category, avg_response, percent_agree, percent_disagree)
I’m having a challenge creating this kind of table. The only thing Airtable lets me do is create a view that lets me look at one summary statistic per column. I can’t use that summary statistic anywhere, even within Airtable’s own apps and page designs.
So … how do I design my base to support creating these kinds of summaries? I know the kinds of SQL queries I’d run on my response table (just group by something and then take aggregations of columns) but this doesn’t seem possible in airtable.
