How would you organize the storage of responses to the form in the database?

Question

MySQL

Good day everyone!
I'm making a system for a school. The system has tests. Those. forms with questions and answers.

Now distributed by tables:
1. tests - a table with data about the test, time limit, etc.;
2. questions - a table with questions associated with the tests table;
3. answers - a table with answer options associated with the questions table;

The question now is how to store student responses. There were two thoughts:

Initially, I thought of creating a users_answers table with the answers of each user to each question, linked to the users, questions and answers table. But what happens if there are, say, 20 questions in the test, in a group of 20 students, and there are 5 such tests for the entire course. Let's assume that the school delivers 5 courses per year, so there will be 10,000 entries per year. What is the best option with a sample of 10 thousand records for extracting the answers of a particular student?

I spied the second option in the moodle database. They, as I understand it, do not save every answer, but every passing of the test. And the responses are stored in json.

Which option, in your opinion, is more optimal? How would you organize the storage of responses?

Answer 1

| qa (question
| st (student.
+ answer (text, fk_qa_id, fk_st_id)
use two foreign keys, do not forget to write down that when deleting a question or a student, delete dependent ones from those tables.
And those 10 thousand records are nothing at all. when there will be a couple of hundred thousand of these tests, a hundred students, and a couple of tens of millions of answers - then it will be possible to think about complicating