Is it better to store data in multiple rows or just in one json in MYSQL?

Question

Once such example for this can be the question bank for a user and test. There can be a lot of relation that can arise in future.

When user attemps questions in a set there are multiple random number of question. So while submitting that set it is good to store data of a particular set as json or multiple rows

Approach 1

QuesTable

id | ques 

UserTable

id | username | setinfo

Where setinfo can be stored as json for a particular user for any number of sets he create, when user creates a set we can append the data in this json.

{
  "sets": [
    {
      "set1": [
        {
          "q1": {
            "given_answer": "a",
            "some_key1": "some_value1",
            "some_key2": "some_value2"
          },
          "q2": {
            "given_answer": "c",
            "some_key1": "some_value1",
            "some_key2": "some_value2"
          },
          "q3": {
            "given_answer": "b",
            "some_key1": "some_value1",
            "some_key2": "some_value2"
          }
        }
      ]
    },
    {
      "set2": [
        {
          "q1": {
            "given_answer": "a",
            "some_key1": "some_value1",
            "some_key2": "some_value2"
          },
          "q2": {
            "given_answer": "c",
            "some_key1": "some_value1",
            "some_key2": "some_value2"
          },
          "q3": {
            "given_answer": "b",
            "some_key1": "some_value1",
            "some_key2": "some_value2"
          }
        }
      ]
    }
  ]
}

APPROACH 2

Its same but we can create another table for set info and store each set as its own id

QuesTable

id | ques 

UserTable

id | username

user_set_table

id | userid | setinfo

Here every time user creates a set it will create a new column in user_set_info and using FK userid where each setinfo is

[
  {
    "q1": {
      "given_answer": "a",
      "some_key1": "some_value1",
      "some_key2": "some_value2"
    },
    "q2": {
      "given_answer": "c",
      "some_key1": "some_value1",
      "some_key2": "some_value2"
    },
    "q3": {
      "given_answer": "b",
      "some_key1": "some_value1",
      "some_key2": "some_value2"
    }
  }
]

APPROACH3

QuesTable

id | ques 

UserTable

id | username

User_Set_Info

id | userid | quesid | given_ans | somekey1 | somekey2

Here the issue is if user gives a test that has 100 question so it will create 100 rows and needs 100 insertion tough the query can be single.

Is it a good idea to make multiple rows ? When should be best to use json in mysql column and when not?

Answer 1

The questions you should ask yourself are:
- Is the data easy to retrieve?
- Is the data easy to update?
- What is my data's durability if I change the model later?

Relational databases answer these questions with:
- SELECT
- UPDATE
- ALTER and UPDATE

When using a JSON storage, you may have trouble to alter the data, reducing drastically the durability of your database, and making it way more difficult to maintain.

When taking your decision about data storage, always think CRUD and ACID