在MYSQL中将数据存储在多行还是只存储在一个json中更好？

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

当用户尝试集合中的问题时,存在多个随机数的问题。因此,在提交该集合时,最好将特定集合的数据存储为json或多行

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.

其中setinfo可以作为特定用户的json存储为他创建的任意数量的集合,当用户创建集合时,我们可以将数据附加到此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

同样但我们可以为set info创建另一个表,并将每个集存储为自己的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

这里每次用户创建一个集合时,它将在user_set_info中创建一个新列并使用FK userid,其中每个setinfo是

[
  {
    "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.

这里的问题是,如果用户提供了100个问题的测试,那么它将创建100行并且需要100个插入,查询可以是单个。

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

制作多行是一个好主意吗?什么时候最好在mysql列中使用json而什么时候不行?

1 个解决方案

#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

关系数据库通过以下方式回答这些问题: - SELECT - UPDATE - ALTER和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.

使用JSON存储时,您可能无法更改数据,从而大大降低了数据库的持久性,并使维护更加困难。

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

在决定数据存储时,请始终考虑CRUD和ACID