Defining dynamic sets of business rules

Defining dynamic sets of business rules

Post by BAIS » Tue, 25 Nov 1997 04:00:00



Hi all,

I have a database application where i want to validate a bunch of fields
against some known "business rules". Of course the rules and the fields are
must be dynamic and table driven. Here is an example of the application of
these rules:

Registration screen consists of 2 fields, state and sales_code

I want to define a rule that says "if state =nj return 1"
I also want to make sure that all "xxx" sales_codes return 5 "if
sales_code=xxx return 5"

What is the best way to do this. Say i create a table with 4 columns: name
,  value , operator, result
Then i can put each rule in this table. My registration program then checks
this table to see if its fields fit and if so does the rule apply? Here's
the problem with this, what do i key the table on? I plan on this table
growing to the 10s of thousands. If i have to scan this whole table this
won't be feasible.

Anyone have any suggestions or pointers (urls, books,...) Any help would be
appreciated!

David M Rosner

 
 
 

Defining dynamic sets of business rules

Post by Gene Wirchen » Tue, 25 Nov 1997 04:00:00



>Hi all,

>I have a database application where i want to validate a bunch of fields
>against some known "business rules". Of course the rules and the fields are
>must be dynamic and table driven. Here is an example of the application of
>these rules:

     Changed by whom?  A programmer or an end user?

Quote:>Registration screen consists of 2 fields, state and sales_code

>I want to define a rule that says "if state =nj return 1"

     Why?  What is being returned to?  If an end user is doing the
rule creation, will the end user understand what he is doing?

Quote:>I also want to make sure that all "xxx" sales_codes return 5 "if
>sales_code=xxx return 5"

     Do you mean that if a sales_code is a something i.e. member of
some group?

Quote:>What is the best way to do this. Say i create a table with 4 columns: name
>,  value , operator, result
>Then i can put each rule in this table. My registration program then checks
>this table to see if its fields fit and if so does the rule apply? Here's
>the problem with this, what do i key the table on? I plan on this table
>growing to the 10s of thousands. If i have to scan this whole table this
>won't be feasible.

     Are you going to require multiple operators?  For example,
          if state="NJ" or state="NY" return 1

Quote:>Anyone have any suggestions or pointers (urls, books,...) Any help would be
>appreciated!

     More details are required before anyone can give you more than
general advice.

Quote:>David M Rosner

Sincerely,

Gene Wirchenko

C Pronunciation Guide:
     y=x++;     "wye equals ex plus plus semicolon"
     x=x++;     "ex equals ex doublecross semicolon"

 
 
 

Defining dynamic sets of business rules

Post by Grant Perr » Wed, 26 Nov 1997 04:00:00




Quote:> Hi all,

> I have a database application where i want to validate a bunch of fields
> against some known "business rules". Of course the rules and the fields
are
> must be dynamic and table driven. Here is an example of the application
of
> these rules:

> Registration screen consists of 2 fields, state and sales_code

> I want to define a rule that says "if state =nj return 1"
> I also want to make sure that all "xxx" sales_codes return 5 "if
> sales_code=xxx return 5"

> What is the best way to do this. Say i create a table with 4 columns:
name
> ,  value , operator, result
> Then i can put each rule in this table. My registration program then
checks
> this table to see if its fields fit and if so does the rule apply? Here's
> the problem with this, what do i key the table on? I plan on this table
> growing to the 10s of thousands. If i have to scan this whole table this
> won't be feasible.

This is UGLY. What you need to do is define something to index your rules
which links to your state and sales_code fields otherwise you require your
sequential seach of the whole database (and everything grinds to a halt
speed wise).

I think you would be better off defining as follows:

Product file:
product_stuff (state, sales_code), product_id

Rule File:
rule_stuff (name,  value , operator, result), rule_id (maybe internal and
never seen)

Product Rule File:
product_id, rule_id

