EAV : Store Everything, Searching nothing

We, Team ReadyBytes were researching one of e-commerce system, Shopping cart and planned to provide it as product in Joomla World.

Now we are going to design our database entity, Item (You can call Product... :) ). We are focused on few below conditions-

• Attribute should be dynamically added
• We will use MySql and respect MySql limitation
• Easy to search
• Easy to manage and understand for other developers

Ok,First I was going to Magento, the king of E-commerce Market and read their doc and see their database design....then say  "Ahhhhhhhhh.... its too complex.... not easy to understand" and think again is it the king .... ??  I have checked other applications openCart, virtualCart....etc then I have realized if you want to create highly flexible product then you will have to introduce lots of Complexity.

Here I have dropped about thinking of EAV model. I think its too complex to manage and tried to think about our vision "What we want"??

"We are trying  to implement simple and easy to use cart system so that novice user can enter in E-commerce world."

So I thought I'll find alternate of EAV. But let me back and forget other systems and try to think from starting point with our vision. Finally, I have decided I'll not check other system entity until prepare my thoughts. 

When you see other systems then you will follow their ideas ....

Think first by yourself then add up your idea with other system.

Now study about EAV model on various site and check their pros-cons. Finally I've found EAV is better for my need (store data and better query performance in MySql.)

Why I should have select EAV model for Shopping Cart?

• Its a denormalization process so my data will be scalable (in RDBMS)
• You can dynamically add any attributes  and store anything with EAV model.
• Easy to develop and manageable.
• Batch process should be done at backend so don’t worry about performance.
• Searching, hmmmm.. its a big issue with EAV model so we will manage another index table for item and their attributes.
• Few extra stuff which will not store into attribute-value table like product quantity (SKU).They will store with item entity because update operation  into attribute-table, may hurt query performance.

NOTE :  You can not directly import data from any existing system, You need extra script for data-import into EAV model and need to export script if you switch to other system.