Simply Clarion - Lookups, Aliases, and RI - Part 1

by Don Reynolds

Published 1999-02-01    Printer-friendly version

Scenario

As you design your new system, you find you have a field (position) in one file (employee) which needs to have a specific range of values. How do you restrict values to the approved range? You could put the accepted values in the "Must be in List" choices of the Validity checks tab in the dictionary editor.

For some companies this would be valid. In others, however, the positions could be different (the mind boggles at official and unofficial possibilities) and could change dynamically with each company. Something better is needed. You could build a file (We’ll call it POSITION) of acceptable values which could be updated by each company to reflect the various approved positions within the company. Let’s look at several ways of doing this, their advantages and disadvantages.

Referential Integrity (RI)

First we need to understand referential integrity. Referential integrity makes sure that the relationship between two related files is accurate. This is usually done in conjunction with a key field in the parent file which corresponds to a similar field in a child file.

For example, an invoice file, a parent, may have a key of InvoiceId. A detail file (child of invoice file) needs to have a field in it corresponding to InvoiceId of the parent. This child file field is called a foreign key. Using these two fields in these two files, the database system can get all of the related details for one invoice.

This is also a classic one-to-many relationship. For one parent (invoice) there can be zero, one, or many children (detail) records.

Setting Relationships in the Dictionary

We set our relationship properties in the dictionary using the screen shown above. The relationship type, the primary file, the child file, and the field mapping are all selected. Underneath the field mapping is a box for Referential Integrity Constraints: On Update and On Delete.

On Update

If the linking key of the parent record is changed, what should be done to the corresponding foreign key in the children records? Options include:

• Restrict Do not allow the parent key to change if there are children
• Cascade Change all of the foreign keys in the children records
• Clear Clear the foreign keys in the children records
• None No update referential integrity is needed.

If an auto-incrementing key is used in the parent file which has no other meaning than providing uniqueness (a recommended database design structure), None is the correct choice. There should be no way that these internal keys are changed; they should not be visible to the end user.

On Delete

If the parent record is deleted, what should be done to the corresponding children records? Options include:

• Restrict Do not allow the parent record to be deleted if children exist
• Cascade Delete all of the children records for the parent
• Clear Clear the foreign keys in the children
• None No update referential integrity is needed.

Usually you need to choose either Restrict or Cascade. The most appropriate answer depends on the particular data. For example, if we delete the invoice, we would probably want to delete all of the children (Cascade). Other cases may warrant Restrict.

Types of Lookup

Several types of lookup exist. They include at least the following:

• Lookup without dictionary referential integrity checks
• Lookup with dictionary referential integrity checks
• Multiple lookups between the same two files
• Lookups without using dictionary relationships
• Specialized Lookups

We’ll look at one of these in this article, then look at the others in future articles.

Lookup without dictionary referential integrity checks

How do we do this?

Using our relationship properties screen, we set up the relationship between Employee and Position as follows. Note that the Foreign Key for Employee is set to none. We need to set what field in Employee links to POS:ApprovedValue. To do this we double click on (No Link).

A listbox showing the available fields in Employee appears. We select the appropriate linking field.

To complete this structure, we to set the validity check for the JobTitle field in employee.

We have set this so that contents of JobTitle must be in the Position File. If we wizard a browse and form procedure for Employee, the appropriate lookup screen will be generated.

Assume that I have the following entries in Position:

• President
• Vice President
• Bottle Washer

When adding an Employee record, if I enter "President" (an approved value), I proceed to the next field. However, if I enter "Accountant",

since this is not an "approved" position, I get the lookup screen:

where I can select a position. After pressing Select, my original Form looks like this:

The value "Bottle Washer" has been moved into the JobTitle field of Employee.

Note:

If this were to be a fully deployed project, I’d modify some of the screens. But for purposes of this article, the functionality can be displayed using the wizard-generated ones.

Advantages

This functionality is fairly easy to set up. Since no relationship is defined between the two files, there does not need to be a key file defined in employee which relates to the Values file. This saves some additional I/O when inserting records.

Disadvantages

Since there is no referential integrity between the two files, and the validity checks Must be in file, future problems may occur. For example, a Position record (say President) could be deleted which has corresponding values in the employee file. Since no referential integrity is in place, no attempt is made to alter the children records. (Hand coding could take care of this.) If this is not taken care of, pseudo-orphan records are created. I say "pseudo" because there is really no formal relationship between the two files. However the validity checks indicates there is a relationship. If one of these orphan records is edited in the future, the default program logic will require the end-user to choose another value (since the relevant data must be in the linking file). If you want to maintain the old value, you cannot keep the old value as is. You either cancel changing the record (losing other changes), or change the value to existing value, losing the old one.

In a future article we’ll show you a way to solve this.

Summary

There are several ways of providing lookups to validate data. Referential Integrity maintains the correct relationship between files. Sometimes it is used in lookups sometimes not. The programmer needs to evaluate the advantages and disadvantages of his lookup strategy.

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942