Geek Answers Handbook

How to represent rules using a MySQL table?

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

I also have a table of thousands of advertisements that are offered to users. Now for each advertisement, I need to add a rule that determines which subset of users it refers to.

For example, a rule might be: All users who have a zip code from 10,000 to 19999.

Another rule could be: All users who are women and do not live in the United States.

However, the third rule may be: users over 18 who live in DC, MD or VA. Etc etc.

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

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

+5
source share
5 answers

Here is one approach.

If you can break each rule into a simple comparison, you can save it in such a way that it works for everything. As a result, you get a sequence of rule elements containing a field, a value, and a comparison (for example, equal, not equal, etc.). They must also belong to the actual rule (which you can name), which will be indicated in another table.

. . , , . , , - . , , , 0, .. . , , , .. 1 AND (2 3), 0, - 1.

, SQL- , AND/OR. .

, , - , , , , , .

+2

, - .

(MVC) , , . .

, , , . . , cronjob.

:
, , , , .

.

+2

, "SQL-like"? , USERS PROMOTIONS, .

, PROMOTION_RULE PROMOTION_PROMOTION_RULE. PROMOTION_RULE , PROMOTION_PROMOTION_RULE . .

PROMOTION_RULE RULE_SQL , :

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

.

, , SQL, rule_sql.

+2

, , . , SQL , , , , ( , ).

, , ORM, , BLOB. , SQLAlchemy Python, Query, User.filter.

, , . , , SQL ORM.

+2

, ; "" ( ). , , . , : ..

, ( ) , ( where ). .

+1

More articles:


All Articles