Rules as Data: Decision Tables and Relational Databases

Jan   Vanthienen
Jan Vanthienen Professor in Information Management, K.U. Leuven Read Author Bio || Read All Articles by Jan Vanthienen

There is a striking similarity between decision tables and databases.  The way in which we model and represent sets of rules can benefit from the insights obtained in database design.  Good decision table design and good database design are subject to similar concerns, such as consistency, non-redundancy, normalization, etc.

Decision tables and relational tables

The decision table contains a set of rules (expressed as rows or columns) indicating which action(s) should be applied for which combination of condition values.  By analogy with functional dependencies in relational database design, the relationship between conditions and actions (or between conditions) in decision tables can be expressed as implications.  A more extensive treatment of this similarity between decision tables and databases and a number of interesting consequences (functional dependencies, primary key, normalization forms, factoring) can already be found in [1], [2], and [3].

The decision table, being a set of implications, can be described in terms of functional dependencies:

Given a decision table with conditions and actions, a set of actions is functionally dependent on a set of conditions if every combination of condition values corresponds to (one and) only one configuration of action values.

Conditions as key

This is the most important criterion when designing decision tables:  are columns mutually exclusive (single hit versus multiple hit)?  In a complete single-hit table, each possible combination of conditions can be found in exactly one (one and only one) row (or column).  If the rows are not exclusive, at least one combination of conditions is present in more than one row, which may lead to inconsistency.  If a combination of conditions is missing, then the table is incomplete.

Figure 1 indicates a decision table that is complete and consistent.  Condition combinations do not overlap, and every combination of condition values is present.

 

TypeOf
Order

Customer
Location

Discount

1

Web

US

Discount 10%

2

Web

Europe

Discount 5%

3

Web

Other

No Discount

4

Phone

US

Discount 10%

5

Phone

Europe

Discount 5%

6

Phone

Other

No Discount

Figure 1.  A complete and consistent decision table

It is intuitively clear that this decision table and a relational table are very similar.  But whereas the database table contains data, the decision table contains rules — rules as data.

The conditions are the key attributes and the actions the non-key attributes.  Possible condition values are the key domains, and action values are the non-key domains.  Condition and action entries correspond to attribute values.  Every rule is an n-tuple, and the number of rules of the expanded decision table is the cardinality of the relation.

Because conditions are the key attributes, and the key should be unique, condition combinations should be exclusive.

The whole key, and nothing but the key[4] 

But there is more to it.  It is clear in Figure 1 that the Discount action is only dependent on CustomerLocation.  Whatever the TypeOfOrder is, the result is the same.  So the discount is only dependent on part of the key, not the whole key.  The TypeOfOrder condition is therefore not necessary and can be omitted, as can be seen from the contracted decision table (Figure 2).

 

Customer
Location

TypeOf
Order

Discount

1

US

Discount 10%

2

Europe

Discount 5%

3

Other

No Discount

Figure 2. The contracted decision table

Normalization and Factoring of Decision Tables

Although there are major differences between decision table knowledge and database dependencies, the analogy is striking, such that the normalization rules of database design[5] provide an excellent guideline to evaluate the design and decomposition of decision tables.  Both normalization of relations and of decision tables have as a primary goal to avoid redundancy and to correct anomalies.  In addition, the normalization of decision tables simplifies decision tables and increases their readability.

Normalization rules for decision tables require that actions are fully dependent on all the conditions (2nd normal form) and that actions are non-transitively dependent on the conditions (3rd normal form).  Transitive dependencies have to do with relations between conditions.

These rules can then be used to improve the design and factoring of decision tables.

An Example

 

TypeOf
Order

Customer
Location

Known
Customer

Discount

Surplus

1

Web

US

Yes

Discount 10%

2

Web

US

No

No Discount

3

Web

Other

Yes

Discount 10%

Shipment
Surplus

4

Web

Other

No

No Discount

Shipment
Surplus

5

Phone

US

Yes

Discount 5%

6

Phone

US

No

No Discount

7

Phone

Other

Yes

Discount 5%

Shipment
Surplus

8

Phone

Other

No

No Discount

Shipment
Surplus

Figure 3.  Order Policy

If we examine the table in Figure 3, it can be seen that the value for Discount depends on TypeOfOrder and KnownCustomer, but there is no difference between the CustomerLocations.  So CustomerLocation is not part of the key.

The same holds for ShipmentSurplus.  The surplus is only applicable to non-US Customers, so it is only dependent on CustomerLocation.

Because the sets of conditions for the two actions do not overlap, the decision table of Figure 3 could be simplified into the two tables of Figure 4 and Figure 5.

 

Known
Customer

TypeOf
Order

Discount

1

Yes

Web

Discount 10%

2

Yes

Phone

Discount 5%

3

No

No Discount

Figure 4.  Discount policy

 

Customer
Location

Surplus

1

US

2

Other

Shipment
Surplus

Figure 5.  Surplus policy

References

[1]  J. Vanthienen and M. Snoeck, "Knowledge Factoring Using Normalization Theory," International Symposium on the Management of Industrial and Corporate Knowledge (ISMICK'93), Compiègne, FR, (October 27–28, 1993), pp. 97–106.  return to article

[2]  J. Vanthienen and G. Wets, "Integration of the Decision Table Formalism with a Relational Database Environment," Information Systems, Vol. 20, No. 7 (1995), pp. 595–616.  return to article

[3]  J. Vanthienen and J. Wijsen, "On the Decomposition of Tabular Knowledge Systems," New Review of Applied Expert Systems, (1996), pp. 77–89.  return to article

[4]  W. Kent, "A Simple Guide to Five Normal Forms in Relational Database Theory," Communications of the ACM, Vol. 26, No. 2 (Feb. 1983), pp. 120–125.  Online at :  http://www.bkent.net/Doc/simple5.htm   return to article

[5]  C.J. Date, An Introduction to Database Systems, 8th ed., Pearson/Addison Wesley, 2004.   return to article

# # #

Standard citation for this article:


citations icon
Jan Vanthienen, "Rules as Data: Decision Tables and Relational Databases" Business Rules Journal, Vol. 11, No. 1, (Jan. 2010)
URL: http://www.brcommunity.com/a2010/b516.html

About our Contributor:


Jan   Vanthienen
Jan Vanthienen Professor in Information Management, K.U. Leuven

Jan Vanthienen is professor of information management at the Business Information Systems Group of KU Leuven (Belgium), where he is teaching and researching on business rules, processes and decisions. The area of business rules modeling, validation and verification, and decision modeling in the context of business process modeling has been his major area of research and expertise for many years. He is a regular speaker at BBC, where his nickname seems to be: not (just) the decision table guy.

Read All Articles by Jan Vanthienen
Subscribe to the eBRJ Newsletter
In The Spotlight
 Ronald G. Ross
 John A. Zachman
';
The Issue Is THE ENTERPRISE By John A. Zachman Jan. 2017 | Vol. 18, Iss. 1

Online Interactive Training Series

In response to a great many requests, Business Rule Solutions now offers at-a-distance learning options. No travel, no backlogs, no hassles. Same great instructors, but with schedules, content and pricing designed to meet the special needs of busy professionals.