Unleash the Power of Magento EAV Database Structure, Generic EAV Object
One of the major features of Magento is the Entity-Attribute-Value (Magento EAV) data model.
This is indeed quite a block to play with. However with the right leverage, it can become rather handy.
To some extent, using EAV is, arguably, an improvement. The major advantage of the Magento EAV is: there won’t be endless ad hoc attributes inserted to the table, which is one of the critical flaw for older platforms, say OS-commerce, where table columns rapidly grow to an unmanageable size.
On the other hand, EAV basically means no raw queries of the database, or at least very challenging if you are really determined to query directly into the database, not to mention that an upgrade will easily break your code. This is, indeed, very clumsy for simple things like selecting a group of products or customers. In addition, there is quite an efficiency trade-off since there would be quite a few table joint to load an EAV object. Also, quite a lot of data a duplicated throughout different tables, a small price we have to pay.
Despite all these, personally, I find the Magento EAV quite okay. I guess the decision making factor for the Magento team to choose EAV tables against flat table is that EAV is generically more flexible. It’s relatively easy to maintain given the fact more than often multiple developers/teams are involved in the same project.