E-commerce product filter db design

Question

I'm creating an e-commerce site and my products are grouped in categories (like in any other e-commerce system).

The problem is the following: different categories (and products in it) need to have different attributes (like TV's smart tv or not).

So someone could help me with some product filtering "theory" (db design) what are the good practices to follow?

And how can I "build" these product category pages automatically (to build up automatically the filters for ex.: in the TV category a switch for smart tv or not and in Phones category smart phone or not ).

Thanks for the help!

Answer 1

Most ecommerce systems use a Category tree with infinite levels.

Then when editing your product you show that tree with checkboxes and you click on the checkboxes of the categories you want to assign this product to.

For this you will need the tables: product, category, and category_has_product. So when you check the boxes all these categories should be inserted to category_has_product for that product.

Then when searching you join product with category_has_product, quite simple.

Otherwise if you want users to be able to choose only one single category from the end of the Tree, but you want to be able to filter products inside a parent category and include the ones in the children you could take a look at stuff like this, it's more complex to understand but maybe you find some other examples on how to implement it:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

The point with nested categories is that you could end up storing only the final category_id on the product table but even so you will be able to do some magic to include that product in the search results of all the parent categories.

So the above would cover the "how to manage products into categories".

As for links: Well you could just loop inside the category tree and print the links where you want and how you want.

As for additional filters such as Type of TV (Smart or Not) you could either just include both inside the categories tree and forget about adding additional fields to the product form or you could have a more complex solution using something like attributes:

You would have table attributes (id, name) and table attributes_value(id, attribute_id, value) then on the product form instead of having static checkboxes for "This tv is Smart", you would loop into the attributes table, and you have a attribute named "Smart Tv", and the values would be "Yes, No, Maybe, etc", so you use that to draw dynamic dropdowns.

Now if you also want links for the attributes, well you just create a link to a category and to a specific attribute with a specific value. Example /category.php?id=2&attribute=1&attribute_value=5

Also if you want to have some seo-friendly urls, just add a "slug" field into both category table and attribute_values table and then rewrite the urls.

You could also take a look at Prestashop, it uses or used some of the things discussed here even if now it grew very complex and quite pain in the ass but might provide some useful information.