Should I put this filtering in SQL code or application code?
Suppose I have the following models in my web application:
Table User:
(attr1, attr2, attr3, ...)
Table Prize:
(condition1, condition2, condition3, prize_val)
Application logic: if the user meets all the conditions of the Prize, I want to give him a prize. The condition can be either NULL (True for all users) or a specific value. Each condition can be calculated using custom attributes. I can do filtering in two ways:
- Retrieve all prize rules from the database (up to 100) and replay the rules in the application code, checking if the current user matches the rule to get a list of prizes.
-
SQL user to do filtering like this:
SELECT prize from Prize where (condition1=NULL or condition1=user_condition1) and (condition2=NULL or condition2=user_condition2) ...
My question is, which one is more efficient?
And a more general question: when is it better to do filtering in application code rather than SQL?
PS. The reason I even think about iterating through the code is this: if I repeat the code and condition1 is NULL to win, I don't need to evaluate condition1 for the user (this calculation can be expensive); But if I take the SQL approach, I need to pre-compute each condition for the user.
source to share
-
You have a matrix of conditions with a prize on each row.
-
Condition values may change over time along with the prize value
Thus, it is desirable to store in a database. The data must be in the database and the logic must be in the code. In your case, the terms provide data that changes. But the logic remains constant.
Hope I understand.
source to share
Your Prize table is not normalized. I see a one-to-many relationship with a Condition.
When you do this, your filter is a union, not an increasingly complex WHERE clause.
In any case, I think this is best done with a database. You need to take care that you are dealing with primary keys. Your queries will not perform well if they are not indexed properly.
source to share