Business rules

Business rule are the other primary source for data rule analysis. Applicable business rules might already exist or they can evolve as data analysis progresses to more complex criteria.

Typically, a business rule in its data rule form will be designed to evaluate whether an expected end-state of data exists following some business or system process (for example, the proper capture of customer information following the enrollment of a new customer). Types of data rules created from business rules include:

Valid Value Combination

An example of a valid value combination might be certain medical types of service that can only be performed at certain places of service and must be compatible with certain city, state, and zip code for USA addresses. One of the most common types of business data rule is one that validates the combination of data values stored in multiple columns within a logical record. There are many business policies and practices that can be represented by the combination of data values in more than one column. Usually the valid combinations of data values represent expected end-states of data based on a business system process.

Table 1. Example of valid values combinations
Type of Service	Place Of Service	Validity
Surgery	Hospital	Valid
Surgery	Ambulance	Invalid
Xray	Hospital	Valid
Xray	Pharmacy	Invalid

There are generally two ways to develop a valid value combination rule. The first is to detail the combinations within the data rule logic:

Table 2. An example of one way to develop a valid value combination rule
Source	Validation test	Reference	Operand
TYPE OF SERVICE Column	=	SURGERY	AND
PLACE OF SERVICE Column	=	HOSPITAL	OR
TYPE OF SERVICE Column	=	XRAY	AND
PLACE OF SERVICE Column	=	OUTPATIENT

A second approach that might be more efficient when there are a large number of combinations is to use an external table to store the valid values combinations. The external table can be created by:

Making a virtual column from all the columns in the combination
Running Column Analysis to create a frequency distribution of the virtual column values (that is, the combinations)
Marking each combination in the frequency distribution as “valid” or “invalid”
Creating a reference table of valid values (that is, combinations) from the frequency distribution
Using the reference table in a valid values combination data rule

Given a reference table, the validation can be performed in a single line data rule.

Table 3. An example of a single line data rule with a given reference table
Source	Validation test	Reference	Operand	Condition
TYPE OF SERVICE Column + PLACE OF SERVICE Column	REFERENCE COLUMN	TOS+POS VALID VALUES TABLE		TOS + POS value combination matches a TOS + POS valid values combination in the reference table

Or, to find invalid value combinations:

Table 4. An example of a single line data rule to find invalid value combinations
Source	Validation test	Reference	Operand	Condition
TYPE OF SERVICE Column + PLACE OF SERVICE Column	NOT =	TOS+POS VALID VALUES TABLE		TOS + POS valid value combination does not match a TOS + POS valid values combination in the reference table

Computational

Another common type of business data rule is one that mathematically validates multiple numeric columns that have a mathematical relationship These computations can be in an equation form (for example, the hourly rate multiplied by the number of hours worked must equal the gross pay amount) or in a set form (for example, the sum of detailed orders must equal the total order amount). There are usually many prescribed computations among numeric columns in a typical database. These business defined computations can be verified by using a computational data rule.

Table 5. An example of a computational business rule
Source	Validation test	Reference	Operand	Condition
HOURLY RATE Column x HOURS WORKED Column	=	GROSS PAY AMOUNT Column		Validate calculation of the gross pay amount
SUM(DETAILED ORDER AMOUNTS Columns	=	TOTAL ORDER AMOUNT Column		Validate calculation of the total orders amount

The computations performed in a computational data rule can vary from simple to complex by creating data expressions that use the appropriate scalar functions and numeric operators.

Chronological (also called Ordered Values)

Business rules that validate time and duration relationships are known as chronological data rules. These rules can define chronological sequence (for example, a project activity date must be equal or greater than the project start date and equal or less than the project completion date) or chronological duration (for example, a customer payment must be within 30 days of billing to avoid late charges). These time-based relationships can be validated by using a chronological data rule.

Table 6. One example of a chronological business rule
Source	Validation test	Reference	Operand	Condition
PROJECT ACTIVITY DATE Column	>=	PROJECT START DATE Column	AND	Project activity cannot occur before start date
PROJECT ACTIVITY DATE Column	= <	PROJECT COMPLETION DATE Column		Project activity cannot occur after completion date

Table 7. Another example of a chronological business rule
Source	Validation test	Reference	Operand	Condition
PAYMENT DATE Column	<=	BILLING DATE Column + 30 days		Payments must be made in 30 days or less from billing

Conditional

Business rules that don't conform to a valid values combination, computational or chronological data rule are generally referred to as conditional data rules. These business rules typically contain complex if…then…else logic that might include valid values combinations, computational and chronological conditions (for example, if the customer has an active account, and the last order date is more than one year old then the catalog distribution code should be set to quarterly).

Table 8. Example of column property testing with this business rule
Source	Validation test	Reference	Operand	Condition
Customer Activity Code Column	=	ACTIVE	AND	Customer is active
CUSTOMER LAST ORDER DATE Column	>=	TODAYS DATE - 365 days	AND	Last order is within the last year
CUSTOMER CATALOG CODE	=	QUARTERLY		Customer is scheduled to receive a catalog every three months

Or, to test for non-compliance with this business rule.

Table 9. Example of non-compliance testing with this business rule
Source	Validation test	Reference	Operand	Condition
Customer Activity Code Column	=	ACTIVE	AND	Customer is active
CUSTOMER LAST ORDER DATE Column	>=	TODAYS DATE - 365 days	AND	Last order is within the last year
CUSTOMER CATALOG CODE	=	QUARTERLY		Customer is scheduled to receive a catalog every three months

There are endless possibilities for creating conditional data rules. Many business rules that form the basis for conditional data rules will already exist in the legacy of application systems. Others will evolve from the data analysis itself as more of the data's relationships and issues are revealed. You should strive to develop the most effective set of data rules applicable to the current situation.