I am working on a Survey Assessment platform and the database has several tables to keep them from getting unnecessarily large:
Surveys - this is the aggregate results from a survey - Question Group, Number Assessors, etc.
Survey Summary- partially aggregated results from the survey to rull up to question groups
Survey Detail - this is the granular results from a survey that get rolled up
Assessors - this is the list of people assessing the individual including the Subject individual
Organizations - this is a list of organizations where the subject works and the org structure
Questions - these are the questions and groupings of questions in the survey
I am building the dashboard with a repeating group of Data Cards and I want the Card to show:
Subject: Assessor (table) with Assessor Type Self
SBU: Business Unit for the Subject - Organizations (table)
Title: Job Title for the Subject - Assessor (table)
Survey Dates: Start/End - Surveys (table)
Survey Status: Complete, In Process - Surveys (table)
Assessors: Total number of Assessors - Surveys (table)
Overall Assessment Score: Surveys (table)
Overall Assessment Score by Questions Group: Surveys (table)
The relational database design does not really lend itself to this task (see image) I think I have to put each data column I want into the final Survey Aggregate table and then run backend processes to update that. Is there a way to bring data like Job Title into the Surveys table without having to add it to the table? I should be able to connect a unique Assessor_id from Surveys to Assessors and pull in the Job and Org data. Not sure how to connect that in a repeating group card.
