How to Represent Rules using a MySQL Table?

Question

I have a table of 100,000s of USERS (name, age, gender, phone, company, street, city, state, country, zipcode, etc).

I also have a table of thousands of PROMOTIONS which are offered to users. Now, for each promotion I need to add a rule which defines which subset of users it applies to.

For example, a rule might be: All users which have a zipcode of 10000 to 19999.

Another rule might be: All users which are female and do NOT live in the U.S.

Yet a third rule could be: Users older than 18 that live in DC, MD or VA. Etc etc.

Now given a specific user, I want to (efficiently!) find out which PROMOTIONS apply to that user.

What is a good strategy of modeling these rules, given that I want to be able to quickly find matching promotions given a specific user?

Answer 1

Here is one approach.

If you can break down each rule to a simple comparison, it is possible to store it like that in a way which will work for everything. What you end up with is a sequence of rule items comprising a field, a value and a comparison (e.g. equals, not equals, etc). They also need to belong to an actual rule (which you can name), which would be listed in another table.

Now it gets complicated. Each rule item also a level. This is so you can do AND and OR comparisons as they require a heirarchy. The way I've done this before is that Even levels AND and Odd levels OR. That means two items that must be true for a rule would both be at level 0, that is, they must both validate. For a rule that has one mandatory condition and two more where either can be true, i.e. 1 AND (2 OR 3), you put the mandatory rule at level 0 and the other two at level 1.

The advantage of this approach is that the code to assemble the SQL filters get the nesting right because it has to get the AND/OR mode right. It also makes it possible to build an editor for the rules.

However, this solution, like all the others, still requires applying all the rules in turn to your user sets to find which rules allow the user you're interested in.

Answer 2

What if you stored your rules in "SQL-like" form? For example, you have a USERS table and a PROMOTIONS table as you already mentioned.

You could add 2 new tables, PROMOTION_RULE and PROMOTION_PROMOTION_RULE. The PROMOTION_RULE would contain the rules, and PROMOTION_PROMOTION_RULE would link a given promotion to its associated rule(s). This design allows you to share the same rule for multiple promotions.

In PROMOTION_RULE, you could store a column called RULE_SQL, and you could store values like:

(gender != 'F' and country != 'US')    
(age > 18 and state IN ('DC','MD','VA')

etc.

Then you could look up the rules that apply for a promotion, and dynamically build an SQL statement which would be comprised of the rule_sql values.