Zoom Lending

Zoom Lending is a fictional finance company that specializes in refinancing and second mortgages. It has more than two hundred offices nationwide, situated in areas of diverse socioeconomic levels. Each office is responsible for soliciting business from past customers. To do this, it often collects information about borrowers that goes beyond the data on their loan applications. Since the offices are located in different socioeconomic areas, the questions asked of borrowers can be quite different.

Initially, the company created one form that contained every possible question, and the employees were expected to only enter data that was applicable. It was soon found that they were not using the form. There were several reasons for this. First, the form was too long and time-consuming to utilize. In addition, branches were often interested in collecting data that was not available on the form.

A Web-based application was developed to build separate surveys for the various branches. The branch manager would select questions applicable to the branch. If employees wanted to add a new question, they would send an e-mail to the DBA. The DBA would add the question as a field in the database and then notify the employee. This process usually took one or two days, and sometimes, as a result, data was not collected.

To increase application efficiency, rule-based functionality was added to the application. If an employee wanted to add a new question, the application would first search the database for a match. If none was found, it would apply rules to determine where and how the field should be added. For instance, if a question contained the phrase "How many," the field would be defined as an integer. If the question contained the word "vehicle," it would be added to the ResponseAutos table.

Log files are written to every time an employee tries to enter a new question. Periodically, the DBA examines the log files to evaluate the application's success. If a field was added incorrectly, the DBA may need to make adjustments to the rules. This chapter will examine the code used to implement the rule-based functionality.

Loading the Zoom Lending Database

Zoom Lending utilizes a SQL Server database named ZoomLending. To execute the sample code available for download from the book's Web site at http://www.awprofessional.com/title/0321246268, you will first need to attach a database file to your instance of SQL Server. To do this, execute the following steps:

1.	Copy the ZoomLending_Data.mdf and ZoomLending_Log.ldf files from the book's Web site to a local directory on the server where Microsoft SQL Server is installed.
2.	Open Microsoft Enterprise Manager.
3.	Right-click the Databases node and click All Tasks. . . and Attach Database. . .
4.	From the Attach Database dialog, browse to the directory where you copied the database files in step 1 and select the ZoomLending_Data.mdf file. Click OK.
5.	Click OK to attach the database.
6.	To see the newly added database, you will need to click Action and Refresh.

Since Zoom Lending is a finance company, there are several tables designed to collect borrower data. The data in these tables consists of information collected on a loan application. The tables utilized by the rule-based functionality are displayed in Figure 7.1.

Figure 7.1. Tables from the ZoomLending database. These tables are the ones central to the rule-based functionality discussed in this chapter. The Response tables contain borrower data collected from branch surveys. They will be expanded as new questions are added to the database.

The RuleType table is used to group rules according to generalized conditions and results. The IF. . .THEN part of the rule is contained in the ConditionText and ResultText fields. It also contains placeholders for parameters. The Rule table is a child of the RuleType table. Values for the parameters used in the RuleType table are stored in the ConditionValue and ResultValue fields. We will cover defining rules in the section titled "Rule Maintenance."

The Response tables are written to each time a borrower responds to a survey. For each record in the Questions table there is an associated field in one of the Response tables. The application relates the question text to the response tables through the ResponseField and ResponseTable fields.