From: GW

To: Editor

 

I have a question concerning the proper relational view on database constraints (in your taxonomy). One common problem that arises in many designs is how to represent information that arrives at different times from different sources. Take for example the case where expenses are entered into a journal to be posted to two or more accounts at a later date (assuming a double entry bookkeeping system). My question is NOT how to do this, but rather how to model it in terms of declarative constraints. Here are sample table headers:

 

JOURNAL {JNL_ID,DESCRIPTION,AMOUNT}

ACCOUNT {ACCT_ID,ACCT_NAME}

ALLOCATION {JNL_ID,ACCT_ID,DB_CR,AMOUNT}

 

Each expense has an associated amount, reflecting how much has been spent on the item purchased. The accounting system, however, needs to obey the following business rules (leaving aside any rules determining type of account, etc):

 

R1. The sum of debits = The sum of credits.

R2. The sum of credits (or debits) = The original journal amount.

 

In effect, we must "decompose" a single-valued attribute (journal amount) into debit and credit entries. If this always occurred at the same time, then one could enforce a 1:M relationship with the components and consider the journal amount as a derived aggregate, dropping it altogether from the journal table.

 

However, since the events occur at different times, it seems we must consider the journal amount at one point to be a single-valued attribute, and at another time (after posting) an aggregate of allocated amounts. We are thus talking about "transition constraints", where the valid state of the database must be constrained during the transition from non-posted to posted. There is an additional problem: during the transition itself, the allocation table entries for a given journal item might not balance. In a way, posting is a way of telling the DBMS that one is finished and ready to validate the allocation amounts.

 

What is the predicate of the journal table, and do the changes described force a change in the predicate? Secondarily, is it proper to consider the database to be in a valid state PRIOR to the final posting and AFTER the initial recording of the expense?

 

I hope I have stated the question clearly. Maybe you could comment on it, or in general on the topic of transitional constraints. Thanks for any response you might have. 

 

Chris Date Responds: I know nothing about accountancy or double entry bookkeeping, so I've probably failed to understand your question.  I say, "probably failed" because--to the extent I do understand it! --the problem seems fairly straightforward.  To be specific, I understand the requirement to be as follows: 

 

At time t1 we enter a tuple into JOURNAL that looks like this: 

 

JNL_ID     DESCRIPTION    AMOUNT

=================================

  j             d           a

 

At time t2 > t1 we enter two tuples into ALLOCATION that look like this: 

 

JNL_ID    ACCT_ID     DB_CR    AMOUNT

======================================

   j        ax          DB       a  

   j        ay          CR       a  

 

If the foregoing is correct, then all we need in the way of an integrity constraint is this: 

 

CONSTRAINT <name>

 FORALL a EXISTS j (j.amt_id = a.jnl_id AND

                    j.amount = a.amount);                

 

J and A here are range variables that range over JOURNAL and ALLOCATION, respectively.  This constraint is indeed (as you say) a database constraint, but it isn't a transition constraint; no transition constraint is needed so long as the two tuples are inserted at time t2 in a single operation, and even SQL can do that.  Where's my misunderstanding? 

 

In case it isn't obvious, let me point out explicitly that there's no problem with the foregoing constraint if there's a tuple in JOURNAL with no matching tuples in ALLOCATION.

 

Posted 01/31/03

 

 

 

[ABOUT] [QUOTES] [LINKS]