Mechanics of Relationships
DataFlex relationships are based on the maintenance in each child record of one or more column values that match column values in the related-to parent record. It is not necessary for the columns to have the same names in both tables, but it is often helpful in understanding how program code works to make the names similar. The columns must, however, be of the same data type and of the same length in both tables.
If there were a table of invoices and a table of customers, each invoice would relate to one customer. A given customer might have many invoices, but each invoice would be to only one customer. Each record in this invoice table would contain a column to support the relationship. This column, whose data would match that in a column in the parent record, might be a customer number, the customer's name—anything that identified the record in the customer table. Record number can serve this purpose very well, but when records are deleted or a table becomes corrupted, the original data structure can be difficult or impossible to restore when relationships are based on record number.
The record values that are related to in parent tables must be inherently unique. Even in applications where it is not necessary to find all the child records that relate to a particular parent, it is often necessary to find the parent record from a child table. In the case of the invoices, it would be to acquire the customer's address, for example, and perhaps also credit limit.
For this reason, the column(s) that the relationship is based on must be indexed in the parent table. That index must be unique, rather than non-unique, so that child records each relate to one and only one parent record. In some cases where parent records contain no positively unique column, the relating index is based on combinations of two or more columns in both tables that do yield unique values in combination. In other situations, a serial-number generator, such as the kind used for invoice numbers, is used to "manufacture" unique data for parent records. If the invoice table had a child table of line items (transactions), it would very likely use invoice number to identify the parent records.
Besides providing the columns in both tables and the index in the parent table, one more step is required to create a relationship between two tables. The columns in the table definition for the child table must be designated as relating to their companion columns in the parent table. Alternatively, the set_relate command may be executed on the child table. No equivalent designation is necessary, or even possible, in the parent table. Thus, it may be said that children "know" their parents, but parents do not "know" their children.