How to break large tables into several smaller ones

[pgillard]

Hello

I have a very simple app used for doing a self-evaluation. It asks a number of questions and scores them.

Each question follows the same format.

There are multiple group/sections e.g. Delivery

Each section has a number of question pairs with rating (low, medium, high) and comment. E.g. Delivery Speed Score, Delivery Speed Comment. Delivery Quality Score, Delivery Quality Comment.

Each score becomes a number and each section is averaged.

Overall result is scored and averaged.

In terms of behaviour I want the view for creating/editing the evaluation (rate and comment). But a different view for presenting it which is just the section averages.

The way I've done this is to have one giant table (over 150 rows) which has a just so many columns and formulas that it is quite frankly unmanageable and exhausting! Plus it far too easy to create bugs and detect them.

Is there a better way where I can modularize this where it is easier to maintain and is more consistent?

[ali-hussain]

We have something similar to this if I'm understanding what you're saying correctly.

First we have a table of all questions and a table of all categories. 

Next we have three tables, a table of assessments, a table of each category for each assessment, and a table of all responses for each question in an assessment. Any time someone is doing an assessment we create new entries in these three tables. Basically an automation that runs any time a value is added to the assessments table.

People fill in their assessments. You add virtual columns in the assessment category table for the analytics you want to do.

One additional advantage you get is that you can add or remove questions and don't have to do a column regenerate. The changes just take effect on a new assessment. 

One disadvantage is you have to do some more standardization on your questions. The equations you use will apply for all questions and you can't do bespoke. We didn't do multiple data types, but technically you can, but that would also add a large amount of complexity. You also lose the ability to use valid_if. 

[pgillard]

Thank you very much for the advice. It sounds like this would be a better solution for my use case.

Would you have any references I can use to understand and learn how to apply this to my app?

Thanks very much in advance

[ali-hussain]

Unfortunately not that I'm aware of. I had to figure out most of it. 

The tables are pretty straight forward with Refs to the other layers. The filters and selects you'll have to create according to your own specifics. Automation, you'll have to use the API and a template to create the assessment category table entries and the questionnaire entries. 

One thing we did with our workflow is you don't create new assessments, you snapshot your assessment. So you always have one assessment ready. You press the snapshot button and a new one gets created. Then the assessment screen is always pointing to the latest version.

[pgillard]

I'll be honest, at this point I would rather ditch the tool than find ways to cleverly exploit design loopholes and write some proper code which allows for scalable designs.

[ali-hussain]

That you'll have to decide for yourself. Granted I don't have too much web dev especially front end experience but I do think overall I saved time, and prototyping new ideas has been faster. In terms of the structure I described. If you have a large number of questions, I think it makes more sense anyway than having a constantly changing schema.