Query product and product variants based on properties

Posted on

Question :

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!

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *