Referential Integrity - Parent-Child Situation (Magic xpa 4.x)

In a typical RDBMS database, some tables are defined in which they are related to one another, such as a Header Order and Header Line relationship. Database rules are created that link these tables together. Deferred transactions are advantageous in a certain situation. In order to explain the problem and the solution, we will use our banking application. We have our Savings account, which holds the current balance. But what often happens is that we would need to have a history file which will show all transactions that were made. You wouldn’t like to phone your bank and ask how much money you have (or don’t have) and be told $100 without any method of finding out how they came by this figure.

This history file is a table that is linked to the account table. What would be the meaning of a history file without the account file? If we had the history of account number 921 in the history file, but no account 921 in the accounts table, the history would be meaningless and unreachable.

This will be a parent-child table relationship. When defining this kind of relationship in the RDBMS using referential integrity, the RDBMS assists us and does not allow a situation to exist in which there is an entry for an account in the history table but no corresponding entry in the account table. By the same token, it would not allow us to delete a parent entity (account) where there is still data linked to it.

Let us look at this in a typical scenario, the same scenario that we have come to know and love. But now, we will slightly change the scenario and say that Fred and Wilma want to create a Savings account for their daughter. The bank database requires that when an account is opened, an entry is written to the history file with a description of “Account opened”.

What will happen here? The program creating the account will call the program that creates the transaction. Let us have a look at the resulting SQL commands (ignoring the problem of date and time conversions):

Here, we can now see the problem. The entry for the history table is being written to the database before the entry in the savings table. This will cause a conflict with the database referential constraint rules. In this case, the transaction will fail.

So what is the solution to this problem? The solution is obvious; the history record has to be written to the database after the savings record has already been written (referred to as flushed) to the database. The question remains as to how to do this in an application. Well, we can utilize the fact that all the data manipulation commands are held in the transaction cache before they are committed to the database itself. We now introduce the use of a new property: Sync Data.

How will this help us in our scenario? By changing the Sync Data value to True, the record of the parent program, Create Account, in which we want to flush the record in order to add a new entry to the accounts table, is actually flushed to the physical database before the record of the second program, Create Transactions.

This functionality is made possible because Magic xpa has all the manipulation information stored in cache, waiting for the final OK.