Query product and product variants based on properties I'm trying to build a filter system for an e-commerce application. The following schema is given:

http://sqlfiddle.com/#!9/18b93


A product has many variants
A variant as has many property_values
A product has many property_values as well
A variant "inherits" the property_values of the related product


I cannot figure out the query to filter out all product variants that have for example property_id 1 = Red, property_id 2 = S and property_id 3 = Cotton. In this case the property_ids 1 and 2 are inherited from the product. The query sould also be flexible enough to be extended with any number of additional filters.

Can this be queried in a performant fasion? Would it make more sense to denormalize the data and keep a "index" table from these values?

Any help is greatly appreciated!
I followed @GerardH.Pille's advice and replaced the describable columns with a product_id and variant_id column. This way the querying is a lot simpler.