-5

I create a MCQ website for my students (mysql). Each of my 1200 students must answer the same 300 questions (Boolean) every day.

Each question is categorized by topic.

Each student must be able to analyze his progression curve (evolution of right answers in relation to wrong answers), the questions that are often asked are wrong, the questions that are most often right, the topic with the most success or failure, as well as the frequency of response (absence, etc.).

The administrator should be able to see an overview of overall participation, as well as questions causing the most or least problems, and the same for topics.

This MCQ will be postponed every year, over a long period.

I was thinking of creating a very simple diagram.

A "Student" table, a "Topic" table, a "Question" table

A "Responses" table would include the columns: id, question_id, user_id, complete_at, is_succeful (boolean)

But given the expected number of responses, it seems to me that this will be resource intensive.

So I imagined using an array type column.

The responses table would then include: id, user_id, complet_at, is_succeful (array [question_id, question_id, ...])

I dont know if i can exploit the array for correct analitics.

Can you advise me ?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Shoooryuken
  • 11
  • 2
  • 'an array type column' - MYSQL as no such datatype perhaps you meant JSON? – P.Salmon Aug 16 '23 at 12:17
  • You can store it as a JSON and you can deserialize it where you needs on code side, it would be better than keep separate results on database. – Mert Akkanat Aug 16 '23 at 12:30
  • If you want to store arrays or json in a relational database, then I would question your decision to use a relational database in the first place. Relational databases are really good at storing data in rows and columns and relate them with each other. Storing data in json or array in a field will require you to extract that data from the field before analysis. – Shadow Aug 16 '23 at 12:46
  • Sorry, yes json. Yes, I am aware of being able to deserialize for a given user or date. However, if I can see the number of correct answers to question 2 for user 128 over the 2 years, I need to deserialize about 200,000 rows, which seems like a lot to me. And it doesn't seem to me that it makes sense to paginate these results. Can we query on the json? – Shoooryuken Aug 16 '23 at 13:07
  • 200k rows are not particularly a lot for modern computers and databases... However, you need to test how things work for your particular use case as you are the one, who can tell how the data will be used. – Shadow Aug 16 '23 at 13:44

0 Answers0