Re: Defining dynamic sets of business rules

BAIS <dave@digidem.com> wrote in article <01bcf917$61f1c590$7a1aa8c0@andersen13>... > 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 hard one 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