[]
        
(Showing Draft Content)

Add a Relationship

A relationship represents the association between two entities. This article explains the common concepts, steps to add a relation, and its important properties. For more information, refer to the following articles.

Add a Relationship

This section describes the steps to add a relationship between the Orders and Customers entities such that many Orders records correspond to zero or one Customers record.

  1. To add a new relationship in the model editor, select an entity for which you want to add a new attribute, and click the Add button in the Relations section.

    Add Relation in Model Editor

  2. In the Add Relation Properties, enter the name and description of the new relation.

    Specify Relationship properties

  3. In the Relations section, select the Condition option and set the target entity by selecting it from the drop-down list. Then add the source column and a target column that appear below.

    Specify relationship properties

  4. Click the Add button. A newly added relationship will appear in the list of relations of the data model's entity.

    Specify relationship properties

Common Concepts

The following section discusses the common concepts related to relations in data models.

Relationship Cardinality

Relationship cardinality dictates how relations are generated when you create a logical model, and how they are used to generate underlying SQL JOIN queries when you run reports.


Relation Types- When you add relations between entities, there are rules to determine cardinality. There are four ways that entities can relate:

  • One: One-to-one

  • Many: Many-to-many

  • OptionalOne: One-to-zero or one

  • OptionalMany: Many-to-zero or more

    If you add relations between entity A (from table A) and entity B (from table B) using the corresponding sets of tables in AC and BC, the rules for determining cardinality are as follows.

Adding relation AB to entity A

  • If there is a unique constraint on table B that is nullable and exactly matches the columns in BC: OptionalOne.

  • If there is a unique constraint on table B that is not nullable and exactly matches the columns in BC: One.

  • Otherwise: OptionalMany. (You cannot use Many in creating relations, but you can set it explicitly.)

Adding relation BA to entity B

  • If there is no unique constraint on table A that matches the columns in AC: OptionalMany.

  • If there is a unique constraint on table B that matches the columns in BC, and one of those columns is nullable: OptionalOne.

  • If there is a unique constraint on table B that matches the columns in BC, and none of those columns is nullable: One.

Example


We can add a relation between the Album and Artist tables using the ArtistID column. Assuming this relation does not exist in the database yet if we add the relation from the Album entity, we get an Album-to-Artist relation with OptionalOne cardinality and an Artist-to-Album relation with OptionalMany cardinality.

SQL JOIN Types

The role cardinality is not taken into account when determining what JOIN type to use. The relationship in the data source controls the behavior.

  • If the relation belongs to the table that the query joins to, then it uses a LEFT JOIN.

  • If there is at least one column in the relation that accepts a null value, then it uses a LEFT OUTER JOIN.

  • Otherwise, it uses an INNER JOIN.

If it accepts null values, it uses outer joins, otherwise, it uses inner joins.


Example


Suppose, the Album table has a foreign key constraint linked with the Artist table, there is a relationship created for it, and it belongs to the Album table. So long as Album.ArtistID does not accept null, then for queries joining the Album table to the Artist table, we use an INNER JOIN, but for queries joining the Artist table to the Album table, we use a LEFT JOIN. However, if Album.ArtistID is nullable, then even in the case of queries joining the Album table to the Artist table, we use a LEFT OUTER JOIN.

Important Properties

The following table describes the important properties of a relationship that you can set in the Data Model Designer.

Property Name

Description

Name

The name of the relation.

Description

A description of the relationship that appears as tooltip text when a user hovers the pointer over the relation.

ID

A unique identifier for the relation. Used internally by the system.

Binding

The database object that the relation represents.

Hidden

Indicates whether the entity reached by the relation is hidden from end-users.

Recursive

Indicates whether the relationship can be added more than once to the same path.

Include Self

Indicates whether to allow the relationship to be accessible to itself in the same recurring path. This applies only to the second level of an entity.

Cardinality

Defines the rules and restrictions for the relation's behavior in the entity tree and query building. Possible values are One, or OptionalOne.

This property determines whether the Recursive property affects the relation.

Hidden Fields

Defines the set of entity fields that is hidden from end-users when they reach the entity through the relation.

Relation

Indicates whether to base this relation on an existing logical relation from the list or a condition.