Question 36

Explanation

• When a one-to-many relationship is created between two tables, a lookup column is created on the related table on the many sides of the relationship. This column links the parent record with the child record. A lookup column is also known as the foreign key.

• Alternate keys are created for integrations with other systems.

• Customer is a special lookup column that represents an account or a contact. You cannot create a customer column by creating a one-to-many relationship.

• In Microsoft Dataverse, every table has a primary key that uniquely identifies each row in the table. The primary key column is created when the table is created.

Table relationships

Table relationships define how rows can be related to each other in the database. At the simplest level, adding a lookup column to a table creates a new 1:N (one-to-many) relationship between the two tables and lets you put that lookup column in a form. With the lookup column, users can associate multiple child rows of that table to a single parent table row.

Beyond simply defining how rows can be related to other rows, 1:N table relationships also provide data to address the following questions:

• When I delete a row should any rows related to that row also be deleted?

• When I assign a row, do I also need to assign all rows related to that row to the new owner?

• How can I streamline the data entry process when I create a new related row in the context of an existing row?

• How should people viewing a row be able to view the associated rows?

Tables can also participate in a N:N (many-to-many) relationship where any number of rows for two tables can be associated with each other.

Decide whether to use table relationships or connections

Table relationships are metadata that make changes to the database. These relationships allow for queries to retrieve related data very efficiently. Use table relationships to define formal relationships that define the table or that most rows can use. For example, an opportunity without a potential customer wouldn't be very useful. The Opportunity table also has a N:N relationship with the Competitor table. This allows for multiple competitors to be added to the opportunity. You may want to capture this data and create a report that shows the competitors.

There are other less formal kinds of relationships between rows that are called connections. For example, it may be useful to know if two contacts are married, or perhaps they are friends outside of work, or perhaps a contact used to work for another account. Most businesses won't generate reports using this kind of information or require that it is entered, so it's probably not worthwhile to create table relationships. More information: Configure connection roles

Types of table relationships

When you look at the solution explorer you might think that there are three types of table relationships. Actually there are only two, as shown in the following table.

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. The related table, sometimes called the child table, has a lookup column that allows storing a reference to a row from the primary table, sometimes called the parent table. A N:1 relationship is just a 1:N relationship viewed from the related table.