What I'm trying to do:
Create a database that, instead of generating results from a criteria, matches an input against a database of criteria.
Sometimes we can save our search results and have a site mail us whenever a new item comes up that meets our criteria.
- Bob is interested in blue widgets, red widgets, or widgets with size > 5
- Alice is interested in blue widgets or green widgets
- Tom is interested in green widgets or widgets size between 7 and 10
- If we receive a green widget with size 10, everyone will see it.
- If we receive a red widget with size 3, only Bob will see it.
- If we receive a purple widget with size 9, Bob and Tom will see it, but not Alice.
What I've Done
Created a list of queries and run them each against the records. Problem with this is that I could end up with tens of thousands of saved queries (search profiles).
Researched RETE algorithm based solutions such as expert systems. However, I'd rather have this be saved in a database.
Is this possible to solve with mySQL other than saving SQL statements and running them over and over? Is there some feature of mySQL that I'm missing - that could show what saved queries match a particular input?