Relate one or more tables - Introduction
To make an efficient and scalable solution for most of the solutions that you build, you'll need to split up data into different containers (tables). Trying to store everything into a single container would likely be inefficient and difficult to work with and understand.
The following example helps illustrate this concept.
Imagine that you need to create a system to manage sales orders. You'll need a master list of items that you sell along with the inventory on hand, cost of the item, and the selling price. You also need a master list of customers with their addresses and credit ratings. Finally, you'll need to manage invoices of sales that you make so you'll want a way to store invoice data. The invoice should include invoice information (such as date, invoice number, and salesperson), customer information including address and credit rating, and a line item for each item on the invoice. Line items should include a reference to the item that you sold and be able to provide the proper cost and price for each item and decrease the quantity on hand based upon the quantity that you sold in that line item.
Trying to create a single table to support the functionality that was previously described would be ill advised and inefficient. The correct way to approach this business scenario is to create the following four tables:
- Customers
- Products
- Invoices
- Line Items
Creating a table for each of these items and relating them to one another will allow you to build an efficient solution that can scale while maintaining high performance. Splitting the data into multiple tables also means that you won't have to store repetitive data or support huge rows with large amounts of blank data. This is known as data normalization. Additionally, reporting will be easier if you split the data into separate tables.
Tables that relate to one another have a relational connection. The technology that underlies Microsoft Dataverse is a relational database that is managed in the cloud by Microsoft. 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.
One-to-many relationships are also known as parent-child relationships. In the previous invoice example, the invoice table would be the parent and the line items would be a child table. An invoice can have zero, one, or many line items (child rows), but the line item will always be related to just one invoice (parent row). Typically, the child rows won't exist without a parent row.
A column that only allows unique values is used to identify the parent row. This unique column is called a key. The same value (the parent key) is stored in the related child rows. This column is called a foreign key when the child row is used to store the parent key value. Ingeniously, filtering is used to display child rows with a value in the foreign key that matches the key value in the parent row. This allows applications to display the child rows (line items in the previous example) that belong to a particular parent row (invoice in the previous example). This concept underlies many business software applications.
Tip
Splitting data into different tables makes for an efficient solution design that can scale, but knowing how to split up the data into tables can be difficult. Dataverse contains many of the tables that most organizations will need. Using standard tables and extending them will ensure that you are building solutions around a proven, scalable way of storing the data that is used by your solutions.