Dynamically generate criteria in SQL

Question

I have a user table that contains dozens of columns, such as date of birth, year of ownership of the vehicle, make and model of the vehicle, color and many other personal fields that are not related to the vehicle.

There is also a second table called “Coupons”, which should be designed in such a way as to support such qualifications as “the user has the right to receive less than 30 years of age”, “the user has the right to receive a car exceeding 10 years”, if car color is green. "

When a user logs in, I need to submit all the coupons to which the user is entitled. The problem I am facing is that the qualification of a coupon can be numerous, can have qualifiers equal, more or less, and can have different combinations.

My only solution at this point is to save the actual sql row in one of the columns of the coupon table, for example

select * from Users where UserId = SOME_PLACEHOLDER and VehicleYear < 10 

Then I could execute sql for each row of the coupon and return true or false. Seems very inefficient as I could potentially execute 1000s of sql statements for each coupon code.

Any understanding, help is appreciated. I have server code in which I could make a loop.

Thanks.

Answer 1

A very difficult problem. It seems users will be added at high speed, with coupons at a fairly regular frequency.

Adding SQL to a table that will be used dynamically is workable - at least you get a new execution plan - BUT your plan cache may pop up.

I have the feeling that launching one coupon for all users will probably be your most effective query, because one set of criteria, which will be quite selective for users at the beginning and the total number of coupons, will be small, while all coupons for one user are Separate criteria for each coupon for this user. Running all coupons for all users may still work well, even if it efficiently cross-connects first - I assume it will just be dependent.

In any case, the case for all coupons for all users (or cut anyway, in fact) will be something like this:

 SELECT user.id, coupon.id FROM user INNER JOIN coupon ON ( CASE WHEN <coupon.criteria> THEN <coupon.id> -- code generated from the coupon rules table CASE WHEN <coupon.criteria> THEN <coupon.id> -- etc. ELSE NULL ) = coupon.id 

To generate coupon rules, you can relatively easily concatenate strings with one hit (and you can combine the design of individual rule lines for a coupon with AND with an additional internal template):

 DECLARE @outer_template AS varchar(max) = 'SELECT user.id, coupon.id FROM user INNER JOIN coupon ON ( {template} ELSE NULL ) = coupon.id '; DECLARE @template AS varchar(max) = 'CASE WHEN {coupon.rule} THEN {coupon.id}{crlf}'; DECLARE @coupon AS TABLE (id INT, [rule] varchar(max)); INSERT INTO @coupon VALUES (1, 'user.Age BETWEEN 20 AND 29') ,(2, 'user.Color = ''Yellow'''); DECLARE @sql AS varchar(MAX) = REPLACE( @outer_template ,'{template}', REPLACE(( SELECT REPLACE(REPLACE( @template ,'{coupon.rule}', coupon.[rule]) , '{coupon.id}', coupon.id) FROM @coupon AS coupon FOR XML PATH('') ), '{crlf}', CHAR(13) + CHAR(10))); PRINT @sql; // EXEC (@sql); 

There are ways to finish the game - play here: http://data.stackexchange.com/stackoverflow/q/115098/

I would consider adding calculated columns (possibly stored and indexed) to help. For example, a calculated column with age - unstable, most likely will work better than a scalar function.

I would consider the issue of this with a table that says whether the coupon is valid for the user and when it was last confirmed.

It looks like age may change, and the user may become valid or invalid for the coupon as birthday passes.

When a user logs in, you can create a background task to update your coupons. Subsequent logins will not need to be updated (since this is unlikely to change until the next day or the triggering event).

A few ideas.

I would also add that you should have a way to check the coupon before approving it, to make sure there are no syntax errors (since SQL is arbitrary or arbitrary) - this can be done relatively easily - perhaps a test user table (test_user as the user in the generated code template) it is required to contain pass and failure lines, and coupon rules on them. Not only EXEC should work - the rows it returns should be expected and only expected rows for this coupon.

Answer 2

This is not an easy task. Here are some quick ideas that might help depending on your domain requirements:

• Limit the type of criteria you will filter on so that you can use dynamic or non-dynamic sql to execute them effectively. For example, if you only have integers between the range of min and max as a criterion, then the problem will be simpler. (You only need to know the field name and minimum values ​​to describe the criteria, not the full where statement.)

• Create some views that will be useful for attributes. Then run queries against these views - or preview these views first. For example, a representation of an age group in which there is a field that may contain values < 21 , 21-30 , 30-45 , >45 . Then your choice just has to return the rows from this view that match those rows.

