Database Design for ECommerce project (Should I use EAV Approach)

Question

I am about to deign my first E-Commerce Database.

What i have find out in most E-Commerce websites is that these sites have Category, then SubCategory and then again SubCategory and so on. And the depth of SubCategory is not fixed means One Category have six nested Sub Category while some other have different

Now All the products have attributes associated with it.

Now my question is are these websites keep on adding tables for nested sub categories and keep on adding columns for the attributes in the database

OR

They apply something called as "EAV" model (if i am right) to solve this problem or they keep on adding columns and or tables and also keep on updated the WebPages as on many sites i have found there is now a new category.

(If they use EAV model then the website performance is impacted isnt it..)

Since this is my first ECommerce project please provide some valuable suggestions of yours.

Thanks,

Any help is appreciated.

Answer 1

What you need is a combination of EAV for product features and nested sets for product categories.

While I certainly agree that EAV is almost always a bad choice, one application where EAV is the perfect choice is for handling product attributes in an online catalog.

Think about how websites show product attributes... The attributes of products are always shown as a vertical list with two columns: "Attribute" | "Value". Sometimes these lists show side-by-side comparisons of multiple products. EAV works perfectly for doing this kind of thing. The things that make EAV meaningless and inefficient for most applications are exactly what makes EAV meaningful and efficient for product attributes in an online catalog.

One of the reasons why everyone always says "EAV is EVIL!" is that the attributes in EAV are "meaningless" insofar as the column name (i.e. meaning of the attribute) is table-driven and is therefore not defined by the schema. The whole point of schemas is to give your model meaning so this point is well taken. However in the case of an online product catalog, the meaning of product attributes is really unimportant to the system, itself. The only reason your catalog system cares about product attributes is to dump them in a list or possibly in a product comparison matrix. Therefore EAV is doesn't happen to be evil in this particular case.

For product categories, you want a nested set model, as I described in the answer to this question. Nested sets give you very quick retrieval along with the ability to traverse multiple levels of an unbalanced hierarchy at the expense of some precalculation effort at edit time.