I have a peculiar situation, I have tried for months to normalise a situation for my products database without success however I was able to create a JSON that works perfectly well. I still need to have it stored in a db table so that I can query the products. The scenario is as follows: I have many products that belong to one category. Each product has a name, description and price. However each product may or may not have child products in which case the child product inherits only the name and description but has its own extra name and price.

This is the JSON I am using and would like to replicate in a database table. As you can see some products have variations which means that they come in different styles or sizes but have their own price and variation name. Any help would be highly appreciated.

{
    "Scarves": [{
            "id": 1,
            "name": "Wooly",
            "dsc": "wooly scarf in red",
            "price": 2.99,
            "cpn": 1
        },

        {
            "id": 2,
            "name": "Silk",
            "dsc": "fine silk scarf",
            "price": 3.99,
            "cpn": 1
        }
    ],

    "Specials": [{
            "name": "Jeans",
            "dsc": "comfortable clothing in different options",
            "vari": [{
                "id": 3,
                "name": "blue",
                "price": 5.80,
                "cpn": 1
            }, {
                "id": 4,
                "name": "grey",
                "price": 6.50,
                "cpn": 1
            }]
        },

        {
            "id": 5,
            "name": "Shawl",
            "dsc": "good quality throw",
            "price": 5.99,
            "cpn": 1
        },

        {
            "name": "Skirt",
            "dsc": "premium skirts in different sizes",
            "vari": [{
                "id": 6,
                "name": "small",
                "price": 7.80,
                "cpn": 1
            }, {
                "id": 7,
                "name": "large",
                "price": 8.50,
                "cpn": 1
            }]
        }
    ]
}