Building a Rule-based Application

Loading the Sample Application

Table 7.1 contains a listing of the single project that comprises the sample application. To execute the sample code available for download from the book's Web site, you will need to execute the following steps:

1.	If IIS has already been installed on your machine, then you will have by default a directory named C:\Inetpub\wwwroot. Create a new folder in this directory named Chapter 7 and copy the contents of the code directory (available for download from the book's Web site) to this location.
2.	You will then need to open the Internet Information Services Manager and browse to the Default Web Site node. Right-click the Chapter 7 folder and click Properties. From the Directory tab, click the Create button. When finished, click OK and exit Internet Information Services Manager.
3.	If Visual Studio .NET has already been installed on your machine, you can open the code sample by double-clicking the solution file named Chapter7.sln from the C:\Inetpub\wwwroot\Chapter7 directory using Microsoft Windows Explorer.
4.	After opening the solution file, you will need to set the start page. This is done by right-clicking the start page, Rules.aspx, from Solution Explorer and clicking Set as Start Page.

Table 7.1. Code listing for the Chapter 7 project. The files can be accessed by opening the Chapter7.sln file.

Project	File Name	Description
Chapter7	Chapter7.sln	Solution file
	Components/Rule.vb	Contains server-side Visual Basic code used to add and update rules, evaluate rules, and execute actions.
	Components/Survey.vb	Contains server-side Visual Basic code used to process surveys and evaluate whether the questions are already represented in database.
	Banner.ascx	User control included at the top of each Web page; contains the left-hand menu for the application.
	EditRule.aspx	Web page used to add new rules and edit existing ones.
	EditRuleType.aspx	Web page used to add new rule types and edit existing ones.
	NewQuestion.aspx	Web page used to enter new survey questions. Executes code to evaluate whether the question is new and will initiate the four rule phases.
	Rules.aspx	Web page used to view existing rules and rule types. Can also be used to navigate to the EditRule.aspx and EditRuleType.aspx pages.

Rule Maintenance

The application lists four main functions in the left-hand menu. The first, Rule Maintenance (see Figure 7.2), is functionality that would only be available to an administrator. It is from here that rules and rule types will be defined.

Figure 7.2. Screenshot of the Administrative Web page, Rule Maintenance. From here, administrators can add new rules and rule types or edit existing ones.

Note

The sample application provided with this chapter only includes functionality related to the processing of rules. Therefore, only two of the items listed in the main menu (New Question and Rule Maintenance) are included.

The application also does not include a proper method for user authentication. The Banner.ascx user control does contain code to capture the identity of the current user, but this will only be useful if security has been enabled for the IIS virtual directory.

An actual production system would include an authentication method of some sort (such as forms or windows authentication). Based on the identity of the authenticated user, only administrators would be granted access to the Rule Maintenance menu item.

Defining Rule Types

As stated earlier, rule types are used to group rules together. They act as a template for the rules. To view the rule types already defined in the database, open the solution file for Chapter 7 (refer to Table 7.1), right-click Rules.aspx from Solution Explorer, and click Set as Start Page. You can then execute the application by hitting F5.

If you click the Rule Types drop-down box, you should see a list of four rule types. Select DataType and click Edit Rule Type. Figure 7.3 is a screenshot of the Rule Type Web page. Note that the Condition Text and Result Text fields utilize a value named "param1." Param1 is a placeholder used to indicate where condition and result values will be placed. Each rule definition contains values that will replace param1 when the rule is executed.

Figure 7.3. Screenshot of the Edit Rule Type Web page. This page is used to add or edit rule types used by the application.

For this rule type the Condition SQL textbox is blank. The Result SQL textbox, however, contains the following valid SQL statement, SELECT name as id, name as param1 from systypes. The Systypes table is a system table used by SQL Server to store information about data types. The data returned by this SQL statement will be used later to populate a drop-down box on the Edit Rule page. The SQL statement is therefore used to restrict the possible values entered as rules are defined.

The Condition Method and Result Method textboxes are used to specify compiled method names. These method names will be called dynamically during the Condition Matching and Application of Results phases. For each rule type, the combined values in these textboxes should be unique. This is because each rule type represents a distinct condition that must occur and the resulting action that will take place. A unique index defined in SQL Server ensures that this will be the case.

Note

When setting up a rule base, the most time-consuming process will involve deciding how to define the rule types and which ones to include. The rule type defines the distinct combination of a specific condition that must be met in order for an action to be executed.

Defining Rules

Click Cancel to return to the Rules.aspx page. Select integer from the Rules drop-down box and click Edit Rule. Figure 7.4 is a screenshot of the Edit Rule Web page. Note that the labels above Condition Value and Result Value were defined when setting up the DataType rule type. They are repeated here to assist the user when defining the Condition Value and Result Value textboxes. Thus when this rule is evaluated in the Condition Matching phase, it will look to see if the question contains the word or phrase "how many." If this condition is true, the integer rule will be considered valid.

Figure 7.4. Screenshot of the Edit Rule Web page named EditRule.aspx. This page is used to add or edit rule definitions. Rules are based on template values defined for the associated rule type.

Note

For the sample application, rules are applied only when a new question is entered. Therefore, the rule base for the sample application is small and simple in scope. This was done intentionally for demonstration purposes. Keep in mind however, that an actual rule-based system would consist of a much larger rule base. The rules may involve several different aspects of the application and perform a wide array of actions.

Using a Threshold Value

The first thing to do, when entering a new question, is to attempt to match it with fields already in the database. Sometimes a proposed question is similar to one already entered. To rate the similarity, the application utilizes an adjustable threshold percentage value. If the question is considered too similar to an existing question, it is not added and the user is notified. This threshold value is stored as an application setting in the Web.config file and is named ThresholdPercent with a default value of 70. Details about how this threshold enters into the similarity comparison are given below.

A new question is entered on the NewQuestion.aspx page when an employee types in the entire question and clicks Next. Most of the code we will examine is spawned from this event. To review this code, right-click the NewQuestion.aspx page from Solution Explorer and click View Code.

The ApplyRules method will execute a series of functions from the Rule and Survey class files, located in the Components folder. The first function from the Survey class file, seen as follows, is named StripQuestion.

 'First replace common chars with word equivalents 'or just remove entirely Dim strQuestion As String = Question strQuestion = strQuestion.Replace("%", " percent") strQuestion = strQuestion.Replace("&", "and") strQuestion = strQuestion.Replace("@", "at") strQuestion = strQuestion.Replace("+", "plus") strQuestion = strQuestion.Replace("=", "equals") strQuestion = strQuestion.Replace("-", "minus") strQuestion = strQuestion.Replace("'", "") strQuestion = strQuestion.Replace("""", "") strQuestion = strQuestion.Replace(",", "") strQuestion = strQuestion.Replace("~", "") strQuestion = strQuestion.Replace("`", "") strQuestion = strQuestion.Replace("?", "") strQuestion = strQuestion.Replace(";", "") strQuestion = strQuestion.Replace(":", "") strQuestion = strQuestion.Replace("/", "") strQuestion = strQuestion.Replace("\", "") strQuestion = strQuestion.Replace("|", "") strQuestion = strQuestion.Replace("{", "") strQuestion = strQuestion.Replace("}", "") strQuestion = strQuestion.Replace("[", "") strQuestion = strQuestion.Replace("]", "") strQuestion = strQuestion.Replace("!", "") strQuestion = strQuestion.Replace("#", "") strQuestion = strQuestion.Replace("^", "") strQuestion = strQuestion.Replace("*", "") Dim arrIn As String() Dim strT As String Dim strSql As String = "SELECT word FROM QuestionExtraWords _             WHERE word IN (" arrIn = strQuestion.Split(" ") Dim i As Int16 = 0 For Each strT In arrIn   'Check to see if the word is in the ExtraWords table   'by building one query statement   If i = 0 Then     strSql += "'" + strT + "'"   Else     strSql += ",'" + strT + "'"   End If   i += 1 Next strSql += ")" 'Execute the newly created query statement Dim dr As SqlDataReader = _   SqlHelper.ExecuteReader(AppSettings("Chapter7.Connection"), _   CommandType.Text, strSql) Dim arrExtra As New ArrayList 'Copy the Extra words into an array list Do While dr.Read    arrExtra.Add(Convert.ToString(dr.Item(0)).ToLower) Loop 'We will loop through our two arrays and compare ' the results. A temporary array list will be built ' with those words not found in the extra words ' table and returned as OutArray Dim arrOut As New ArrayList Dim blnFound As Boolean = False Dim strE As String For Each strT In arrIn   For Each strE In arrExtra     blnFound = False     If strT.ToLower = strE Then        blnFound = True        Exit For     End If   Next   If blnFound = False Then     arrOut.Add(strT)   End If Next OutArray = arrOut Return True 

Most of the words in a sentence are not meaningful and could be considered filler words. For example, words such as "the," "a," and "this." These words will be removed from the incoming question.

The StripQuestion function will also remove any extra characters, such as "*", "!", and "^". Some characters are converted to their English word equivalents. For example, the character "%" is changed to the word "percent." Once the question has been stripped, it is split and each word is stored in an ArrayList object. This ArrayList will be used as an incoming parameter for the next executed function, ApplyThreshold.

Responses are grouped into broad categories, and the corresponding table reflects the category name. For example, the table that contains question responses about automobiles is named ResponseAutos. Data for any similar question will be stored in one of the Response tables seen in Figure 7.1.

ApplyThreshold is viewable by right-clicking the Survey.vb file in Solution Explorer and clicking View Code. Select ApplyThreshold from the right-hand drop-down box. The ApplyThreshold function, seen as follows, will first execute a SQL query that returns all the fields from any of the tables that have a name starting with the word "Response."

 'First get all the fields in the database that are dedicated 'to question responses. This list represents the potential 'field names that our incoming question may match to. Dim strSQL = "SELECT sc.name as fieldname, so.name as tablename " strSQL += "FROM syscolumns sc " strSQL += "LEFT JOIN sysobjects so ON sc.id = so.id " strSQL += "where sc.id IN " strSQL += "(select id from sysobjects where xtype = 'U' " strSQL += "and substring(name, 1, 8) = 'Response' " strSQL += "and name <> 'Response') " strSQL += "AND sc.name <> 'Responseid'" Dim blnMatchFound As Boolean = False Dim arrFieldName As Array Dim strField As String Dim intNumMatchingWords As Int16 = 0 Dim arrMatches As New ArrayList Dim dblThreshold As Double = _ Convert.ToInt16(ConfigurationSettings.AppSettings("ThresholdPercent")) _                    * 0.01 dr = SqlHelper.ExecuteReader(AppSettings("Chapter7.Connection"), _     CommandType.Text, strSQL) Do While dr.Read   arrFieldName = Convert.ToString(dr.Item("fieldname")).Split("_")   intNumMatchingWords = 0   'Now, see how many words in our incoming array   'match with the words in the field name   objWord = New Microsoft.Office.Interop.Word.ApplicationClass   objWord.Visible = False   For Each strField In arrFieldName     If CheckSynonyms(InArray, strField.ToLower) = True Then        intNumMatchingWords += 1     End If   Next   objWord.Visible = True   objWord.Quit()   objWord = Nothing   'If the number of matching words divided by the number   'of available words exceeds the threshold percentage   'specified, then we consider a match to be found   If intNumMatchingWords / _     (arrFieldName.GetUpperBound(0) + 1) > dblThreshold Then      blnMatchFound = True      'Lookup the question so we can return it to the user      strSQL = "SELECT question FROM Questions WHERE ResponseTable = '"      strSQL += dr.Item("tablename") + "' AND ResponseField = '"      strSQL += dr.Item("fieldname") + "'"      dr2 = SqlHelper.ExecuteReader(AppSettings("Chapter7.Connection"), _        CommandType.Text, strSQL)      Do While dr2.Read        CSurvey.ResultLogDetailEntry(LogID, "QM", 0, _           dr2.Item("question"))        arrMatches.Add(Convert.ToString(dr2.Item("question")))      Loop      dr2.Close()    End If Loop dr.Close() Dim strMatch As String If arrMatches.Count > 0 Then  'We have at least one potential match, so we need to format the  'return message for the user    RetMsg = "The following questions have been found as potential "    RetMsg += "matches in the database. <br> If you wish for the "    RetMsg += "question to still be considered, then click "    RetMsg += "'Consider Question'. <p>"    For Each strMatch In arrMatches       RetMsg += strMatch + "<br>"    Next End If Return blnMatchFound 

The ApplyThreshold function reads the threshold percent value from Web.config and proceeds to loop through all the potential field names. Individual words in the field names are separated with an underscore character (e.g., often_purchase_automobile). Therefore, the field name is split into an array of individual words. This array is looped through to compare each word in the field name against each word in the incoming question.

Since most words have several meanings, it is necessary for the application to check each word against a thesaurus. The CheckSynonyms function executes routines from the Microsoft Word API. It first looks to see if the word matches exactly. If it does, it just exits the routine. Otherwise, it will create an instance of a Word document.

Note

To execute the code in this chapter you will need to install the Microsoft Office XP Primary Interop Assembly. The install file is available for download on MSDN. Once downloaded, you will need to execute the oxppia.exe file to install the XP Interop.

Since the Word API is COM-based, the XP Interop allows unmanaged code to be called from a managed environment (.NET). There are assemblies available for each application in Microsoft Office. Refer to the Readme.htm file available with the Interop install for instructions on installing the Word interop assembly.

The text contained in the new document is the incoming field name. A SynonymInfo object is populated with the results of the SynonymInfo property.

Dim objSyn As SynonymInfo = objDoc.Range.SynonymInfo 

The newly created object, objSyn, now contains all the possible meanings. If the MeaningCount property is greater than zero, we will use the SynonymList property to return an array of these meanings. We will then loop through that array looking for a match, such as:

 Dim arrList As Array = objSyn.SynonymList(1) For i = 1 To UBound(arrList)    If InArray.Contains(arrList(i)) Then        blnFound = True        Exit For    End If Next i 

The CheckSynonyms function will return a true value if the field name exactly matches a word in the question or a synonym for one of the words. The variable intNumMatchingWords will be incremented each time this function returns a true value. We will then divide the variable by the total number of words in our field name array, such as:

 intNumMatchingWords/arrFieldName.GetUpperBound(0)+ 1) > dblThreshold 

If the number of matching words divided by the number of available words exceeds the threshold percentage specified, we consider a match to be made. In this case, the value of dblThreshold is .70. So if at least 70 percent of the meaningful words match an existing field, we retrieve the question text for that field and populate an outgoing array list.

To demonstrate the Threshold matching functionality, execute the application by hitting F5 and click New Question from the left-hand menu. Type the question "How often do you buy a car?" and click Next.

Note

Readers familiar with Microsoft tools may wonder why Microsoft's English Query was not used to interpret the English questions. English Query is a tool available with SQL Server 2000. It allows database administrators to build an XML-based domain file using a front end based in Visual Studio 6.0. The domain file contains rules for applying language to specific fields in a database. Once defined, the file can be used by Web pages and Windows applications to query a database with natural language queries.

The main reason it was not used in this chapter is that it only generates a query statement and cannot be used as a language interpreter. There is also no .NET interface or support available for this tool.

The database file, available for download, has four fields defined in the ResponseAutos table. In this case, the ApplyThreshold function will find that the field named often_purchase_automobile is a potential match (see Figure 7.4). It considers the match to be 100 percent because three matching words were found. Buy is a synonym for purchase, and car is a synonym for automobile.

Figure 7.5. Screenshot of the new survey question page. In this case the question entered matches with an existing field in the database. The user is notified about the match and given the option to still have the question considered.

If the question was changed to "How many times do you buy a car?" the ApplyThreshold function would not consider the question a match. In that case, the number of matching words would be two. When you divide this value by three, or the number of potential matches, the result is 0.6667.

Note

The threshold percentage value is easily modifiable by editing the Web.config file. It is not necessary to recompile the application every time this value is changed. It could also be fed from a database table. It may be necessary to either raise or lower the threshold value in order to attain better results.

Condition Matching Phase

Once it is determined that no match exists, the decision is made to add the new question to the database. The system still must determine where in the database to place the question and what kinds of fields will be required. This is where the rule-based process comes into play. The first phase of the process is the Condition Matching phase. This involves examining all the rules and determining whether the condition is satisfied. If it is, then the rule will be added to an array list and passed to the next phase as a parameter.

The function that performs this phase is located in the Rule.vb file and is named ConditionMatchPhase. It utilizes functions and types from the System.Reflection namespace to programmatically invoke the condition method for each rule. In essence, this function utilizes .NET's reflection capabilities to translate text descriptions into references of methods and parameters.

The first thing this function does is check to see whether the rule is a default. Default rules are rules that are executed if no other matching rule has the same result method. If the rule is a default rule, it is added to the outgoing array list. Otherwise, the condition method name for the rule will be invoked. The code to accomplish this is as follows:

 Dim objType As Type = objAssembly.GetType("Chapter7.BLL.CRule") Dim Method As MethodInfo = objType.GetMethod(dr.Item("ConditionMethod")) Dim Obj As Object = objAssembly.CreateInstance(objType.FullName) Dim MethodParameters(2) As Object If Not Method Is Nothing Then    MethodParameters(0) = CType(dr.Item("ConditionValue"), String)    MethodParameters(1) = CType(Question, String)    MethodParameters(2) = CType(Login, String)    Dim blnMatch As Boolean = Method.Invoke(Obj, MethodParameters)    If blnMatch = True Then       OutArray.Add(dr.Item("RuleID"))    End If End If 

This code will create a type object to store a portion of the compiled assembly for this chapter. The path "Chapter7.BLL.CRule" is included to restrict which group of methods is returned to objType. It then populates a MethodInfo object with the results of the GetMethod method.

Tip

To be consistent, each method is passed three parameters, ConditionValue, Question, and Login. Since the Login parameter is used only by a particular condition method, it is not necessary to pass it to all the invoked routines. The code could be modified to programmatically determine which parameters should be passed to the invoked routine.

The CRule class contains two condition methods named QuestionContains and EmployeeEntered. QuestionContains determines whether the incoming question contains one of the words or phrases specified in the rules condition value. Since there may be more than one value specified, it uses the Split function to move all the words or phrases into an array. It then loops through this array, and if anything matches, it returns a true value. The EmployeeEntered function returns a true value if the user's login matches the employee specified in the rule.

To step through the code for this function, set a breakpoint at the start of the function by setting the cursor on a line of code and hitting F9. Type in the question "How many times do you buy a car in 10 years?" and click Next. Hit F10 or F11 as you step through the code and examine the variable values. Before exiting the function, type "? Outarray" from the Command Window and hit enter. You should get the following result:

 {Length=7}     (0): 1 {Integer}     (1): 4 {Integer}     (2): 5 {Integer}     (3): 10 {Integer}     (4): 17 {Integer}     (5): 2 {Integer}     (6): 12 {Integer} 

The OutArray variable contains the RuleID value for all the rules where the condition method returned a true value. Note that in this case, six rules were found as matches. Table 7.2 lists the conditions associated with each of these rules.

Table 7.2. Conditions for rules found as matches when the question "How many times do you buy a car in 10 years?" was entered.

Rule ID	Rule Description	Condition	Is Default?
1	Integer	If question contains the word or phrase "how many."	No
4	Linda Notify	If question was entered by the following employee, Linda Wright.	No
5	Default Data Type	No condition specified because this is a default rule.	Yes
10	Linda Table	If question was entered by the following employee, Linda Wright.	No
17	Default Table	No condition specified because this is a default rule.	Yes
2	Auto Table	If question contains the word or phrase "car," "auto," "automobile," "vehicle," "sedan," "van," or "truck."	No
12	Purchase Table	If question contains the word or phrase "purchases," "purchase," "buy," "finance," "financing," or "assets."	No

Two of the six rules retuned by the Condition Matching Phase are defaults. The other four were found because the condition method returned a true value. The condition methods supplied with the sample application are simple. Keep in mind that a production rule-based system may involve longer and more complex routines.

Handling E-mails with Rules

Rule-based applications can be very effective when it comes to automatically managing messages. Microsoft Outlook includes its own rule-based system in the form of the Outlook rules wizard. This wizard allows users to define the rules and actions used to handle all incoming e-mails. Rules can be applied at either the client or the server level.

A wide variety of third-party tools provide general message-processing capabilities. Some of them can be used to eliminate junk e-mail, create custom replies, move message text into databases, or just archive old e-mails. There are also tools that provide specific functionality, such as creating contacts for people you send e-mails to or automatically saving attachments to folders based on different rules.

For companies that want to create their own custom rules, Microsoft Outlook 2000 and above allow you to create your own custom rules using Visual Basic for Applications.

If you are a Microsoft .NET developer, then there is a good chance you already have access to Microsoft Outlook. To access the rule wizard from a Outlook 2003 client, go to Tools and Rules and Alerts. The tab labeled E-mail Rules shows the rules that will be applied to your messages in the order they are shown. New rules can be created using a template, or you can start with a blank rule. Rules created from templates allow you to do predefined things, such as move messages to a certain folder if they are from a particular person or contain certain words in the subject.

To create blank rules, the wizard walks you through a series of steps in which you specify the condition for the rule to be applied along with the action it will take. You then name the rule something meaningful to you and choose to apply it to all existing messages or just those going forward.

The Outlook rules wizard is specific in that it is used to handle a limited number of conditions and actions. It is restricted to handling messages for Microsoft Outlook only. In this case, the expert is the individual Outlook user and the domain of expertise is Outlook messaging.

The rules wizard is a good example of how a rule-based system can work. If you have never used the rules functionality in Outlook, you might want to look at it before developing your own rule-based system. It may give you some ideas about how to create an easy-to-use interface with which users can create and apply rules.

Priority Evaluation Phase

Typically, the Condition Matching phase will return several matching rules. Since the actions of these rules may override each other, it is necessary to resolve any conflicts. The administrator is able to assign numeric priority values to the rules as they are defined in Rule Maintenance. These priority values will be used to rank rules according to which are most important.

The numeric value can be any positive value from 1 on up. If a rule is so important that it is always to be executed when the condition is true, it should be assigned a value of 1. Other rules that execute the same result method should be assigned a higher priority value.

Tip

The Rule Maintenance page provided with the sample application is very simple. It would be helpful if the administrator had a page that displayed the priority hierarchy for rules associated with a specific result. You may want to consider this if you design your own rule-based system.

In the last section, six rules had matching conditions. Table 7.3 lists these rules along with the priorities and result method names assigned to each.

Table 7.3. The six rules identified in the last section along with the priority and result method names assigned.

Rule ID	Rule Description	Condition	Priority	Result Method	Is Default?
1	Integer	If question contains the word or phrase "how many."	1	FieldType	No
4	Linda Notify	If question was entered by the following employee, Linda Wright.	1	E-mail Notify	No
5	Default Data Type	No condition specified because this is a default rule.	1	FieldType	Yes
10	Linda Table	If question was entered by the following employee, Linda Wright.	1	Question Category	No
17	Default Table	No condition specified because this is a default rule.	1	Question Category	Yes
2	Auto Table	If question contains the word or phrase "car," "auto," "auto-mobile," "vehicle," "sedan," "van," or "truck."	2	Question Category	No
12	Purchase Table	If question contains the word or phrase "purchases," "purchase," "buy," "finance," "financing," or "assets."	3	Question Category	No

Note that the result method named QuestionCategory appears four times in the table. Since the result method performs a distinct action, having three rules execute the same result method could cause a conflict.

The code for this phase exists in the CRule class and is named PriorityEvaluationPhase. The function executes a SQL statement that queries the rule base and orders the results according to how they should be evaluated. If executed, the function would build the following SQL statement to process the six rules listed in Table 7.3.

 SELECT ruleid, resultmethod FROM [rule] r LEFT JOIN ruletype rt on r.ruletypeid = rt.ruletypeid WHERE ruleid in (1,4,5,10,17,2,12) ORDER BY resultmethod, isdefault, conditionpriority 

The SQL statement would return the results seen in Table 7.4.

Table 7.4. Query results obtained when the SQL statement that queries the rule base and orders the results according to how they should be evaluated is executed.

	Rule ID	Result Method
	4	E-mail Notify
	1	FieldType
	5	FieldType
	10	QuestionCategory
	2	QuestionCategory
	12	QuestionCategory
	17	QuestionCategory

As the results are read, the program will add the first rule with a distinct result method to the outgoing array. So the function will return rules 4, 1, and 10. It will ignore rules 2 and 12 because they have a higher priority value. Rules 5 and 17 will be ignored because they are default rules. The default rule is only applicable when no other rule with the same result method is found.

Adjustment Phase

The final phase, the Adjustment phase, is the most important. It involves the evaluation of decisions made by the application. Decisions are based on the definition of rules and rule types. If the administrator determines that a field was added incorrectly, it may be necessary to adjust a priority or to add a new rule.

The ResultLog and ResultLogDetail tables are used to record decisions made during the first three phases. A record is inserted into the ResultLog table immediately following the StripQuestion function. Child records are inserted into the ResultLogDetail table every time a decision is made or an error is encountered.

The ResultLog table contains the question text along with the id of the employee who entered it. It also contains a date/time stamp and the name of the new field created. If an error is encountered, a new child record is inserted into the ResultLogDetail table along with the error message. Whenever a question is entered and the application determines that a question already matches it, a record is inserted with the value "QM" (Question Matched) in the Result field.

For each rule that is found to have a matching condition, another record is inserted into ResultLogDetail. These types of records are indicated with the value "CM" (Condition Matched) in the Result field. For each rule in which the result method is invoked, another record is inserted with a value "RA" (Result Applied) in the Result field.

Tip

Although not included with the sample application, it would be helpful for administrators to have access to a report style interface for the Result log. You may want to consider creating such an interface if you are designing your own rule-based system.

Administrators can periodically review the records in these tables to determine how the application arrived at certain decisions. Queries like the one that follows can be issued using SQL Server's Query Analyzer.

SELECT rl.resultlogid, rl.question AS [Question Entered], e.firstname + ' ' + e.lastname AS [Entered By], entereddate, rld.question AS [Question Matched] FROM resultlog rl LEFT JOIN resultlogdetail rld ON rl.resultlogid = rld.resultlogid LEFT JOIN employee e ON rl.enteredby = e.employeeid WHERE result = 'QM' 

The preceding query returns all the log entries where the question entered matched a question already in the database. Table 7.5 lists the query results obtained if you execute the program and enter the question "How often do you buy a car?"

Table 7.5. Query results obtained when the program is executed and the question "How often do you buy a car?" is entered. In this case the question "How often do you purchase an automobile?" already exists in the database and is seen as a matching question.

Result Log ID	Question Entered by User	Employee That Entered Question	Entered Date	Matching Question Already in Database
48	How often do you buy a car?	Linda Wright	2004-05-04 09:45:00	How often do you purchase an automobile?

In the last example, the application made an appropriate decision and no adjustment is necessary. Consider, however, the following question, "In the last 3 years have you had at least 1 car note?" If the question "In the last 3 years have you had more than 1 car note?" already exists in the database, the application will consider the two questions to be a match. In this case, the questions are similar but will collect entirely different answers from survey respondents.

Administrators who wish to restrict the number of fields in the database would be pleased that the application made the preceding decision. If, however, the administrator prefers to allow the alternative question, then an adjustment of the Threshold percentage is necessary. If the value is changed from 70 to 90 and the question reentered, a different result will be obtained. In the case of a 90 percent threshold percentage, an existing match will not be found and the question will be added to the ResponseAutos table.

The number of adjustments will depend on the expectations of the experts controlling it. Initially, one should expect quite a few adjustments. The number of adjustments will decline as the application is utilized and input variances are identified.