0

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!

Improve this question
5
  • The following schema is given It don't explain how the tables are linked. products.id = product_variants.product_id is almost obvious, but how the property_values is joined in? The best way is to add FK into the scheme.
    – Akina
    Aug 29, 2018 at 7:17
  • It's a mistake to design one object (property values) with an attribute (describable_id) referring to different parents. Why don't you add the default product to the product_variant table (eg. as variant 0), so that the property_values can always refer to product_variant?
    – Gerard H. Pille
    Aug 29, 2018 at 8:52
  • @Akina describable_type = 'product' and describable_id = 1 is used to join the values on product 1. It's the same for variants.
    – rqk991
    Aug 29, 2018 at 9:28
  • @GerardH.Pille Thank you, I really like the idea and this would make the filtering simpler. The schema currently is more or less dictated by the application framework. With this schema the management of everything is handled by the framework since it follows the conventions. It should be possible to add the values for a "variant 0" but this will need some manual work on our side.
    – rqk991
    Aug 29, 2018 at 9:30
  • This smells like an EAV schema, but with tweaks to make it really bad. Back off! Follow the Tag I added to see what Q&A say.
    – Rick James
    Sep 30, 2018 at 17:56

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.