Using Table Relationships
Once the relational mechanism is in place, it can be used in programs both for database maintenance and for reporting purposes.
At this point, it should be emphasized that the pre-eminent facility for maintaining table relationships is the DataDictionary class. See The DataDictionary class for more information. DataDictionary classes are built and maintained mostly visually using the Studio's Data Dictionary Modeler.
All the foregoing steps—creating the columns, creating the index, defining the relationship—are required in order to use table relationships with or without such data dictionary objects (DDOs). Actually using them without DDOs, however, is a demanding process requiring considerable understanding not only of relational theory but also of the mechanics of maintaining data integrity in a multi-file, multi-user setting.
Any time a group of related tables is to be used together in a program, all the tables to be used must first be opened with the open command. Typically, a program oriented to a particular table opens that file plus some or all of the table's ancestors. If a child table to the "main table" is to be opened, the program is probably in reality oriented to that child table rather than to its parent. For example, where an invoicing program addresses a table of invoices and its child table of transactions, the transactions table should be regarded as the "main table" of the program, and the invoice table and its siblings and ancestors as the parent tables.
When a new record has been opened, or an existing record found, in the main table, the pertinent parent record can be found with a command in the form relate child_file. If, for example, you were in an invoice table and you wished to check an existing invoice known to you only by its number, you could enter the invoice number into a column in the invoice record, and the relate invoice command would find the customer record. That record, for example, would display the customer's name, address, current balance, and other data that might not be in the invoice record.
If, on the other hand, you had entered a new invoice for an existing customer, and you kept the customer's name in each invoice record to facilitate reports of invoices by customer name alphabetically, an attach invoice would move the customer's name down into the column of the invoice record that related to the customer name column.
If no attach were executed and the invoice record were saved, the same thing would still happen, since the save command executes an attach before saving to disk. The reason for putting an attach into a saving command like this is not just a convenience and a reminder. It is also because attaches should be done as late before the save as possible. The greater the time between an attach and a save, the greater the possibility that a field in the parent record will be changed by another user, and the (attached) data in the child-table record made obsolete. This is guarded against by DataFlex, but if it occurs, it is an error condition and can at a minimum produce some processing overhead.
Primary and Secondary Relationships
The example above of an invoice record containing both a customer number and a customer's name is an illustration of primary and secondary relationships between the same two tables. The primary relationship (the customer number) is the one used by relates, and it is made primary by the placement of the column before the column on which the secondary relationship is based. Relate checks the child table's columns for relationships in the order in which the columns stand in the table definition, and once a relationship to a particular parent table has led to finding a record in the parent table, additional columns relating to the same table are not used for finding.
There are a number of advantages to "redundant" relationships of this kind. The most obvious one is that two ways of relating into parent tables are provided (both customer number and customer name would appear to be of value), and child tables acquire an additional way of being indexed (again, both number and name would seem to be of use). But there are additional benefits.
One has to do with changes in the key values that the relationship is based on. If customer names might change from time to time, so long as customer numbers were not allowed to change, customer records could always be found from invoice records based on the customer number. The customer names would become simply auxiliary data, on which no actual table relationship was based. The purpose of the secondary relationships would be purely to support attaches, never to support relates. Used this way, the secondary-column data in the parent table would not have to be unique and would not even have to be indexed, as is required of tables on which relationships are based.
A kind of opposite use would be as a backup to the primary column. If, for example, record number were used for the relationship, it would be primary because record number is always the first "column" in every (embedded) database table. Record number always gives the fastest relational performance as well. But record numbers are "fragile"—subject to change from database corruption and other mishaps. If the child and parent tables had a column on which a secondary relationship could be based, this column could be used to restore the record numbers in the event record numbers were corrupted. To do the restoration, it would be necessary temporarily to remove the definition of the primary (recnum-based) relationship, bring each record of the child table into the buffer, relate on the old secondary column, move (not attach—the relationship has been temporarily removed) the parent's recnum into the appropriate column of the child, and resave the child record. Then the definition of the primary relationship could be restored. For this kind of use, the secondary field in the parent must be kept unique, and maintaining an online unique index on the field is a good way to do it.