Now you have your indexing and can have millions of rules and millions of
products.
This has a problem of too much data in the product rule file. Eg we have
red, white, blue, green wigits. So I would split the product file into a
Product_Range file and a Product file, with rules applying to
Product_Range's. But then you might have a special on green wigits only ...

So my next solution would be:

Product_Range file:
product_range_id, range_stuff

Product file:
product_id, product_stuff

Product Range Rules:
product_range_id, rule_id

Product Rules:
product_id, rule_id

Rules:
rule_id, rule_stuff

The system then checks 2 files (Product Rules, and Product Range Rules).
This may seam stuppid when 1 file can do the job, but to enter all this
data in is bothersome, and breaking the Rules into Range rules, and Product
Rules is better normalisation, as well as much easier to do the data entry.
The checking of two files is more difficult to code and maintain, but worth
it.

Use your imagination to group things. Anything that requires a sequential
search as a normal operation I view as WRONG.

I have done something very similar to this for a client of mine. It is a
*e to try to explain to a non database person - solution, don't try.
Just show them how to use it (saves time, and you may still require that
haircut afterwards - after not having pulled it all out in frustration).

I believe in a dynamic set of rules for a database system. So I wrote a
database to define the database system. It speeds up and simplifies the
system maintenence. You need a couple of procedures -Make/Rebuild File,
retrieve File Definition. and your file maintence problems are greatly
reduced.

Grant Perry

 
 
 

Defining dynamic sets of business rules

Post by Brady Kel » Thu, 04 Dec 1997 04:00:00



Quote:>Use your imagination to group things. Anything that requires a sequential
>search as a normal operation I view as WRONG.

True widsom shining out in the darkness!  I am busy with a problem
that requires a sequential read of a table 800,000 records strong,
which results in a 22,000 row result.  Each of these rows requires a
sequential read of another table, several million strong.

I am not in a position to create indexes, and have resorted to
creating my own flat files as auxilliary indexes.  Without these, the
first search takes a few minutes, and then each of the subsequent
reads takes about twenty odd minutes each.  Using my indexes, one
sequential read is still necessary,  to create the index, but all the
subsequent searches are reduced to one simultaneous sequential read of
both tables.

Quote:>I believe in a dynamic set of rules for a database system. So I wrote a
>database to define the database system. It speeds up and simplifies the
>system maintenence. You need a couple of procedures -Make/Rebuild File,
>retrieve File Definition. and your file maintence problems are greatly
>reduced.

With a little more effort you could include the facility for standard
field definitions, e.g., you create a standard 'name' field type or
basic field with attributes such as type=text(25), lookup
table=xxxx,column heading='yyyy' etc.  It is my experience that this
is almost invaluable when developing and maintaining even small
applications.

Brady Kelly

 
 
 

1. Defining dynamic sets of business rules

Hi all,

I have a database application where i want to validate a bunch of fields
against some known "business rules". Of course the rules and the fields are
must be dynamic and table driven. Here is an example of the application of
these rules:

Registration screen consists of 2 fields, state and sales_code

I want to define a rule that says "if state =nj return 1"
I also want to make sure that all "xxx" sales_codes return 5 "if
sales_code=xxx return 5"

What is the best way to do this. Say i create a table with 4 columns: name
,  value , operator, result
Then i can put each rule in this table. My registration program then checks
this table to see if its fields fit and if so does the rule apply? Here's
the problem with this, what do i key the table on? I plan on this table
growing to the 10s of thousands. If i have to scan this whole table this
won't be feasible.

Anyone have any suggestions or pointers (urls, books,...) Any help would be
appreciated!

David M Rosner

2. deadlock on a single table

3. Sp_OAGetProperty Returns Empty String

4. Dynamic business rules stored in db tables

5. Upgrades/service packs

6. How to centralize business rules?

7. Open Filemaker databases with Active X

8. Business rules/Constraints firinng sequence

9. Enforcing Business Rules in SQL Server 2000

10. Business Rules Engine

11. Application Programming: Business Rules and Stored Procedures

12. Business rules/ Functions with SQL Server