mysql - JSON Type or JOINS on non-queried data
I'm working on an application for preparing and then generating tests for school.
Currently, I have faced the following dilemma - how to most effectively store answers to questions?
At the moment I have a ready table for questions, however, due to the fact that I will not search for answers to questions (tests will not be online only for printing) I have a dilemma how to store them. I have 2 concepts:
- an additional table containing individual variants of the response along with their attributes
- additional column to the question table in which the JSON structure will be located (I was thinking about a JSON or BIGTEXT type)
There is no limited number of answers for questions so we assume that one question has N answers and I will not search for them in the database.
What do you think about it? (application in PHP, Symfony 4)
Good database design rules say that in this case, where you have a 1:N relation, you should create a table that contains all the answers to the questions along with their attributes, like you said in the first point, and then link the two tables with a foreign key.