Forum Academy Marketplace Showcase Pricing Features

Joining Tables into One Repeating Group

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.

@chris43

Data card
Title (text)
Assessor (user)
Organization (organization)
Overall score (number)
Score by Group (number)

User
First name (text)
Last name (text)
Email (email)

Organization
Title (text)
Assessors (users)

Survey
Date start (date)
Date end (date)
Status (options set: complete, in-progress)
Questions (questions)
Score
Respondent - Assessor (user)

Question
Title (text)
Group type (options set> Type1, Type2, Type3)

As above. Unless you are getting this data from elsewhere, then don’t use foreign keys.

Starting to realize that foreign keys are a persistent part of my headaches with Bubble.io.

It takes some unlearning if you are used to a relational-style database my mySQL.

On the positive side…no more left inner-outer righthand joins :grinning:

LOL - "Always look on the bright side of life… " We will reconfigure the database tables to contain lists of things wherever we can. some if it is a little tricky - have our work cut out for ourselves. Thanks @NigelG and @cmarchan

1 Like