Exercises app DB design

Get Help Database
1

I want to make a simple exercise app where exercises would be auto-corrected after submitting them. Let’s say we have the following exercise:

Captura de pantalla 2023-11-10 a las 14.18.02
Captura de pantalla 2023-11-10 a las 14.18.02708×1128 38.3 KB

Once the user will answer both questions, he will click on the “SUMBIT” button and the exercise will be auto-corrected.

As you can see, the exercise is made out of 2 questions which have three options each.

My data structure so far is as follows:

Exercise Collection:

  • Exercise number
  • Questions (All questions related to the exercise)

Question collection:

  • Question Number
  • Question Text
  • Exercise where the question belongs to
  • Options (All options related to the question)

Options Collection:

  • Options Number
  • Options Text
  • Question where the option belongs to.
  • Exercise where the option belongs to

My initial idea was to create 2 more collections to store the temporary user’s answers before clicking on the “SUBMIT” button, in that way, I should be able to “highlight” the selected option, and once the “SUBMIT” button is clicked, I would simply copy all the information from the Temporary collections to the “Submissions” collection and delete all data from these 2 collections. Here you can see the 2 temporary collections:

Temporary Exercise Collection:

  • Id of the User doing the exercise
  • Id of the exercise he is doing

Temporary Options Collection:

  • Id of the User selecting the option.
  • Id of the selected option
  • Id of the exercise the selected option belongs to
  • Id of the question the selected option belongs to

The problem with this approach is that it has many “ifs” I’m not able to do in Adalo:

  1. Once the user selects an option for a question, only if it is the first selected option within the exercise, we have to create both, a new entry for the Temporary Exercise Collection and a new entry for the Temporary Options Collection.
  2. For all the upcoming selected options we have to check if the user already has answered the question. If he has, we will have to delete the previous Temporary Options Collection entry and add a new one. If he hasn’t we will have to create a new Temporary Options Collection entry.
  3. I have to “highlight” the selected option based on the Temporary Options Collection entry value.

Since I’m pretty new in Adalo, my questions are the following:

  1. Is this the best approach to achieve what I just described?
  2. Is this the optimum DB design or is there a way to achieve the same results in a more simple way?
  3. How do I handle all the “IFs” exposed on the previous paragraph?
  4. How do I set a different color for the selected option?

I would really appreciate any kind of help!