Database design for tracking student exam performance at topic level with possibly 10 billion rows

Question

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.

Tables

subject

• id
• name

Topic

• id
• name
• subject_id

Question

• id
• text
• options columns
• correct_option
• difficulty

topic_questions

• topic_id
• question_id

question_attempts

• user_id
• question_id
• was_correct //if user got this question right
• attempt_date

Data size

• 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

Answer 1

If each user attempt each question only once, we will have 100K * 100K rows for attempts

I think it's unlikely every student would answer 100,000 questions, let alone even one student to do so. That's a lot of questions that would take someone 70 continuous days of testing, if each question was simple and took only 1 minute to read, analyze, and answer. Nonetheless, it doesn't change the answers to your questions anyway, so let's assume that's an accurate prediction of the future size of data.

Will mysql handle such large number of rows and still give decent performance for reads

Yes, size of data at rest doesn't change the read performance of the queries against that data, as long as the database is architected properly with appropriate indexes. This assumes you're not reading the entire table at one time, which is a size of data operation, and would be physically bound by the hardware of your server and irrelevant of whichever database system was used. (In that case, no different than moving a large file from one harddisk to another, for example.)

You'll find the more difficult problem is managing the database, from a schema changes and backups perspective. Again, this would be constant for any database system, and they each have their own features and solutions to alleviate this. Partitioning is one feature that can help with the management side of things.

Is mysql even ideal solution to store question attempt records ?

Sure. No different than any other modern relational database system. Whether a database system is an ideal choice for you, should more so be based on the features it offers, the price tag, the size of the community using it, and the support and documentation available for it. All relational database management systems are similar enough that the size of data shouldn't affect which one you choose.

If not mysql thn what ?

See above. Yes, it can be MySQL.

Optimal table / database design for this problem

More information is needed to answer this. You would need to provide more concrete examples of what the tables look like. But the table design itself will essentially mimic what makes logical sense as far as your objects, and not be anything too out of the ordinary to specifically cater for performance. Proper indexing will be key. But we'd also need some actual example queries provided as well, to help provide index examples.

And then this would all still be based on theory, until you actually implemented them in practice, tested the queries, ran with EXPLAIN ANALYZE enabled so we could observe their query plans, and then revise accordingly. Performance tuning is a multi-revision and generally ongoing process that needs to be based on the actuality of what the database engine is doing.

Answer 2

Calculate question difficulty score based on success/failed attempts

SELECT  question_id,
        ROUND(100 * SUM(was_correct) / COUNT(*)) AS success_percent
    FROM question_attempts
    GROUP BY question_id;

Get list of unattempted questions for users to practice.

SELECT  DISTINCT q.question_id
    FROM question AS q
    WHERE NOT EXISTS ( SELECT 1 FROM question_attempts AS qa
            WHERE qa.question_id = q.question_id );
    

Each time user click next get an unattempted question for given topic, or subject.

SELECT  question_id
    FROM  question AS q
    JOIN  topic_questions AS tq   ON tq.question_id = q.id
    JOIN  topic AS t  ON t.id = tq.topic_id
    LEFT JOIN  question_attempts AS qa USING(question_id)
    WHERE t.name = 'birds'
      AND qa.question_id IS NULL
    LIMIT 1;
    -- similarly for subject, etc.

Calculate score for user's strength/weakness for subject and topics based on user's question attempt success/fail

SELECT  question_id,
        ROUND(100 * SUM(was_correct) / COUNT(*)) AS success_percent
    FROM question_attempts
    GROUP BY question_id;
    ORDER BY success_percent DESC;
    -- or something like that

Get list of questions which user attempted last week but failed and let user practice them

    ...
    WHERE  NOT qa.was_correct
      AND  qa.attempt_date >= NOW() - INTERVAL 7 day

Determine user's overall rank among other users based on his attempts

-- This is more complex; get used to the other queries before attempting this one.