If you string-concatenate atomic rules in a field, IMHO you will never manage to extract any information. Parsing these strings will be a nightmare.
With the fully normalized model I have in mind, your SQL should be a breeze. This is quite logical after all: your "flattened" rules are polynoms of degree 1 in an arbitrary number of variables; each monome of which is homéomorphic (?) to a subset of the rule set. This structure matches well the representation of records in a SQL table as set elements: - it is easy to check that a given monome exists, - then it is easy to check that the required monomes have been associated as a complex rule. HTH (hope I am right! - not tested) Ignatius _________________________ ----- Original Message ----- From: "John Dillon" <[EMAIL PROTECTED]> To: "Ignatius Reilly" <[EMAIL PROTECTED]> Sent: Saturday, February 07, 2004 20:56 Subject: Re: [PHP-DB] Rules in a database > Thanks for that. I understand you'd have one table with simple phrases like "a=b" being atomic rule 1, > "c=d" being atomic rule 2...e=f being atomic rule 4, say; the second table with "1,2" as an AND group > rule 1, being the 2 alternative - OR - conditions, one of which must exist; and the third table with > 1->4, stating that AND group rule 1 would lead to result 4. In total, if 1 or 2 is true then e=f. > > I think, however, that I might find it easier to combine: > > > - store "AND" rule groups in a table (1-n relation to the "atomic" table) > > - store full rules in a table (1-n relation to the "AND" table) > > into one table column and use string functions to extract the rule. The first table remains the same, > being a list of a=b rules. The second table might have a record in the IF field +(12 OR 3) AND (13 OR > 4) and 4 in the THEN field.. > > Looking beyond to the sort of queries I might want to run. If I knew a condition was true, say > condition 12, I would want to know what possible rules might be relevant (I think of a rule as an > IF:THEN set). Or if I knew two conditions were true, say 12 and 13, I would want to know what rules > applied when those two conditions were true. The queries would be: SELECT full_rule FROM > full_rules_table WHERE INSTR('/12/',IF) - I don't know if that INSTR works as shown. That would tell > me if the rule might apply. To know whether the rule would actually apply - is fully qualified - I > need to know if condition 13 - or alternative 4) is also true. I envisage parsing the expression +(12 > OR 3) AND (13 OR 4) into array elements (12 OR 3) and (13 OR 4) and then going through each and > comparing to the conditions I know exist, then reporting various facets of the rule, including > alternatives or additional conditions required. > > The report would contain information like: > > These conditions are true to start with..... > > The following results follow (since all conditions needed are true).... > > - the same result would occur in alternative conditions: > ..instead of condition 12 condition 3 applied etc > > The following results could occur if additional conditions were true:... > - result 1 ...the following extra conditions are required....etc. > > Do you think this is feasible? > > Regards, > > John > > > > > > ----- Original Message ----- > From: "Ignatius Reilly" <[EMAIL PROTECTED]> > To: "DB list PHP" <[EMAIL PROTECTED]>; "John" <[EMAIL PROTECTED]> > Sent: Friday, February 06, 2004 3:13 PM > Subject: Re: [PHP-DB] Rules in a database > > > > A bit of algebra first: > > > > any expression formed of atomic expressions, AND, OR and parentheses can be > > reduced to a "canonical" form, by using the De Morgan laws: > > a AND ( b OR c ) is equiv. to a AND b OR a AND c > > > > So a rule can eventually be reduced to "AND" groups, joined by "OR": > > a(1) AND... AND a(n) OR ... OR z(1) AND ... AND z(p) > > Each "AND" group contains 1 or more rules. > > > > Therefore, to model "input" rules: > > - store atomic rules in a table > > - store "AND" rule groups in a table (1-n relation to the "atomic" table) > > - store full rules in a table (1-n relation to the "AND" table) > > > > Now you can model "output" rules likewise, and finally create a table of > > associations between "input" and "output" rules > > > > Problem is: once you've flattened your rules to the canonical form, they can > > become illegible. So perhaps a hierarchical data model (XML) would be more > > appropriate, for your rules would remain human-legible (and thererfore > > human-maintainable) > > > > HTH > > Ignatius > > _________________________ > > ----- Original Message ----- > > From: "John" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Friday, February 06, 2004 15:35 > > Subject: [PHP-DB] Rules in a database > > > > > > > I work in tax and thus have to read legislation - some complex - and I > > > wanted to store some of the logic in a database so that if I know certain > > > conditions were true I could look up what results this might have. > > > > > > Thus I am thinking of having two tables - one of phrases and the other of > > > how these phrases are linked together as rules. A rule structure could > > be: > > > > > > IF A THEN B > > > IF A OR A1 OR A2 THEN B > > > IF A THEN B AND C > > > > > > I was wondering how to do this. For instance, each rule could be one > > record > > > in the table. The rules table has fields, IF and THEN. The ID field is > > the > > > rule ID. Assume the phrases are numbered. One IF record could say: > > > +/12/41/31/+/90/ meaning if phrases 12, 41 or 31 are true and phrase 90 is > > > true. Then if I have a real situation where condition 12 is true, for > > > instance, I can find it and access it using sub string functions. Does > > this > > > scheme seem OK? Has anyone done anything like this before? > > > > > > Regards, > > > > > > John > > > > > > -- > > > PHP Database Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php