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.
Here comes my strategy, every time I need to create a new object, unless I know for sure this new object is very simple and demands very frequent save/load, I will always choose an EAV approach.
Indeed I pushed myself further to package a generic EAV object module. So new EAV objects can simply extend this generic EAV object.
So far, this proves to be very useful for lots of critical add-ons. For example, I can easily create models like ‘merchant’ or ‘seller’, which helped a lot when I worked on converting Magento from a store to a selling platform similar to Amazon or eBay.
In brief, the generic EAV object module creates the corresponding eavobject_xxxxxx tables in the database, via /sql/setup upon Magento initialization. It also contains a setupController to add/edit/delete new attributes. In addition, there is also a generic EAV object collection model, this is very critical for search functions.