Relationship types that are available in Microsoft Dataverse
In unit one of this module, you learned that Microsoft Dataverse supports two kinds of relationships: one-to-many and many-to-many.
One-to-many relationship
The one-to-many relationship (which is also called 1:N or parent-child) includes a primary (parent) table that can be associated to many other related (child) table rows by using a lookup column on the related (child) table. The primary row is the parent and the related table rows are called child rows.
When viewing a primary table row in a 1:N relationship, you can view a list of the related child table rows by filtering all child rows that contain the same key value as the key value in the primary row.
Many-to-one vs. One-to-many
The many-to-one (N:1) relationship isn't fundamentally different than one-to-many, it's only a different point of view. The many-to-one relationship type exists in the user interface because the designer allows you to view and create relationships from either of the tables involved in a relationship. If you have a one-to-many relationship between Table A and Table B, you can see a one-to-many relationship under Table A in the Power Apps maker portal, and a many-to-one relationship under Table B. A many-to-one relationship is just a one-to-many relationship viewed from the related table. It isn't a different type of relationship, it's merely a different perspective.
Lookup columns and relationships
An easy way to create a table relationship is by creating a column with data type Lookup to another table. Creating a lookup column creates a many-to-one relationship. Correspondingly, creating a one-to-many relationship creates a lookup column on the related table.
Many-to-many relationship
The many-to-many relationship (which is also called N:N) includes a special third table called a relationship table, sometimes called an intersect table, which maps how the many rows of one table can be related to the many rows of another table.
When viewing rows of either table in an N:N relationship, you can view a list of any rows of the other table that are related to it.
One-to-many relationships are simplistic and are universal. An example of a one-to-many relationship includes an invoice (the one) with line items (the many), as previously discussed. Another example is a classroom (the one) and students in the classroom (the many).
Many-to-many relationships are a bit more complex. Tables that have this type of relationship require a special table in between them to map how they're related to one another. Examples include authors and books. It's possible that a book could have many authors and an author could write many books. A new intersection table between the book table and the author table is needed to map (resolve) the books and authors with an entry in the intersection table that contains the name of the book and author name in each row. You can create a report by using this intersection table to show all the books that are written by an author, even if that writer was one of many or the only author on the book.