database schema for products attributes

Question

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?

Answer 1

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:

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.