Reference relationships

Reference relationships in the logical model serve to establish functional relationships between two entities.

Example

  • In an EmployeeHasCostCenter reference, the referenced entity is CostCenter, and the referencing entity is Employee.

  • In a CostCenterHasParentCostCenter reference, the referenced entity is CostCenter, and the referencing entity is also CostCenter. This is a self-reference.

Reference relationships usage

Reference relationships are used for:

  • Organizing records into hierarchies. For example, to build a hierarchy of cost centers using a self-reference called CostCenterHasParentCostCenter, which links the CostCenter entity to itself.

  • Displaying lists of child records referencing a given record. For example, to display a cost center with a list of employees belonging to this cost center according to an EmployeeHasCostCenter relationship.

  • Navigating to records referenced through a relationship. For example, to navigate from an Employee record to the cost center they belong to.

  • Referential integrity purposes. Referential integrity is enforced as part of the golden data certification process. References can be used as constraints on records. For example, to check that an employee is attached to an existing cost center, or a contact is attached to an existing customer.

  • Propagating or preventing deletion. For example, to define that a customer with existing contracts cannot be deleted, or that when a product is deleted, all its items should also be deleted.

A reference is expressed in the model in the form of a foreign attribute that is added to the referencing entity.

Create reference relationships

To create a reference relationship:

  1. Right-click the Reference Relationships node in the model and select Add Reference.
    The Create New Reference Relationship wizard opens.

  2. In the wizard, select the Auto Fill option and enter the following values:

    • Name: internal name of the object.

      Due to a legacy constraint based on the maximum object name length in certain relational database management systems, physical data object names that are generated during model deployment are truncated to 25 characters. Designers should plan accordingly when choosing logical data object names.

    • Physical Name: name of the database indexes created for optimizing access via this reference.

    • Label: user-friendly label for this object. Since the Auto Fill checkbox is selected, the Label field is automatically filled in. Modifying this label is optional.

    • Validation Scope: select whether the referential integrity should be checked pre- or post-consolidation, or both.

  3. In the Referencing [0..*] group, enter the following values:

    • Referencing Entity: select the entity which references the referenced (parent) entity. For example, in an EmployeeHasCostCenter relationship, it is the Employee entity.

    • Referencing Role Name: name used to refer to this entity from the referenced entity. For example, in an EmployeeHasCostCenter relationship, it is the Employees.

    • Referencing Role Label: user-friendly label to refer to one record of this entity from the referenced entity. For example, in an EmployeeHasCostCenter relationship, it is the Reporting Employee.

    • Referencing Role Plural Label: user-friendly label to refer to a list of records of this entity from the referenced entity. For example, in an EmployeeHasCostCenter relationship, it is the Reporting Employees (plural).

  4. In the Referenced [0..1] group, enter the following values:

    • Referenced Entity: select the entity which is referenced. For example, in an EmployeeHasCostCenter relationship, it is the CostCenter entity.

    • Referenced Role Name: name used to refer to this entity from the referencing entity (subject to the above-mentioned 25-character limit). For example, in a relationship like EmployeeHasCostCenter, it represents CostCenter. This name also serves as the name for the foreign attribute added to the referencing entity to express this relationship.

    • Referenced Role Label: user-friendly label to refer to this entity from the referencing entity. For example, in an EmployeeHasCostCenter relationship, it is the Cost Center.

    • Mandatory: defines whether the reference to this entity is mandatory for the child. For example, in an EmployeeHasCostCenter relationship, this option must be selected, as an employee always belongs to a cost center. For a CostCenterHasParentCostCenter reference, the option should not be selected, as some cost centers may be located at the root of the organization chart.

    • Physical Name: name of the physical column created for the foreign attribute in the tables representing the referencing entity.

  5. Click Finish to close the wizard.
    The Reference Relationship editor opens.

  6. (Optional) In the Description field, enter a description for the reference relationship. This description is intended for the model designers.

  7. In Documentation, enter user-facing documentation for the attribute. You can use plain text or Markdown syntax to provide detailed information about the attribute, which will be displayed in the documentation side navigation.
    You can also define a description and documentation in the Referenced Entity (From) and Referencing Entity (To) tabs to provide more detailed information about the roles of the parent and child entities in that relationship.

  8. (Optional) For a mandatory (one-to-many) reference, define a custom Error Message (Mandatory). This message appears instead of a default message when this reference is left empty.

  9. (Optional) In Error Message (Broken Reference), define a custom error message to be displayed instead of a default message when this reference points to a parent record that does not exist.

  10. With the Delete Propagation property, define how this reference affects deletion. Possible values are:

    • Restrict: prevents the deletion of the referenced record if there are referencing records linked through this relationship. If a child record exists and references the parent (or an ancestor), the deletion operation of the parent will be aborted or disallowed.

    • Nullify: sets the reference to null in the referencing record when the referenced record is deleted. If a child record exists through this relationship and references the parent, the relationship to the parent is set to null. This option cannot be selected for mandatory relationships.

      Upon model validation, an error message will appear if the survivorship rule for the referenced entity is set to No Override. This prevents deployment of the model.

      If the survivorship rule for the referenced entity is set to Override - until consolidated value changes, a warning will be displayed to inform you that this configuration may result in the reuse of master record values that still reference the removed golden record after reconsolidation. Despite the warning, you can proceed with deploying the model.

    • Cascade: deletes referencing records when the referenced record is deleted. If a child record exists through this relationship while attempting to delete the parent, an attempt is made to recursively delete this child and all its children. If any child relationship, at any level, prevents deletion, neither the parent record nor any of its children are deleted.

  11. With the Split Duplicates Propagation property, define how splitting records in the referenced entity affects the referencing records. This property applies when both the referenced and referencing entities are fuzzy-matched. Possible values are:

    • None: with this configuration, when records in the referenced entity are split using a duplicate manager, the records in the referencing entity remain unchanged.

    • Reset Matching: with this configuration, when records in the referenced entity are split using a duplicate manager, the records referencing the split records are re-matched. This process ignores previous match groups and user decisions. The resulting golden records are re-distributed under their respective parent records. When re-matched, the referencing records are all re-merged into new golden records with new golden-record IDs, losing all their value overrides in the process.

  12. Press Control+S (or Control+S on macOS) to save the editor.

  13. Close the editor.