I’m building a question bank as there a couple of tricky details.
The Basic Layout of the Things/ Data Types I have created are:
1) Users
2) Questions
Fields
2a) Question Text (the text of question)
2b) Question Answer (the text of the answer)
3) User-Question(junction table)
Fields
3a) List of Questions [for that User] (a list of Thing 2 Questions)
3b) Question status [for that user] (data type - Option set)
links to Option Set of Question Status; options: Unanswered / Incorrect / Correct/
The app works by taking users through a question bank. Thing 2) Questions are bank of questions and answers which I am creating. However, they will continue to be updated even after the app is launched.
Here’s the tricky part:
When a new user signs up, I need field 3a) List of Questions (for user) to autopopulate with all the items in 2) Questions -
What’s more, for an existing user, when I update / create a new 2) Question (in the question bank), I need their 3a) List of questions
Is this possible?
If not, is there another database design that could solve this issue?
(My thinking was to put 2 Questions as list under 3a List of questions. But maybe there is a better way)
When the user signs up, you can use a workflow to create/populate new questions however you see fit. Same thing with creating a new question – set a workflow to create whatever entries you need.
You can use a junction table if you want, but in Bubble it’s not required since Bubble has a “list” field that accomplishes the same thing. So that gives you flexibility in how you set things up – but more options also mean more decisions on how to connect and structure things. In terms of the “optimal” way – it really depends on the nature of your app, how much data is in there, and how you will search and display. This is a great discussion on various approaches: Alternative approach to the Bubble’s recent tutorials for list of things
If your app’s data set isn’t expected to get big, don’t sweat it too much, most ways will work fine. It’s just if you expect to scale to large numbers that structure comes into play.
Things I think about are…
Keep “like” data together and split out (ie “normalize”) data when reasonable
Keep entries data light (normalization helps here)
Know who should be the parent and who the child
Aim for simplicity and elegance, go complex only as needed
From a quick look at your situation, if each user shares the same questions, what comes to mind as a structure is below:
User datatype
Question datatype (assuming users are submitting unique answers to shared questions)
Answer datatype (assuming each answer is unique to each user)
The Answer datatype has two custom fields: Question (connects the Answer to the right question) and User (connects the answer to the user who wrote it).
However if each question is unique to each user, then you’d have a Question & Answer datatype that contains both the question and the answer, with a custom field connecting it to the User.
I think this would keep the database the “lightest” – but as mentioned, you’ll also want to think about what you are doing with the data in search and display, as that sometimes calls for other approaches.
. I’ll give it a try with the workflows and read the post you suggested.