I'm trying to build a filter system for an e-commerce application. The following schema is given:
- 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!