7

I want to implement products filtering in category and I have questions about the right DB schema. For now I have the following tables:

Categories:

1. id
2. category
3. description

Products:

1. id
2. category_id
3. product
4. image
5. price

Attributes:

1. id
2. attribute

Category_Attributes:

1. category_id
2. attribute_id

And the question I have is what tables should I create and what columns shout they have to store different kinds of values, attribute values, products attribute values etc.

Would it be normal to create 3 more tables:

Values:

1. id
2. value

Attributes_Values:

1. attribute_id
2. value_id

Products_Attributes_Values:

1. product_id
2. attribute_id
3. value_id

I've messed up in last tables. What would be better to store and filter?

AndroidLearner
  • 4,500
  • 4
  • 31
  • 62
UAMoto
  • 271
  • 3
  • 8
  • Can you explain more about what you want to achieve with your last three tables? What is each table meant to do? Are values meant to be a standard list? Does this list depend on category? Can a product have more than one value for an attribute? It's difficult to give you advice without understanding your requirements better. – Joel Brown Jan 07 '13 at 13:15
  • For instance I have category "Beads", it will have a few attributes: Diameter, Material, Packaging. Diameters will be 8mm, 10mm, 12mm etc, Material also could vary plastic, wooden, glass, metal, ceramic. In admin area I want to have selects for all attributes and choose the only one option. And in catalog I want to freely filter my goods, for instance I'm looking for Beads with diameter 10mm, 12mm, and material Ceramic. – UAMoto Jan 07 '13 at 20:36
  • Yes, each category will have its attributes and those attributes will have their values. – UAMoto Jan 07 '13 at 20:41

1 Answers1

13

What you are trying to achieve is an Entity-Attribute-Value (EAV) or possibly a row modeling solution. Note that this type of structure is largely frowned upon for a wide variety of pretty good reasons.

However, I have argued (e.g. here, here, here, and here) that EAV is EVIL, except when it isn't. One of those rare exceptions is in the case of a product catalog where you are tracking the properties of products and where those properties aren't that interesting (to your system!) except insofar as you need to retrieve these and print them out on a product web page or a comparison grid, etc.

Consider a design like this:

enter image description here

What you are doing in a model like this is describing which attributes products in a given category ought to have, what values those attributes might have, and then which values each specific product does have for each attribute.

This design does have all of the usual limitations that EAV imposes. However, if you want to ask questions like: "Which beads have a diameter of 8mm?" that is pretty straight-forward.

Community
  • 1
  • 1
Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • don't we face performance problems if we use `eav` ? if we face what suggestions you will give to solve performance issues , also is there any way to prevent this ? – fresher Sep 22 '16 at 10:13
  • @PhpBeginner Yes, there can be various challenges that come from using EAV, this is why so many people consider it an anti-pattern. I myself would never use EAV except in those very particular circumstances where EAV is perfectly suited. I've argued [here](http://stackoverflow.com/questions/11779252/entity-attribute-value-table-design/11972029#11972029) and elsewhere that online product catalogs are one of those applications. In that scenario, EAV would not be a performance concern and it would work with much simpler code than if full semantic data modeling were applied. – Joel Brown Sep 22 '16 at 10:46