SQL reverse filtering system

Question

Sorry for the non-descriptive title, I don't know how to explain what I'm trying to achieve succinctly.

In most SQL use-cases, we have records in the database that have properties, and we are trying to retrieve records which match some conditions. For example, we may have for instance a table of products, where each product has a release date, a price and a category. We can then easily query for products released after a certain date, with a price below a certain value and belonging or not belonging to a specific category.

What I am trying to achieve is essentially the reverse of this. I have a table of services with conditions, and each user has various properties. (think 'date of birth', 'has drivers license', etc...) I need to display a list of all services that a given user is eligible for.

Another one of my requirements is that this needs to be possible for anonymous users, so the property values for the user won't be stored in a table anywhere, they will simply be provided as part of the user's request to my server.

I'm struggling to think of what the best way to achieve this would be. At the moment I see two main possibilities:

1. Build out a relational model for the conditions, and build long SQL queries based on user input

In order for a user to be eligible for a service, they must fulfill at least one Condition Group. In order for a user to fulfill a Condition Group, they must fulfill all the Conditions within that group.

Pros:

Works with a well defined ERD and proper SQL queries

Cons:

Have to generate very complicated SQL queries with multiple joins, reduced flexibility for writing conditions

2. Store conditions as a string. Build a condition parser and go through all services testing their condition against the user input.

Pros

Flexible and easier-to-write conditions, easier to develop

Cons

Queries have to take place in application-layer instead of database-layer, possibly bad performance

---

I’m wondering if anyone has dealt with a similar problem in the past, and how they solved it. Maybe there’s an option I’m not considering? I’m open to use a NoSQL solution if it’s a better fit for the problem, but my preference is to do this against a SQL database if practical.

Answer 1

My suggestion, (if you have to do it in sql) would be to split the conditions table by field.

Since the field is tightly coupled to the columns on the user table having it as a string is going to give you problems when you try and use it. Once in separate tables you can see that the problem becomes easier

select u.id, c.id, ( u.age > c.min and u.age <= c.max ) as result from conditionAge c left join users u 

repeat for each condition table and use as sub selects to tell what groups are fully satisfied.

However, this is business logic, you would be better off in my opinion retrieving all the conditions and evaluating them in code rather than attempting to do it in the data layer

Answer 2

I think the application layer is the only practical place for the actual rule evaluation to take place. As these rules become more complicated, entirely modeling and querying them with SQL may become very difficult (if not even impossible).

As to the performance: i dont know how many services and rules there are. But keeping them in a cache seems like a viable solution to me. That way you have all the flexibility a programming language offers and a good runtime performance.

Further, i'd not concern the DB with the actual rule modeling but instead use a good OO Model and Single-Table-Inheritance.

Answer 3

This answer may seem a bit counterintuitive, but in this case some denormalization might help.

Considering the fact that there will be only a limited number of variables used to check conditions, what you could do is have a table with all possible combinations of variables (for date or year of birth you could use 2 variables: between X and Y) and then create a table to link the services available to each of the combinations. Yes, this may create a whole bunch of records in the table that holds the combinations, but that isn't a problem.

Writing the SQL for this isn't too complicated and it is all that is needed.

The trade-off is maintaining the combinations table. This may however be less work than figuring out how to get all of the conditions to work properly and efficiently. The question is how many conditions/condition groups are there and how often will they change?