PL400 : configure relationships and types of behaviors (Implement tables and columns)

The concept of relation (database relation) was given by E F Codd in 1970 and nowadays, it is the soul of database. Relationships are  a logical relationship between different tables, which is established on the basis of the interaction between these tables. Relationship helps to make more efficient and scalable solution. Microsoft Dataverse is a relational database that is managed by Microsoft in the cloud.

Table relationship refer to metadata that is used to make changes to the database. these relationships assist queries to retrieve related data efficiently. With the table creation, Dataverse provides the interface to create the relationship between tables, Relationships between tables exist in many forms, but the two most common are one-to-many and many-to-many, both of which are supported by Dataverse.

There are two types of table relationships:

One-to-many relationships(1:N or N:1):

A primary can be associated with multiple reference (related) tables. The primary table row is sometimes referred to as the "Parent" and rows of related table are called as "Children". The N:1 (many-to-one) relationship type exists in the user interface because the designer shows you a view grouped by tables. 1:N relationships actually exist between tables and refer to each table as either a Primary/Current table or Related table. 

• A parent table can be zero, one, or many records of a related entity
• Related entity has a lookup field to store the GUID of the parent record
• N:1 is a 1:N relationship viewed from the other side.

Many-to-Many Relationship (N:N):

A many-to-many relationship occurs when multiple records in one table are associated with multiple records in another table. In the Dataverse, A hidden intersect entity is automatically created that has a 1:N relationship from each of the N:N entities and it can not be viewed or customized.

The Power platform natively supports the following relationships: one-to-many, many-to-many, and many-to-many.

Relationship behavior is most important feature in the data modeling because it helps to ensure data integrity, business automation and how CRUD operation impact the related table data. 

Relationship behavior is combination of actions and cascade type.

There are four type relationship behavior in the Dataverse:

• System
• Parental 
• Referential
• Custom

System:

• These relationships are created and managed by Dataverse and can't be modified or removed.

Parental :

• If parent is deleted, assigned or shared then child records are also affected

Referential :

• Data is related but we don't want action on the related records to affect the other.
• On this behavior we can also set it to restrict delete which allows primary records as long as related data data exists.

Custom:

Custom relationship is the most flexible which allow to user/developer for fully customize. Customizer can manager data according to their business.

There are various type of cascade that can be applied with certain actions occur.

• Cascade Active: Perform the action on all active related table rows.
• Cascade All : Perform the actions on all related table rows.
• Cascade None: Do Nothing.
• Remove Link: Remove the lookup value for all related rows.
• Restrict : Prevent the primary table row form being deleted when related table rows exist
• Cascade User Owned : Perform the action on all related table rows owned by the same user as the primary table rows.

Actions:

Actions are triggered with some certain behaviors. For N:1 and 1:N relationships, there are setting for the relationship behavior, called cascading rules, that apply to the following actions:

Assign: This action occurs when you change the owner user of an entity owned by a user.

Share:  This action occurs when data is shared with another user

Unshare: Opposite of share.

Reparent: Allows specification of how reparent should be controlled.

Delete: This is an important because it controls how cascading works during a delete. By configuring this we can choose to cascade the delete, restrict the delete or simply remove the link.   Restrict the delete requires the user to reassign all child records prior to deleting the related record.

Merge: Merge occurs when you combine multiple records together and what should happen to the children.

Usage :

1:N (one-to-many) or N:1(many-to-one) table relationship can use to address any of the following requirement.

• Field Mapping
• Navigate pane options of model-driven App.
• Relationship behavior
• Whether the relationship is hidden in Advance Find.
• Make a Hierarchical Relationship

Field Mapping:

A mechanism that maps one field to another, allowing data to be copied between related records.

A valid field map must meet these requirements:

• The data type must match.
• The length of the target field cannot be shorter than the source field.
• The format should match.
• The target field must not be used in another mapping.
• The source field must be visible on the entity form.
• The target field must be a field a user can enter data into.
• Address ID values cannot be mapped. 

Navigate pane options of model-driven App:

Navigation within the form allows app users to view lists of related rows. Each table relation has properties controlling whether it should be shown. Any table relationship that is configured to be displayed can be overridden in the form editor. You can also include navigation links to display Web resources or other Web sites through form navigation.

Relationship behavior : Read Above

Whether the relationship is hidden in Advance Find:

Although Advanced Find is a remarkable tool for searching or retrieving data across an organization, Microsoft offers the option to restrict access to particular relationships or columns. The column cannot be made searchable in the Table Editor window, similarly the relationship can be hidden as a Relationship Editor window.

Make a Hierarchical Relationship:

With the Microsoft Dataverse, hierarchical data structures are supported by self-referential one-to-many (1:N) relationships of related rows. Self-referencing means that the table is related to itself.

• Only one (1:N) self-referential relationship per table can be set as hierarchical. In a self-referential relationship the primary table and the related table must be of the same type.
• A hierarchy or visualization is based on one table only.
• The maximum number of columns that can be displayed in a tile is three with Unified Interface and four with the legacy web client. If you add more columns to the Quick Form that is used for the tile view, only the first three columns will be displayed.

Connection:

Apart from the table relation, another feature which is used to set the relation(s) in the Dataverse is known as the connection. Connection helps to create a connection between two records. With Microsoft Dataverse you can define connections between table rows without creating table relationships. In model-driven apps people can establish a named link between rows to establish a less formal relationship that doesn't justify creating an actual table relationship. Some examples include friends, siblings, spouses, partners, and stakeholders. Some relationships may also be mutual, such as child and parent, husband and wife, or doctor and patient.

This is not a default feature, it needs to be enabled during table creation. Once enabled, it can be disabled.

Enable the Connection feature in the Table: 

Connection option in the Account table: