I'm developing an application that allows to analyze data from different viewpoints. This data is stored in 30+ tables of a SQL database (some tables have a 1:n or n:m relationship).
Now I want to filter this data by different criteria and display the filtered data in my application.
Simplified examples for such filters would be:
Show all products IN category X AND Y which AT LEAST 100 customers from New York have bought
Show all customers that have bought a product with a name starting with 'ab' and a price between 100 and 300 USD
Now I want to provide a filter in my application (similar to ebay where you can filter by category, seller, price, etc.).
My Question:
How do I design such a filter framework?
I don't want to write an SQL Query for each possible combination of filters. Additionally, there are at least 10 subjects I want to query and filter and performance is important.
Is there a best practice on how to build such a filter framework?