I'm currently building an 'online examination' type of web app using Ruby on Rails and PostgreSQL (or MySQL), which is going to be part of a bigger application once pushed to production. Since I just recently got into RD design I'd appreciate some feedback on the following design.
App Specifications
- There are 1000+ different questions. Each question can have 2-5 different options, only 1 option is correct.
- The user is presented with 10 questions at a time.
- If a user answers a question correctly 2 times, the question will not be shown to him again (let's call those questions now completed).
- If a user answers a question incorrectly then he needs to answer the question 2 MORE times correctly.
- The user passes the exam when there are no more questions left to show.
My current database schema is:
user
-user_id
-name etc
question
-question_id
-text
-correct_id (or option_id, stores the correct option id)
option
-option_id
-text
-question_id
user_answer
-user_id
-question_id
-is_correct
So in order to create a '10 question test' we could:
create a list of every completed question querying the user_answer table
create a list of possible questions (all questions - completed questions)
finally serve the user with 10 random questions from the last list.
Is there a better approach to represent "the remaining times a question needs to be answered" inside the database? Please feel free to suggest even a completely different design.
What concerns me is that for each single user there would be more than 2x1000 user_answer rows. This quickly becomes a huge number, so I guess there must be a better way.
Would using a json array be okay in this case? But as far as I understand it'd quickly become a hassle to add more questions etc.