Articles in previous issues of TDAN[1] have discussed the extent to which business rules can be directly
represented by data models. To summarize our story so far, data models do use terms to represent concepts and facts. They can represent derived data (although not the derivation logic), and they
can represent a few constraints. There are a large number of constraints that they cannot represent, however. This article presents an approach to representing a category of constraints that cannot
be directly represented, but which can be managed in terms of data.
Implicit in the notion that a data model can describe a rule is the idea that the rule can then be directly implemented in a relational database. Thus, basic definitions and referential integrity
constraints are all in that category. The article in the last issue described extensions to e/r modeling (e.g., the Ross Method) and alternatives to it (e.g., ORM) that can capture some of the
additional constraints that interest us, but it is worth noting that none of these extensions suggest anything other than programming that would be required to implement them. These extensions
describe rules whose implementation would lie outside the database management system.
Rather than focusing on extensions to the notation, then, let’s take a moment to see what can be done to simplify any programming that might be required. The way to do this is to formulate any
rules we do encounter in terms of data manipulation, not simply program logic. Make the rules “data-driven”.
Example – Laboratory
The model
Figure 1 shows one kind of model that stymies our attempts to represent constraints. (This model was originally shown in previous articles in this series.) This is the basic laboratory model, where
each sample (from, say, a production lot in a factory, or from a lake in an environmental study) is drawn according to one and only one sample method (such as “fill a beaker”).
The sample method not only describes the manner in which the sample should be drawn, but also specifies the kinds of laboratory tests that can be conducted. Specifically, each sample method is
the basis for one or more test requirements, each being the requirement for a particular test type .
Meanwhile, each sample drawn according to the sample method is then subject to one or more tests to determine its characteristics. Each test, in turn, is an example of a single
test type, that defines the characteristics of the test(such as “pH”, “viscosity”, etc.).
The data constraints
This model is subject to the following business rule:
Business Rule:
- A test may only be given if it is an example of a test type that is one of those approved for samples drawn according to a particular sample method. That is, a test
on a sample may only be given if it is an example of a test type that is the object of a test requirement from the sample method that is used to draw
that sample.
There is nothing in the model to describe that rule. Looking only at this model, there would be nothing to prevent us from specifying a test that is an example of a test type that has not
been approved for the sample method. Clearly some program logic will be required around any system based on this model.
But note, however: the language of the rule is fully present in the diagram even if the actual constraint itself is not. This is significant. The rule is data-driven.
That is, the program logic will not have any “hard-coded” values. It will be described entirely in terms of the data in the database.
The remaining programming required
This means that the program logic will have to know how to navigate the model. When a test is specified, it must enforce the requirement to specify a test type. Given a sample and a proposed test
that is an example of a test type, the program then:
- navigates the database to determine which sample method applies to the sample being tested.
- navigates the database a second time (via test requirement) to determine which TEST TYPES are allowed.
- compares the sample method used for the sample with the list obtained from the second navigation to determine whether a test of this test type is allowed.
- if so, creates the test, linking it to the appropriate sample and test type.
Example – Accounting
Accounting is a very complex field, largely because of the large number of rules that apply to it. These are on several levels:
- Rules that are part of the fundamental nature of double-entry bookkeeping.*
- Rules that are imposed by governmental agencies to ensure that the books are honest.
- Rules that are established by the company to reflect its own operations and constraints.
Any model of accounting must intrinsically reflect the structures from the first category. What follows in this article is a model that reflects the underlying structure of double-entry
bookkeeping.[2]
The second and third set of rules, however, tend to change over time. It is important, therefore that these not be reflected in the structure of an accounting data model. Instead these should be
described in the data supplied to a resulting system. This makes for a very good example of data-driven rules.
The Model – Part I
Figure 2 shows the beginning of a data model for a general ledger system. It introduces the basic terms required to understand double-entry bookkeeping. An account is an enumeration of funds
received or spent by an organization. This enumeration is in terms of both a category and a portion of the organization that is involved. That is, each account is an example of a specific
account type (such as “travel expenses”) and for a cost center (such a the Data Management Department).
An account may be evaluated with one or more account balances. Each account balance is a single value for the account at a particular time. Note that both actual balances and projected
balances are shown.
Accounts are of three types:
- Asset Account – What the company physically consists of. An asset account describes money or value held by the company.
- Liability Account – Debt owed by the company. A liability account describes money that is owed to someone else.
-
Equity Account – Ownership of the company. An equity account describes a category of ownership, such as “common stock” or “retained earnings”. Since revenues
and expenses are additions to and subtraction from the company’s equity, they are also included in this category.
Note that the sub-types of account are also reproduced as the first three instances of account type: “Asset account”, “Liability account”, and “Equity account”. Each of these, in turn may be
a super-type of two or more other account types, such as “accounts receivable”, “cash”, etc.
Data Constraints – Part I
This is just the beginning of the accounting model. At this point, there are three constraints beyond the usual requirements of mandatory relationships:
Business Rules:
- For an account, at any point in time, only one occurrence of balance is active. The total value of the company may be determined by examining all the balances that are active at a point in
time. - The first three account types must be “Asset”, “Liability”, and “Equity”. After that, other account types may be defined that are sub-types of these.
- (At a particular point in time) the sum of balances for all asset accounts must be equal to the sum of balances for all liability accounts plus the sum of balances for all equity accounts.
That is, the total value held by the company must be the sum of what was borrowed and what is owned.
Remaining Programming Required – Part I
The first rule can be enforced by the process that handles balances. The second rule is met when the data are set up initially.
The third rule implies that upon each update, the sum of asset balances, liability balances, and equity balances must be calculated and compared. In fact, as we will see below, this constraint can
be met by properly controlling the transactions that affect these balances as they are entered.
The Model – Part II
Figure 3 shows that, in order to maintain the balance described above, each accounting transaction has at least two parts. An accounting transaction must be composed of one or more debit entries
and it must be composed of one or more credit entries.
Debits and credits are defined as follows:
- A debit entryincreases the balance of an asset account or decreases the balance of a liability or equity account.
- A credit entrydecreases the balance of an asset account or increases the balance of a liability or equity account.
Debit entry and credit entry are sub-types of the more general concept accounting entry. Each accounting entry (of either kind) must be to update an account. That is, each accounting entry
represents the creation of a new balance of the account by adding to or subtracting from the previous balance. The new balance, then, must be based on exactly one accounting entry. Each
balance has a “Date” when it was entered and by definition the occurrence is active until the “Date” of the next one.
Data Constraints – Part II
To maintain the equivalence between assets on one side and liabilities and equities on the other, every accounting transaction must either update both sides (asset or liability plus equity)
equally, or add and subtract a single Amount from one side. To do this, the following rules have been defined:
Business Rules:
3. Debits and credits are defined as follows:
– The “operator” for a debit entry must be “plus” when affecting the balance of an asset account, and “minus” when affecting the balance of a liability account or an equity account.
– The “operator” for a credit entry must be “minus” when affecting the balance of an asset account, and “plus” when affecting the balance of a liability account or an equity account.
4. The total value of all debit entries that are part of an accounting transaction must equal the total value of all credit entries that are part of the same accounting transaction.
5. The account that is evaluated by the balance that is based on an accounting entry must be the same account that is updated with the same accounting entry.
That is, if you have an asset credit that subtracts and an asset debit that adds, the net total assets are unchanged. On the other hand, if you have an asset credit that adds, and an equity debit
that adds, you have increased both sides of the equation, so the set of accounts are still in balance.
Programming Required – Part II
The code controlling each transaction simply compares the total of values for credit entries to the total of values for debit entries to ensure that they match. If they do, then processing entails,
for each accounting entry referred to,
- reading the last (actual) balance of the account updated in this accounting entry,
- adding or subtracting the accounting entry “Amount” to or from the balance “Amount”
- creating a new balance of the account with the new “Amount” and today’s “Date”, based on the accounting entry.
The Model – Part III
The only problem with all of this is that different kinds of transactions update different kinds of accounts. For example, an invoice transaction increments an asset account of a type
something like “accounts receivable” and also increments an equity account of a type something like “sales revenue”. A payment against that invoice then decrements the asset
account of type “accounts receivable” and also increments another asset account such as “cash”. Every kind of transaction has a different set of rules about which accounts are
incremented and which are decremented.
The program to support this could be written with each of these rules encoded into the program code. In the past, its reasonable to assume that some accounting systems were in fact written that
way. Thus, every time a new kind of transaction was developed, new code had to be written.
It is possible, however, to pull the specifics of these rules into data. Figure 4 shows one way to do this. Each accounting transaction must be an example of an accounting transaction
type.
An accounting transaction type is then defined to be composed of one or more debit entry rules and composed of one or more credit entry rules. Each of these accounting entry rules
determines which account or account type will be affected by all accounting transactions that are examples of the accounting transaction type.
Each accounting entry, then, must be based on an accounting entry rule.
For example, here are the rules for two transactions, one for invoicing a customer and another for recording payment against that invoice:
In the first accounting transaction type, both an asset (“Accounts Receivable”) and an equity (“Product revenue”) are increased. In the second, one asset (“Accounts Receivable”) is decreased,
while another (“Cash”) is increased. In each case, the rule forces the total assets to remain equal to the total liabilities plus equities.
Here, then, are the results of two actual transactions of the above types:
(Note that, in this example, the resulting balance “Amount” for “Accounts Receivable” will still be $20.00 after the payment.)
Data Constraints
The actual structure of a particular accounting system, then, is governed by:
Business Rules:
6. Each accounting transaction’s credit entry must update the balance of an account specified in the account transaction’s corresponding account transaction type.
The credit entry in an accounting transaction must be to update an account that either is affected by a credit rule entry (or is an example of an account type that is affected by a credit rule
entry) that is part of the accounting transaction type the accounting transaction is an example of.
7. Similarly, each accounting transaction’s debit entry must update the balance of an account specified in the account transaction’s corresponding account transaction type.
That is, the debit entry in an accounting transaction must be to update an account type that is affected by a debit rule entry (or is an example of an account type that is affected by a debit rule
entry) that is part of the accounting transaction type the accounting transaction is an example of.
Remaining Programming Required
Again, this structure means that programming can be defined in terms of general rules, without regard for the particulars of a particular company’s accounting system. Whenever a transaction is
entered, the program must check for:
- Are the debits and credits of the transaction equal?
- Are the accounts being updated the right ones for the transaction type?
If so, processing can proceed as described above.
Indeed, in an interactive system, the program could accept the accounting transaction type and then prompt for values to be debited and credited to the accounts defined by the accounting
transaction type.
Conclusions
The domain of data models is not the same as that for business rule modeling, although they overlap. Clearly a data model does address the issues of semantics surrounding the terms, concepts and
facts that define a business or government agency. It is less appropriate, however, as a way to describe the constraints that affect a business. To the extent that the constraints are
well-understood, however, much can be done to render them in data terms that can be represented by a data model.
[1] What Data Models Do, First Quarter, 2004. What Data Models Cannot Do, Second Quarter, 2004.
The Business Constraint Metamodel, Third Quarter, 2004.
* “Bookkeeping” and “bookkeeper” are the only words in the English language that contain three pairs of letters in sequence. This fact has nothing to do
with business rules or data modeling, but I thought it was interesting…
[2] This model is based on one in David C. Hay’s Data Model Patterns: Conventions of Thought. (New York: Dorset House) 1995.
