EAV is one of those love/hate relationships in Magento. EAV provides great flexibility but with potentially large performance implications. Knowing this is a sensitive topic does not however diminish my curiosity. I know there will be some passionate opinions out there in the community. But I also know there is a lot of expertise in the community which I also enjoy learning from. So I hereby don my flame retardant suit and dive in.
Disclaimer: This post is my own and is no indication or commitment from eBay. Seriously. There are no plans to change anything at present in Magento 2. Seriously. But its useful for me to learn from the community for when such discussions come up.
How It Works
Very briefly, EAV is where for each additional attribute instead of adding another column to a table, you create a new row in an EAV table where you have a key, attribute name, and attribute value. This means when doing SQL queries you have to do lots of outer joins to get the full record back, but it does make it easier to add new attributes to a table without database schema changes.
Why Use It
One example in Magento is a merchant can define their own attributes for products and store them. No need to do database schema changes. Extensions can also use it without introducing schema changes.
How Could it be Done Differently in Magento 2?
Well, actually this is where I was hoping to learn from the community. Have you tried implementing any other approaches and have practical experience of what worked well or badly? I have talked to people and read some blog posts. This post is to cast a wider net to see what I have missed.
Some examples I know of
- Add table columns, at least for common EAV attributes. (I believe it is faster to do column adds now in MySQL than in the past.)
- Use MongoDB or similar which has a richer data module – its easy to add additional attributes then. But it is more open heart surgery on your Magento 1 installation to achieve this, and transactional integrity is harder to guarantee with some data in MySQL and some in MongoDB.
- Instead of an EAV table, use a CLOB field to store say JSON encoded name/value pairs in the main table. Makes it harder to do SQL queries or use the results, but no need for join queries.
I was curious about other options and real world experiences. For example, how often do people query on EAV attributes? Do the number and size become large? Does a JSON field end up worse that separate tables with lots of join queries? And so on.
I know there will probably be some passionate opinions expressed on this one. That is fine. Please play fair, my flame retardant suit is on, but fire away with your comments!😉