Using MySql 5.7 JSON columns for EAV

Question

I am developing an e-commerce product and I have been able to implement all functionality and am left with allowing users to create additional attributes for a product. Right Now I have two options.

EAV

EAV is largely frowned upon but seems to work for Magento. But after researching all the headaches it causes I am a bit reluctant to use it

Use JSON Columns in MySql 5.7

This is rather new an I have not seen it being implemented anywhere else and I am dreading full table scans as a result of querying the JSON attributes. But after reading this MySql 5.7 JSON they seem to recommend using JSON. and it would be less tiresome than implementing something like this Practical MySql schema advice .

My question is, although I am biased towards using the JSON column way of storing attributes as NoSQL is not an option for me, are there any drawbacks that are more severe than using EAV tables.

Answer 1

Neither EAV nor choosing a JSON column are bad approaches in your case, but which one is really better for you depends on what you want to do with the data once it's stored in the database.

If all you want is to have a product with user-defined attributes and you want to read the product as a whole going the JSON way is going to provide a better performance for you, because the whole product will be located within one table, you can simply decode the retrieved JSON from the database and do with it as you please on the frontend.

If you however want to not only read the product as a whole but, with a future insight, maybe introduce the ability to filter out products with certain attributes (let's say color), using the EAV approach would increase performance of this operation, as you could filter out products whose attribute names directly match the one you're searching for.

SELECT
    pa.ProductId
FROM
    product_attributes pa
WHERE
    pa.`Name` = "color"

Should you have this feature with the JSON column, going through the JSON attribute model takes up more resources than direct string comparsion.

---

As a developer of REST API backend for mobile applications, an example I am often working with is providing to the user an overview of push notifications they have received through some notification centre within the mobile client.

Because I am not planning to do heavy querying on the data on a frequent basis, the JSON column is completely fine. I just want to provide the data existing in different formats to the user when they query it, so I take the data out of the database and dump it to the user. It's even better because the REST APIs surface is JSON, so I am not even required to do any additional formatting as I would be required to do in the case of an EAV model.