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.
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:
Source | Validation test | Reference | Operand | Condition |
---|---|---|---|---|
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.
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:
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.
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.
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 |
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).
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.
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.