I have a few screens/requirement which contains a lot of form fields (dropdown) below is the JSON I get after the construction of JSON in vueJS.

{
  "14": {
    "required": true,
    "details": {
      "cDetails": [
        {
          "key1": "string",
          "key2": "string",
          "key3": "string",
          "obj1": {
            "key1": "1",
            "key2": "",
            "key3": "",
            "key4": 0
          }
        }
      ],
      "tDetails": [
        {
          "key1": "string",
          "key2": "string",
          "key3": "string",
          "obj1": {
            "key1": "1",
            "key2": "",
            "key3": "",
            "key4": 0
          }
        }
      ]
    }
  },
  "15": {
    "required": true,
    "details": {
      "cDetails": [
        {
          "key1": "string",
          "key2": "string",
          "key3": "string",
          "obj1": {
            "key1": "1",
            "key2": "",
            "key3": "",
            "key4": 0
          }
        }
      ],
      "tDetails": [
        {
          "key1": "string",
          "key2": "string",
          "key3": "string",
          "obj1": {
            "key1": "1",
            "key2": "",
            "key3": "",
            "key4": 0
          }
        }
      ]
    }
  },
  "obj3": {
    "required": true,
    "obj1": {
      "subobj1": {
        "key1": "",
        "key2": "",
        "key3": 0
      },
      "subobj2": {
        "key1": "",
        "key2": "",
        "key3": 0
      }
    }
  }
}

Firstly I Started writing model classes considering each JSON key, this creates a lot of MySQL tables and I get difficulty in tracing the data. So I decided to save the entire JSON in one column now my model class looks like this

class ScreenDetails{
    private Long id,
    private Long parentId,
    private String screeName;
    @Column( columnDefinition = "json" )
    private String screeDetailJSON;
}

And the MySQL table looks like below

CREATE TABLE `screen_details` (
  `id` bigint NOT NULL AUTO_INCREMENT,  
  `screen_details` json DEFAULT NULL,
  `screen_name` int NOT NULL, 
 `parent_id` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Operations I do POST GET By Id GET By parentId and screeName Update

Is anywhere the performance of the application gets down please let me know

CodePudding user response：

I think there would be issues on querying too. What if you have to query from an id that is there in the JSON string?. You can breakdown tables like this:

"obj1": {
        "key1": "1",
        "key2": "",
        "key3": "",
        "key4": 0
      }

As a table:

This way if you find another property which should be a key to query, you can add another column

CodePudding user response：

Add new columns for the things that you will filter on or sort by. Toss the rest into a single JSON column.