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.


Works with a well defined ERD and proper SQL queries


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.


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


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.

  • 1
    Why don't you build up a SQL query in the application layer and submit that SQL to the database server? Then you get the best of both worlds: flexible, easy to write conditions, without the performance penalty. Commented Dec 27, 2016 at 15:30
  • @RobertHarvey I'm not really sure what you mean. If I knew how to build such a query I wouldn't have asked this question in the first place. How can I store my conditions against each service in my database? How can I then check these conditions against the user's input? An example of a condition might be "(DOB < 1998 AND hasDriversLicense = true) OR DOB < 1992", which somehow needs to be stored in the database. Then, user input might be {DOB:1997, hasDriversLicense:false} and I need to go through the database and check this input against all stored conditions. Commented Dec 27, 2016 at 15:34
  • What prevents you from concatenating these strings to achieve what you want? SELECT someFields FROM wherever WHERE DOB < 1998 AND hasDriversLicense = true Commented Dec 27, 2016 at 15:41
  • 3
    Then I ask my question again, in a slightly different way. What prevents you from taking whatever information is available to you, and using it to build up a sql string in the general form of SELECT someFields FROM wherever WHERE DOB < 1998 AND hasDriversLicense = true and executing it against the server to achieve what you want? Commented Dec 27, 2016 at 15:51
  • 1
    Let us continue this discussion in chat. Commented Dec 27, 2016 at 15:56

3 Answers 3


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

    u.age > c.min
    u.age <= c.max
    ) as result
    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

  • Thanks, at this point I'm thinking that doing it in the application layer is my only option, but I'm going to leave the question open for a bit in the hopes that someone has a neater solution. Commented Dec 27, 2016 at 16:14

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.


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?

  • Interesting idea! I'll definitely consider that, but I think for my purposes it may be a bit over engineered. Thanks for suggesting it though! Commented Jan 1, 2017 at 12:45

Your Answer

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

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