Simply Clarion - Lookups, Aliases and RI - Part 3

by Don Reynolds

Published 1999-05-01    Printer-friendly version

Review

• Part 1 (Published in the February '99 issue of COL) discussed five kinds of lookups, referential integrity, and a simple lookup strategy for multiple relationships between two files using standard Clarion Templates. This did not maintain referential integrity.
• Part 2 (Published in the March '99 issue of COL) provided a template to help provide lookups (maintaining referential integrity) in one relationship between two files.

You may want to review these two articles before reading on. (Editor's note: If you are a new subscriber and do not have access to the two issues referenced, please call Jeff at (888) 675-9477 or send e-mail to editor@clariononline.com , and he will ensure that you get that material at no charge. Hmmm...I feel like Bob Dole there, referring to myself in the 3rd person<g>)

Scenario for Part 3

Part three provides one to achieve all of the advantages of Part 1 and Part 2. In our example, we have a file (employee) which has two relationships to the same file (position). We want to store an employee's job title and an employee's supervisor's job title in the employee record using foreign keys. Each foreign key will point to an appropriate record in the position file.

If the name of a position changes (say from "Bottle Washer" to "Glass Cleanser Engineer") we want to make one change and have this reflected in all related records.

How many relationships can exist between 2 files?

Currently only one relationship can be defined between any two files. Part 1 did not really define a relationship, if you recall. In the scenario for part 3, we have two relationships-one for employee position, one for supervisor's position.

One Solution

One way to solve this limitation would be to clone the position file. If the file changed rarely, and someone "always" remembered to copy it when it changed, this strategy could work.

If more frequent updates are needed, online routines could ensure both files are synchronized. This second solution does get a bit trickier.

However, there are at least two better ways. We'll talk about one in part three, discuss another in part 4.

Dictionary Review

We have in our dictionary two files, Employee and Position. Adjusting the Employee file to use foreign keys for Employee Position and Supervisor's position, we have the following fields:

For the Employee file we set up the following Keys:

There is a key for each of the foreign key fields we will be using.

The Position file has the following fields:

Keys for the Position file are as follows:

We establish a relationship between the file Position and the file Employee as follows:

This now shows the relationship between the two files in our listing of files and related files.

But we cannot add a second relationship between Position and Employee. At least not directly. To accomplish what needs to be done we use a file Alias.

File Alias

Briefly, a File Alias is a synonym for a file. It occupies the same physical location on the disk, but is treated logically as if it were a separate file. (This is especially handy when you want to be in the same file in more than one place, but that's a topic for another time.) Differing relationships can be defined for the Alias. In this situation, we'll create an alias file for the Position file.

We include the name, a description, a new file prefix, and show which file is being aliased. (Note: there must be a file designation in the original file for this to work. In this case, I put Position.TPS in the file designation.)

We can now see the added alias to our list of files. But so far we have defined no relationships for this new alias file.

To define a relationship, we follow similar procedures to what we would do for a normal file, only we use the aliased file.

Note that the relationship points to the key for supervisor.

Once this has been done, if we highlight the Employee file, we see there are two relationships for this file. The use of an Alias has enabled us to simulate two relationships between two files.

Inside the Application

In part two we used a template to control the lookup for the Employee's Job title. The prompts for this template look like this:

Now we have an alias file in our dictionary, we can use the same template strategy for the supervisor's job title. We fill in the template prompts as follows:

Note that the lookup file and file references point to the aliased file. Note also that the lookup procedure is different than the lookup procedure for Job Title. L:SupPosition is a clone of L_Position, but uses the alias file.

Advantages

Relational Integrity is maintained for both relationships in this example. In addition, multiple relationships are simulated between two files.

Disadvantages

There is some overhead with additional keys in the database structure. We had to clone a lookup procedure for the aliased file for our lookup.

Summary

Multiple relationships between two files can be simulated using file aliases. The same template we used in part two can be used with each aliased file. This provides relational integrity between the files.

Printer-friendly version

Copyright © 1999-2009 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