I'm working on a small e-commerce solution which uses MySQL. At first there was an initiative by the boss to use MongoDB but I suggested relational database because there are lot entities with relations between them. He then told me to just store everything in form of JSON so we can easily add new attributes. Now we have field products
in the table orders
which is basically a text field containing JSON and it's content looks like this:
{
"<product 1 hash>": {
"attribute1": "value1",
"attribute2": "value2",
"vat": "0.25",
"price": "300"
},
"<product 2 hash>": {
"attribute1": "value1",
"attribute2": "value2",
"vat": "0.25",
"price": "270"
}
}
Fast forward to now, client is looking for statistics about best selling products / types of products and sets of products, as well as other data about products. Now I think this would be a huge performance issue once they reach bigger number of orders because we would need to;
- Get all orders
- Parse JSON from the
products
field - Look for the products we need
What would be a good way to improve performance of this solution? I saw that MySQL has a support for JSON
fields but I never worked with it before (I always used MongoDB when I had to design non-relational databases).