I am developing a test/exam solution where We have around 100K questions (MCQ / Objective Type). Each question belongs to a topic and each topic belongs to a subject.
Students practice these questions as part of weekly/monthly tests, but students can attempt questions just as practice workout and without any test.
- options columns
- was_correct //if user got this question right
- Need to support around 100K questions
- could have 100K users
- question_attempt could go as big as 100K*100K or more.
Users subscribe and use the app over the period of 2 to 3 years during which period they take tests, practice questions as workouts etc. All of the students are competing for a single exam.
Use cases I need to support
- Calculate question difficulty score based on success/failed attempts
- Get list of unattempted questions for users to practice.
- Each time user click next get an unattempted question for given topic, or subject.
- Calculate score for user's strength/weakness for subject and topics based on user's question attempt success/fail
- Get list of questions which user attempted last week but failed and let user practice them
- Determine user's overall rank among other users based on his attempts
Major problem I see is : Finding unattempted questions, As it would need join between question and question_attempt which is very huge and querying last weeks failed question for user
For calculating topic/subject performance, i could do jobs which runs at midnight and could get away with lower performance.
Currently we use Mysql