Skip to content

Relationships

Relationships are used to "normalize" your data. Some of the goals of normalization are:

  1. To Eliminate Repeating Groups
    Make a separate lookup table for each set of related attributes, and give each table a primary key. For example, you may be recording contacts that you have with your clients. You should not store the contacts in the client table. Instead, you would move the contact information into a separate table and relate each record to the primary key in the Client file.

  2. To Eliminate Redundant Data
    If an attribute depends on only part of a multi-valued key, remove it to a separate table. Let’s say each contact you have with a client is categorized (Telephone call, mail-out, personal visit, etc.). You should store the Contact Types in a separate table and relate the Contacts to the contact types.

  3. To Eliminate Columns Not Dependent On Key
    If attributes do not contribute to a description of the key, remove them to a separate table. For example, suppose you are storing the Client's Employer Name, Employer Address, and Employer Phone Number. These attributes describe the client's place of work, not the client, so you should create an Employer table and move the Employer information to this table, relating it to its Key from the customer table.

These are the first three forms of data normalization and are probably the ones you will apply most commonly to your database.

Relationships are represented in DataFlex using the standard foreign-key and primary-key model:

  1. A relationship must be defined from a child to a parent table. A child table defines a field or set of fields that refer to a corresponding set of fields in the parent. The size and data type of fields in the parent and child must be the same. This relationship is defined using the Studio's Table Editor.

  2. The value of the related-to field(s) in the parent must be unique and supported by an index whose segment(s) are the same as the related-to field. The related-to field in the parent is almost always your primary key and is referred to in Data Dictionaries as the key field.

  3. The child table will usually contain one or more indexes that allow for fast finding by the relating fields. This means that the first segment(s) in this child should consist of the relating-to field(s).

Data Dictionaries make use of relationships in four ways:

  1. Relates: When a Data Dictionary finds a record, all parent DDOs will automatically find their related-to parent records. The parent DDO of those DDOs will then find their related-to records up the relationship structure. The find/relate process finds an entire structure of related records.

  2. Attaches: Before a save or a find, the values of the parent related-to fields are moved into child relating fields. This attach process ensures that child-parent relationships are properly maintained during saves.

  3. Constraints: A relates-to constraint defined in a Data Dictionary Structure constrains the finding of child records to a parent. This feature is used extensively in header-detail types of applications (e.g., an order entry system where order-detail records should be constrained to a specific order).

  4. Validations and Save: A validation occurs before a save validation in the Main DDO and all related parent DDOs. In addition, the relational DDO structure is inspected before the save. If the entire structure is not in place, validation will fail. When a save occurs, the record in the main DDO and all of the records in the parent DDO are saved as a single transaction.

Basically, relationships in Data Dictionaries allow you to work with a hierarchy of records as a single entity.

See Also