Structures DB Schema Design

Question

I am working on db schema for the below json. product has different parameterCategories and categories has different parameters. same parameter may belong to different categories. product can have 1 or more categories. product may have same categories with different parameters. let me know if my approach is correct. should I keep productCategory-section and section-parameters linking or simple table would work as I created below. all products of the same category will have same section and parameters so I am linking productCategory with parameters.

table Parameters
parameterid
parameterName
standard
value
parametersection
productCategory


{
  "productCategory": "electronic",
  "products": {
    "productId": "productId",
    "productName": "productName",
    "productParameterSections": [
      {
        "productParameterSectionId": "appearance",
        "parameters": [
          {
            "parameterId": "color",
            "unit": "",
            "standard": "red",
            "val": "light red"
          },
          {
            "parameterId": "brightness",
            "unit": "",
            "standard": "high",
            "val": "medium"
          }
        ]
      },
      {
        "productParameterSectionId": "quantitative",
        "parameters": [
          {
            "parameterId": "length",
            "unit": "cm",
            "standard": "440",
            "val": "400"
          },
          {
            "parameterId": "height",
            "unit": "cm",
            "standard": "red",
            "val": "400"
          }
        ]
      }
    ]
  }
}

Answer 1

Recently we work on the same schema design. What we did is as below:

• Made a list of all the parameters possible and all the different fields in the parameters possible.
• Then we created templates like here it is a category which is a combination of some of the parameters.
• Then that template is assigned to any entity like product in this case.

Pros of this approach - You can add as many parameters as you want in the list - you can customize the template as you want and attach it to the entity

How to use it

• Use parameters as a contact like an array of objects.
• Create a template with an array of the selected parameters so that it is creating a copy of the selected parameter for every category to keep the constant array safe from updates.
• The template is the second table which can have other fields like template name ( category name ) who created it when it is last updated even from which category it is created like a reference to own.
• The final entity table ( product ) will have reference to that template table and also an array from that template. So reference provides information about parameters and you can update the copy with the values to use.

I hope it explains well, let me know if you still have any doubts.