• Create a table that stores the results of query execution that meet the criteria (this can be disabled using the reverse process). Then, for a given user, verify membership by looking at where this user ID exists in the table.

Thinking about this, I understand that all my suggestions are based on one idea.

A query for an individual user will work faster if you first execute an SQL query with all users and the cache, which will result. If each user plays queries across the entire data set, you will lose efficiency. You need to somehow cache the results and reuse them.

Hope this helps - please comment if these ideas are not clear.

Answer 3

My first thought on an approach (similar to Hogan) would be to test the applicability of the coupon at the time the coupon was created. Save these results in a table (e.g. User_Coupons ). If any user data is changed, your system will repeat any changed users for whom coupons apply. When creating (or changing) a coupon, it will only check this coupon. When creating (or changing) the usage time, it will only check this user.

Coupon criteria should be from a known set of possible criteria and at any time when you want to add a new type of criteria, perhaps this will lead to a change in code. For example, let's say you have a table like this:

 CREATE TABLE Coupon_Criteria ( coupon_id INT NOT NULL, age_minimum SMALLINT NULL, age_maximum SMALLINT NULL, vehicle_color VARCHAR(20) NULL, ... CONSTRAINT PK_Coupon_Criteria PRIMARY KEY CLUSTERED (coupon_id) ) 

If you want to add the ability to base the coupon on the age of the car, you will need to add a column to the table, and you will also have to adjust your search code. You must use NULL values ​​to indicate that criteria are not used for this coupon.

An example query for the above table:

 SELECT CC.coupon_id FROM Users U INNER JOIN Coupon_Criteria CC ON (CC.age_maximum IS NULL OR dbo.f_GetAge(U.birthday) <= age_maximum) AND (CC.age_minimum IS NULL OR dbo.f_GetAge(U.birthday) >= age_minimum) AND (CC.vehicle_color IS NULL OR U.vehicle_color = CC.vehicle_color) AND ... 

This can become cumbersome if the number of possible criteria is very large.

Another option is to save the coupon criteria in XML and use the business object for your application to determine eligibility. It can use XML to create the correct query on the User table (and any other necessary tables).

Answer 4

Here is another opportunity. Each criterion can be assigned a query template, which you can add to your queries. This is simply related to updating data instead of DDL and can have good performance. This will be dynamic SQL.

 CREATE TABLE Coupons ( coupon_id INT NOT NULL, description VARCHAR(2000) NOT NULL, ... CONSTRAINT PK_Coupons PRIMARY KEY CLUSTERED (coupon_id) ) CREATE TABLE Coupon_Criteria ( coupon_id INT NOT NULL, criteria_num SMALLINT NOT NULL, description VARCHAR(50) NOT NULL, code_template VARCHAR(500) NOT NULL, CONSTRAINT PK_Coupon_Criteria PRIMARY KEY CLUSTERED (coupon_id, criteria_num), CONSTRAINT FK_Coupon_Criteria_Coupon FOREIGN KEY (coupon_id) REFERENCES Coupons (coupon_id) ) INSERT INTO Coupons (coupon_id, description) VALUES (1, 'Young people save $200 on yellow vehicles!') INSERT INTO Coupon_Criteria (coupon_id, criteria_num, description, code_template) VALUES (1, 1, 'Young people', 'dbo.Get_Age(U.birthday) <= 20') INSERT INTO Coupon_Criteria (coupon_id, criteria_num, description, code_template) VALUES (1, 2, 'Yellow Vehicles', U.vehicle_color = ''Yellow''') 

Then you can build a query simply by combining all the criteria for any given coupon. The disadvantage of the big one is that it is only unidirectional. Given a coupon, you can easily find who suits him, but given a user, you cannot find all the coupons for which they are entitled, except for passing through all coupons. I guess the second is what you are likely to be most interested in. Maybe this will give you some other ideas.

For example, you can potentially work with it in another way by setting a certain number of criteria in the table, and the table of links to coupons / criteria shows whether the criteria are active or not. When prompted, you can include this in your query. In other words, the query will look something like this:

 WHERE (CC.is_active = 0 OR <code from the code column>) AND 

Requests become very complicated, because you either need to join once for all possible criteria, or you need to request a comparison of the number of active requirements for the coupon compared to the number that is being fulfilled. It is possible in SQL, but it is similar to working with the EAV model, which basically consists in the fact that this happens: variation on the EAV model (yuck)