10 Understanding Data Relationships
This chapter contains the following topics:
10.1 Data Relationships
The Data Relationships tool is a collection of programs that you can use to manage the consistency and quality of data that is entered in certain master tables. Using this tool to set up data relationships enables you to place tighter controls over your data and helps increase efficiency during data entry. You use the tool to define relationships between columns (data items) in these tables:
-
Business Unit Master (F0006)
-
Contract Master (F5201)
After you define data relationships for the columns, you define the default destination values that you want to appear for a single basis value when you enter a new business unit, job, or contract. The system automatically applies the data relationships rules during data entry in the following programs:
-
Business Unit Master (P0006)
-
Job Cost Master (P51006)
-
Contract Master (P5201)
-
Create/Edit Project and Government Contracts (P52G01M)
This program is used by customers who conduct business with the government.
If a job, business unit, or contract exists, you must update the existing records in the F0006 or F5201 table with the data relationships rules by doing one of the following:
-
Running the Update Data Relationships program (R00422).
-
Manually updating the records in the data entry program.
You update the F0006 and F5201 tables after you initially define your data relationships rules. Thereafter, you update the tables whenever you revise existing data relationship rules or define new ones. If you have just a few records to update, you can manually update the records in the data entry program instead of running the R00422 program.
See Updating F0006 and F5201 Records with Data Relationships Rules.
|
Note: The Data Relationships functionality supports multiple columns in the F0006 and F5201 tables; however, for brevity, this documentation uses business unit category codes as examples instead of listing all of the possible supported columns. |
10.1.1 Example: Data Relationships
This example shows the setup for a simple data relationship between four business unit category codes (RP12–RP15) and the end result of the setup, as it appears in a data entry program.
10.1.1.1 Setup: Data Relationship Between City and State
The data relationship setup and values are:
| Basis Column = RP12 (City) | Destination Column = RP13 (State) |
|---|---|
| CHI (Chicago) | IL (Illinois) |
| DEN (Denver) | CO (Colorado) |
| LAS (Los Angeles) | CA (California) |
| NYC (New York City) | NY (New York) |
10.1.1.2 Setup: Data Relationship Between State and Region
The data relationship setup and values are:
| Basis Column = RP13 (State) | Destination Column = RP14 (Region) |
|---|---|
| IL (Illinois) | MID (Midwestern) |
| CO (Colorado) | WES (Western) |
| CA (California) | WES (Western) |
| NY (New York) | EAS (Eastern) |
10.1.1.3 Setup: Data Relationship Between Region and Country
The data relationship setup and values are:
| Basis Column = RP14 (Region) | Destination Column = RP15 (Country) |
|---|---|
| MID | USA |
| WES | USA |
| EAS | USA |
10.1.1.4 End Result: Data Entry Program
After performing the setup described in this example, the user updates the F0006 table by doing one of the following:
-
Running the Update Data Relationships program (R00422)
See Automatically Updating F0006 and F5201 Records with Data Relationships Rules.
-
Manually updating the records in the Business Unit Master program (P0006).
See Manually Updating F0006 and F5201 Records with Data Relationships Rules.
The system displays the results on the Revise Business Unit form:
In this example, Category 12 is the basis column and Category Codes 13–15 are the destination columns. The destination columns are protected from input because the Enforce Rule flag is selected for the column relationships.
Destination values of blank are allowed if both the Allow Blanks and Enforce Rule flags are selected for the column relationships.
10.2 Data Relationships Terminology
This table describes terminology that is used for data relationships:
| Term | Description |
|---|---|
| Relationship tables | The tables that are supported for data relationships functionality:
|
| Relationship columns | The columns that are supported for the data relationships functionality. These columns can have a relationship with one another and must be part of the F0006 or F51006 table. |
| Basis column | The column in a table that is considered the basis of a data relationship. In a data relationship, the basis column drives the default value for the destination column. |
| Basis value | The value in the basis column that drives the default values in a data relationship. |
| Destination column | The column in a table that is considered the destination in a data relationship. In a data relationship, the default value for the destination column is driven by the basis column. |
| Destination value | The default value that is based on the basis value in a data relationship. |
| Value relationships | The relationship between the values in the basis and destination columns. |
| Column relationships | The relationship between the basis column and destination column. |
| Column set | A set of column relationships that are in the same table and that have the same effective date. Typically, the column set is identified with a column set description (name). |
| Column pair | Within a column set, the basis column and destination column for a single data relationship. A column set usually consists of multiple column pairs. |
10.3 Data Relationships Setup
Because of the complexity involved in data relationships setup, system administrators typically set up the data relationships and rules for each table. The system administrator should be responsible for:
-
Activating the data relationships functionality for the F0006 and F5201 tables.
-
Defining the column relationships between the supported columns for each table.
-
Defining the subsequent default values (value relationships) for each column set.
-
Determining whether to enforce the default rules for a destination column.
-
If rules are enforced, the destination column is protected from user input in the entry program. If the destination column has a non-blank value, the update program and entry program will overwrite the value with a new value.
-
If rules are not enforced, the destination column is input capable in the entry program. If the destination column has a non-blank value, the update program and the entry application will not overwrite the value with a new value; however, the user will be able to change the value.
-
-
Determining whether to allow blank values in a destination column.
-
Updating changes made to existing default rules for destination columns.
This process flow shows the programs that are used to set up data relationships, the data entry programs in which you can view data relationships, and the tables that are updated with data relationships rules:
Figure 10-2 Data relationships process flow
Description of ''Figure 10-2 Data relationships process flow''