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)

1 answer

  • answered 2018-03-13 21:48 Daniele Murer

    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.