|
Razer's Edge: Database Models, Digital Business Rules
Blueprints:
Querying the data structures and Evaluating the Rules with SQL
Let's take
a look at how to query these table structures in order to evaluate a
business rule. All the SQL examples that you will see have not been written
specifically for any particular platform.
Rule set
evaluation requires that the data rules be linked with the data being
evaluated in
some fashion. Either the data must be passed as arguments or some meta
data must be
associated with each data rule so that the SQL knows what actual data
columns are being
evaluated.
Implementing this part of the solution becomes technology and business
specific.
In our case,
we will utilize a temporary table with the data elements we want evaluated.
Our
temporary table will have the following structure:
temp {
data_name
data_value
}
The following
data elements will be in the temporary table:
AccountPayDate, '01/05/2001'),
(CardMemberType, "NewMember"),
(CardMemberStatus, "Silver")
Assume that we have some stored procedure or program structure will call
the following SQL with
an id_rule_set = 1.
Arguments:
id_rule_set = 1;
Begin:
/* list of rules to evaluate */
select id_data_rule
from data_rule a, data_rule_set b
where a.id_rule = b.id_rule
and a.id_rule = 1
/* for each rule */
select 1
from data_rule a, temp b
where a.data_name = b.data_name
and condition = "="
and a.data_value = b.data_value
End;
Now lets combine these two pieces of logic. We are going to assume that
if we select count(*) from statement #2, that we are getting a count of
all rules from the rule set that evaluate to 'true'. We will also add
some logic for additional condition types.
elect count(*) into @li_count
from data_rule a, temp b, data_rule_set c
where a.id_rule_set = c.id_rule_set
and id_rule_set = 1
and a.data_name = b.data_name
and condition = "="
and b.data_value = a.data_value
select
@li_total = @li_total + @li_count;
select @li_count = 0;
select
count(*)
from data_rule a, temp b, data_rule_set c
where a.id_rule_set = c.id_rule_set
and id_rule_set = 1
and a.data_name = b.data_name
and condition = ">="
and b.data_value >= a.data_value
select
@li_total = @li_total + @li_count;
Ok, lets pick it apart to make sure we have the right values. We are going
to get a count of data rules from
rule set id = 1 where the meta data and data values both match for all
"=" conditions.
This works
ok for a single condition of "=", but what about other conditions?
Just add the code required to
check multiple conditions types. We could implement a stored procedure
that would evaluate each condition
type and hold the count in a working variable or update the temp table
with a flag that indicates the rule
evaluated to 'true' and then count all rules at the end which evaluated
to true.
In the case
of rules that use conditions which are expressions, we would need to code
special logic to handle
that. For example our case of AccountPayDate +30 >= AccountDueDate,
AccountDueDate is variable and would
need to be passed to the rules processor so it knows what value to use
when checking the rule.
The next
step is to check the count of rules which evaluated to true against the
total rule count for the rule set.
elect 1
from rule_sets
where id_rule_set = 1
where count(*) in (select count(*)
from data_rule a, temp b, data_rule_set c
where a.id_rule_set = c.id_rule_set
and id_rule_set = 1
and a.data_name = b.data_name
and condition = "="
and d.data_value = b.data_value)
This yields
a single SQL statement that evaluates the number of rules from a rule
set that evaluate to true.
If all rules match, then a Boolean condition (1) of true is returned.
A failure of any of the data rules will cause
the count to be short and the having clause will restrict the results
set of the outer select to no rows. In effect
this gives us our 'false' or null results set.
Applying
the default rules then becomes a simple matter of leveraging the temporary
table to hold return values
or by passing values back to the calling process in some fashion. In the
event a rule-set evaluates to true (1),
then we execute a secondary process to apply the default values from the
rule_set_defaults table.
The SQL to handle the rule-set defaults might look something like the
following:
Procedure
check_the_rules (@id_rule_set int)
Begin
/* figure out if the rule set is true */
select 1
into @rule_set_flag
from roulettes
where id_rule_set = @id_rule_set
where count(*) in (select count(*)
from data_rule a, temp b, data_rule_set c
where a.id_rule_set = c.id_rule_set
and id_rule_set = @id_rule_set
and a.data_name = b.data_name
and condition = "="
and d.data_value = b.data_value)
/* we are just selecting the list of defaults */
if @rule_set_flag = 1 then
select data_name, data_value
from data_default a, rule_set_default b
where b.id_rule_set = @id_rule_Set
and a.id_data_default = b.id_data_default
end-if
END
We could
easily change the above select to work with database meta data and update
fields directly in
the database or pass them back to some calling process.
|
