TABLE-DRIVEN VALIDATION DESIGNS
This third post in the Better BPM series on Interaction Profiling presents a generic design for the persistence of the validation rules required in a business application in the application database. This post introduces the simple designs that enable Users to maintain their own validation rules directly almost all of the time, with no support from IT, yet still provide an extremely powerful validation capability which will be included in an Interaction Profiling framework that can be re-used any number of times in custom application development.
Clearly, to perform detailed field validation, a system must contain the validation rules for each application field. Also, we need to be able to provide Profile-based overrides where necessary at the individual Interaction Profile – Application Field level. The actual validation capability can be simple or sophisticated, but the more sophisticated the implementation, the more involvement from IT support is likely to be required to maintain the validation metadata. This is a tricky judgement call and often will depend upon the level of sophistication of the supervisory Users who will be tasked with maintaining the validation metadata. Some, limited IT involvement is quite acceptable in most organizations but the simpler the validation, the greater the savings on maintenance costs.
The prototype system used to exemplify the designs in this paper includes a fairly sophisticated validation capability, even using regular expressions, which would typically be beyond most power users outside of IT. However, the need for complex regular expressions in validation will be relatively rare and it is possible to train some Users at least to maintain the more simple expressions by providing training and reference materials for any fields (such as telephone numbers, email addresses etc.) that may occur in a production implementation.
Figure 2 presents the table-driven validation design embodied in the prototype system. This partial diagram is extracted directly from the SQL Server 2008 database. Click the diagram to see a clearer image.
We see the previous Profile definition tables (4) plus two validation metadata tables, the ApplicationField table and the ProfileFieldRule validation overrides for individual Profiles where required. The ApplicationField table contains the definition of any field that either requires validation, or is included as a Profile Attribute. This is not as onerous in real life as you may think, as was discussed in the first post in the Interaction Profiling series. Large applications (in data model terms) with appropriate validation will require most fields to be defined in this table. This could result in as many as a few hundred field rows, which is still relatively small in size. The ProfileFieldRule table contains rows only where a different validation rule set is necessary for a specific application field, when it occurs on a transaction that matches the corresponding Profile. Multiple profiles may match a specific transaction and the Profile table Precedence field is used to resolve any possible conflicts automatically. This situation is rare, especially if the set of interaction Profiles is well constructed, when typically there are no conflicts of this nature.
Two other tables appear on the diagram requiring clarification. The prototype design is multi-interaction. Although the system (a hypothetical Sales Order Processing System) only contains one interaction (Sales Order) that is tracked by the application, the design was created as multi-interaction so it could be expanded if necessary to include other business interactions, such as Invoices or Payments. Typically most transaction processing applications are focused on a single interaction, but a flexible, extensible design is always a good idea as long as it does not introduce too much unnecessary processing overhead or complexity.
The last table shown, SalesOrderProfile, is the repository for the current set of matching profiles for each transaction (in the prototype, Sales Orders). This is a performance assist, as we only need to update this information when interaction data changes occur and there are many occasions when the Interaction Profiling metadata is processed when data is not changed and this avoids the overhead (which is small anyway) of having to perform the profile matching logic unnecessarily. The profile matching logic in the prototype is embodied in a single function with related data retrieval in a single stored procedure. This generic code never changes once fully tested and implemented. All of the validation logic and the BPM task logic is stored in the database and maintained by the Users directly with minimal support from IT. There is NO application code anywhere in the prototype system that hard codes any logic for deriving the rules at runtime for all of the many different types of business transactions. The Interaction Profiling database metadata controls everything in this regard. Compare this to most of the applications you may be familiar with, where such detailed logic is scattered all over the place in code or in lengthy, complicated process flow diagrams.
The prototype validation capabilities exposed by the data content on the ApplicationField and ProfileFieldRule tables are quite extensive and would handle almost all requirements in the vast majority of applications. Given there is an intrinsic facility to call an IT-provided function or a SQL statement (or VBA code, as explained in a later post), there is really no limit to the validation capabilities of the Interaction Profiling framework embodied in the prototype application. Specifically, the validation incorporates checking for the data type, boundary values (max and min), existence (optional or mandatory), special case requirements (upper / lower), SQL lookup validation / statement execution, calling a validation function, plus regular expressions for any amount of sophisticated character checking and even search and replace functionality. Although the primary purpose of the prototype system is to substantiate and confirm the framework designs and check the feasibility of the Interaction Profiling concepts, the validation functionality would handle almost any real world validation requirements that could be envisaged.
Previous Post: Better BPM – 2 Interaction Profiling Definitions and Design
Next Post: Better BPM – 4 Table-Driven BPM Designs