email article printer friendly get the code

Match by Exception
Create more flexible, data-driven solutions with the aid of a match by exception design pattern.
by Joel Champagne

October 23, 2006

Technology Toolbox: VB 2005, SQL Server 2005, SQL Server Express Edition

An almost universal requirement faced by those who create data-oriented applications is the need to locate records that match a given set of criteria.

The criteria by which you match can take a variety of forms. For example, the criteria can be simple: "Find me a Customer with ID = 42." Sometimes the structure of the request can be complex: "Find me a business rule record in a table that is more specific than any other record, given multiple matching fields." A complex request doesn't require complex code by necessity. Nor does such code have to perform poorly. In fact, there's a general class of problems that you can solve efficiently using a design pattern that might best be described as matching by exception.

I'll show you how to implement such a design pattern, using a billing system as an example. I want to stress that this technique is by no means limited to accounting. If you encounter a situation where it becomes clear that you have some conditions that drive the selection of data—whether these conditions revolve around rates, rules, templates, or something else—and you're finding maddening exceptions to these selection rules, this approach can keep you from wanting to pull out your hair. There are many creative ways to apply matching by exception as part of the broader principle of data-driven design, and I've seen it used to simplify greatly what might otherwise have been an ugly solution. For example, I remember a situation where goods would move between a set of locations that were represented as multiple records containing "from" and "to"—effectively specifying a route. But these routes differed depending on the service, the customer type, and various other criteria. It was dealing with the exceptions that was tedious, but I was able to offload this aspect of the problem to business analysts because the solution was data-driven.

The billing system described in this article assumes you work for a theoretical company that invoices dozens of different customers. Suppose you discover that rates are set based on customers' location, type, and some attributes of the services being invoiced. Maintaining a Cartesian product of all possible combinations would be unmanageable in all but the most trivial cases, even though the SQL code to locate these records would be simple and fast. Another approach might be to build multiple tables to account for different levels of matching. Unfortunately, this can lead to complex, conditional processing logic at the expense of efficiency and maintainability.

A better approach is to leave the concept of a rate table as flexible as possible. This enables you to accommodate changes in rates themselves, as well as accommodate changes in how you set the rates. Under this approach, you focus on building an engine where rate records can be selected based on either specific or general needs. You then codify the set of parameters by which you define the records, not the parameter values. The end product is a table with much fewer records. Note that such a table might still be fairly wide; that is, it can have a large number of columns. You can use this approach to model underlying business truth closely. You simply model the conditions that are meaningful to the business. In some cases, a business will dream up new conditions when it has the kind of flexibility this approach offers.

In addition to explaining how to implement a matching by exception design pattern, I'll also cover the practical ramifications of this solution using a VB.NET test harness to illustrate different options for using the engine. For example, you'll see how the test harness is designed to operate against both saved and unsaved source data.

Build a Data Model
Your first step is to create a data model for the sample application. This data model contains different types of entities. One type consists of instances of services performed for customers. This is the CustomerService table. The data model includes code tables such as Customer, CustomerType, ServiceType, ContainerSize, WeightClass, Location, and SubLocation. Each table provides descriptions and other rich metadata. The latter two tables have a hierarchical relationship, as well. Finally, the Rate table serves as your source of matching, ideally producing exactly one RateAmount value for every invoice line (see Figure 1).