3

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

ERD

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.

  • 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. – Robert Harvey Dec 27 '16 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. – Joshua Walsh Dec 27 '16 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 – Robert Harvey Dec 27 '16 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? – Robert Harvey Dec 27 '16 at 15:51
  • 1
    Let us continue this discussion in chat. – Robert Harvey Dec 27 '16 at 15:56
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

  • 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. – Joshua Walsh Dec 27 '16 at 16:14
1

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.

1

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! – Joshua Walsh Jan 1 '17 at 12:45

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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