Challenge 021 | Dataverse modelling

Low-code is designed to be user-friendly. This is enabling every business employee to build the tools they need. This is the core of my love for low-code. In my day-to-day job I see that many business employees are perfectly capable of creating apps themselves. In the majority of the cases, this starts with a Canvas App with a SharePoint list or two as a data source. This gets them going and they become really excited about all the capabilities a Canvas App has to offer. When they eventually notice they spend too much time on the look and feel, I show them the Creator Kit and they get even more excited and skilled.

Then they start building bigger solutions. More tables, more users, and potentially the more critical business processes. These types of solutions gravitate towards Dataverse solutions with a proper ALM process. From my experience, making them accustomed to use environment variables, the ALM Accelerator, and Model-Driven Apps is doable with some time. The biggest hurdle for most citizen developers is translating their business need into a database design. In this challenge we will walk through the process of creating a Dataverse model for a restaurant.

Challenge Objectives

🎯 Learn the key concepts of relational databases

🎯 Be able to transform a business scenario into a database schema

🎯 Get familiar with drawing database diagram

🎯 Use tools to generate a diagram from existing solutions

Introduction

In this challenge we will create a Dataverse database model for a restaurant. But before we will do that, we will need to understand the fundamentals of databases. So first some theory. Once we understand the concepts of how databases work, we will elaborate the restaurant scenario a bit and design a database model. The actual creation of the tables is left out of scope as I solely want to focus on the design process. At the end of the challenge we will go over some tips how you can quickly get an understanding of the database model of an already created solution. This is especially helpful when you are asked to make adjustments to a solution.

Database fundamentals crash course

Structured data

You know that data is essential for an application. In the old days, you would call to a restaurant to make a reservation. The person picking up the phone would then note some of your details on a sticky note. This is called unstructured data. A pile of these are collected and put into a good old agenda like the image below.

As you can see, there is already some structure, as the pages are printed with predefined tables on it. this can give you a good overview of the day. But you would still need to turn many pages to search for the data. This is called semi-structured.

You as a citizen developer would directly think "why not put it in an excel or SharePoint List?". I agree. That's much easier to handle all the different days and search for the things you need. This is called structured data.

Entities

Structured data is great, but it can get cumbersome to put everything into one table. To give you an example, have a look at the Reservation table below. It contains a snippet of the agenda for a restaurant.

Reservation
Date	Time	Name	Phone
1-11-2023	12.00	Satya	+31612345678
8-11-2023	12.00	Satya	+31612345678
15-11-202	12.00	Satya	+31612345678
22-11-202	12.00	Satya	+31612345678

As you can see, Satya comes by every Wednesday for lunch. Unfortunately, Satya is being stalked by Sundar and decides to change phone number. Luckily he informed us with his new number, +31612345679. With the current setup, we will need to go over every row of the table to update the Phone value for Satya. When Mark starts stalking Satya, we will need to go over all the records, again!

We can split up the information into different tables, or entities. As you can see, we now have a Customer table and a Reservation table. In the Reservation table we store the CustomerID of the related record of the Customer table. If we now have to update Satya's phone number, it only needs to be updated once in the Customer table.

Customer
CustomerID	Name	Phone
1	Satya	+31612345678
2	Bill	+31634567890

Reservation
ReservationID	Date	Time	CustomerID
1	1-11-2023	12.00	1
2	8-11-2023	12.00	1
3	15-11-2023	12.00	1
4	22-11-2023	12.00	1

Keys

The ID numbers used in the tables are called keys. In the Customer table, the CustomerID is the primary key. The primary key is a unique value across the table. We could have multiple customers names Satya or Bill, and the phone number could potentially also be the same, but the ID will always be unique. Many services auto-generate these for you. SharePoint uses an ID just like in the table. Dataverse uses a GUID, which is a globally unique identifier and looks like the string below. Both will do the job.

2907ec4c-5f2w-4aca-95f1-9b6dd9acb0b3

Dataverse also has the option to add alternate keys. You will specify a column that contains the primary key from a different data source. This is useful when migrating data to Dataverse.

In the Reservation table, we will store the primary key of the Customer table as a foreign key to a record. This is how we associate the Customer data to the Reservation data.

Relations

We can also create a relationship between the two tables. This will help the eventual application retrieving related data quickly, and use relationship settings. There are three types (cardinalities) of relationships:

• One-to-Many

• Many-to-many

• One-to-One

One-to-many

The first relationship is by far the most commonly used relationship between tables. This is actually also the relationship that we can use between a Customer and Reservation. It associates one record from one table to multiple records of another. We know that one customer, Satya, can make multiple reservations. But the reservation is only done by one person. This databse way of thinking is what you experience when you are not the one who made the reservation, but you enter the restaurant first. They will always ask you under which name the reservation was made.

You can draw relationships between tables in a diagram. This gives you great overview of the tables and how they relate to one another. There are many ways of doing it. you can type a 1 on the one side and an asterisk (*) on the many side. Personally, I like to use draw.io for these types of diagrams. These diagrams are called Entity Relationship Diagram (ERD). Draw.io has specific ERD shapes available. Below you can see an example of some of those. The tables are expandable and the relationship is a simple line. The many side of the relationship is a fork, which makes it super easy to read.

Many-to-many

Imagine we want to keep track of the all the ingredient that are required for a particular dish. We know a dish made out of multiple (many) ingredient. But we also know that an ingredient such as butter is used probably used in more than one (many) dishes. This means we are dealing with a many-to-many relationship.

Although such a relationship is possible in Dataverse, it actually consists of two one-to-many relationships. It will create a junction (or linking) table in between the two tables, with one-to-many relationships. Below you can see such a junction table.

This junction table is required to store the data, but is not always available for you to get data from. This also is the case in Dataverse. This is one of the reasons why this type of relationship isn't used as much. Some even argue you shouldn't use it at all. I don't have such a strong opinion on it. But I do think that modelling it as two one-to-many relationships can give you benefits. In the example given, what would you do when you eventually want to keep track of how many of a particular ingredient is required for a dish? If we model the junction table ourselves, we could add the amount as a field.

If you modeled it as a many-to-many relationship, you don't have that flexibility. It's not a no-go, but something to keep in mind.

One-to-One

This type of relationship isn't commonly used. The reason for this is that when we want to store something particular for a record in a table, we can just add it as a field/column to that table. I personally don't use it often either. The only reason I see to use it is to make the database more efficient.

Imagine we have a loyalty program in our restaurant, which will give the enrolled customers a certain amount of discount. We could add a column to the Customer table that contains the discount percentage, but that will leave many fields blank in the table, which isn't perfectly efficient. We could create a LoyaltyProgram table, that will relate one LoyaltyProgramID to one Customer. This allows us to also add a enroll date, valid to date, etc. to this newly created table, rather than adding it to the Customer table.

Model a database

The theory of what entities, fields, keys and relationships can be conveyed to a citizen developer. This is technical information, which citizen developers can familiarize themselves with quite fast. Where the main issue lies in in translating a business scenario into a database model. Simply put, what entities, fields, and relationships are required to facilitate this scenario?

At the beginning of my career I learned a method that uses natural language to extract the entities and relationships. If you clearly describe the scenario in text, you can find tables as nouns and relationships as verbs. As I am just at home at the moment of writing, I will type what I think a restaurant does. In a real-world scenario, this is something you will have to check with a subject matter expert. The description is below.

A customer makes a reservation, either online of by phone. The date, time, and the number of people are noted to make sure that a table is reserved. At the day of the reservation, An employee welcomes the customer and leads them to the table where they will be seated. The waiter asks if the customer wants to order any drinks or food. Once the order is taken, the chef and barkeeper will prepare the order. Once the order is prepared, the waiter will bring it to the table. The customer can order everything at once, but most of the times they make a few orders over the evening. At the end of the evening, the customer will ask for the bill. This is a list of all the ordered items and their prices, with a total on it. The customer will pay the bill and go home. The waiter will clean the table and prepare it for the next customer.

We can highlight all the nouns and verbs to get us going.

A customer makes a reservation, either online of by phone. The date, time, and the number of people are noted to make sure that a table is reserved. At the day of the reservation, an employee welcomes the customer and leads them to the table where they will be seated. The waiter asks if the customer wants to order any drinks or food. Once the order is taken, the chef and barkeeper will prepare the order. Once prepared, the waiter will bring it to the table. The customer can order everything at once, but most of the times they make a few orders over the evening. At the end of the evening, the customer will ask for the bill. This is a list of all the ordered items and their prices, with a total on it. The customer will pay the bill and go home. The waiter will clean the table and prepare it for the next customer.

Extract tables

It might seem everything is highlighted. Not all nouns will become a table. Some nouns will become a field of a noun, and some nouns are a type of a more abstract noun. Lets give you an example.

Noun as field

The date, time, and number (of people) are all nouns but are clearly details of the reservation. So we put the Reservation on our diagram as an entity with the other nouns as properties.

Noun grouping

The other option, the more abstract noun, can also be found in the text. We see a waiter, barkeeper, and a chef. These are all employees. With this one it is a bit less clear if we need to put it into one Employee table, or store it in separated tables. A question you can ask yourself is what data you need to store for this noun? If this is similar for the nouns, it is best to keep it simple and put it in one table. In our description we can imagine we need to store a name, hour wage, employee number, phone number, email. So I would say this can be all put into one table. We could add a choice field where we can register what type of an employee we are dealing with.

The same can be said of food and drinks. These are both Items with a price (another noun).

Nouns we don't save

With both the nouns/entities and verbs/relationships we have to consider is we actually want or need to register this data. These is one noun that I don't think is of any value. we have worked away a few of the nouns, so can you pinpoint which one I mean?

Answer

The rest of the nouns can be added to our diagram. We now have the tables that we need. I have added some fields that we want to store to our tables. Most of the times this isn't the hardest thing to determine. It is always a good thing to double check with a subject matter expert. The tables are listed below.

Extract relationships

Noun-verb-noun

Now that we have all the tables, we need to understand how the tables relate to one another. we have to search the text and see where we can make the following combinations:

Noun - verb - Noun

The first one is quite easy again. Customer makes Reservation. I put the nouns in capital and verbs in lowercase to easily distinct them. This clearly is a relationship between the two tables. We can directly think of the cardinality. We discussed this option before. A customer can make multiple reservations. A reservation is make by one customer. So this is a one-to-many relationship.

Verbs we don't save

We can now go over all the verbs. As mentioned before, not all verbs are something we need to register. For example, the second verb, noted, can be stored in the database. The first question is if we actually need to save this. You could argue that we only need to know if someone is coming. Who puts it in the system might not be necessary to know. Something else I want to point out is that many systems already register this type of (meta)data. Both SharePoint and Dataverse have system fields for this. By default, the the system stores who entered the record (Created) and when (CreatedBy). The same counts for the last modification made (Modified & ModifiedBy).

For this reason it is good to understand what the platform has to offer itself. For Dataverse this also counts for the tables. Every Dataverse instance comes with tables. This is the Common Data Model. We will not go into detail on that, but it is good to mention that there is a users table which is used in those Created and Modified fields.

There are more verbs that I don't think are of any value to store in the database. Who welcomes the customer and leads them to the table aren't as relevant in my opinion. You could argue that it is nice to know who welcomes a customer for when they give a review (not in the description). If something bad or good happened at welcoming, you might want to know which employee was involved. But you have to be aware that this actually needs to be recorded. This is an administrative task. We can make it easier with an app, or automate it with a flow, but it still must be done. It's something you need to balance out.

Continue with noun-verb-noun

We do want to know at which table the customer is seated. This can be done directly when the reservation is made, but from my personal experience I know that most restaurants count the reservations for a night, and reserve the tables. When the customer comes in, they can pick a table of choice. If you want to make the booking at reservation time, you nee to know the capacity of the table to make sure that the Number of people of the reservation first the capacity of the table. The model is now as follows.

Then we get to the section where a customer can order food or drinks. We already put the food and drinks into an Item table. We might want to make it more clear that we are dealing with menu items. I renamed it to MenuItem.

Although the description says that a customer makes an order, we know that this is not the same customer who made the reservation. Anyone at the table is allowed to make an order (in most cases). We don't want to get all their names, so it's easier to link an order to a table. If you walk to a waiter to ask for something, they always want to know at which table you are seated. Such a nerdy question isn't it?

So, we relate the Order entity to the Table entity. An order is always related to one table, but one table can have many orders. You should know how to draw this by now.

Now it get's a bit trickier. We know that when an order is made, we need to say which items we want. An order can have many items, and an items can be related to many orders. So we are dealing with a many-to-many relationship. Then we get to the question if we model it as such, or change it to two one-to-many relationships. Can you think of what is the best option here?

Answer

According to the description, once the order is made, it needs to be prepared. Who this does isn't as relevant in my opinion. So the whole employee table doesn't have to be linked. But there is something here that is of interest. When you order something, you want to know if something has been delivered already or is still being prepared. As this is such an important aspect of a restaurant, I would always ask the subject matter expert on how this is actually done. For now, we will just think of something ourselves.

We have mentioned that we are dealing with food and drinks. The drinks are made by the barkeeper and the chef prepares the meals. Almost every restaurant have the bar and kitchen separated. That'a also why after an order, the drinks are brought all together, and the meals are bundles together as well. Once the that part of the order is done, we want to register it to keep track of what is already brought to table and what's not. We can add a simple boolean to the order table to keep track of this. But we don't want to mess up the kitchen order when the drinks order is finished. I think the best option would be to have some logic in the app of the waiter. When he submits the order, it is split into two orders, one for the kitchen, and one for the bar.

There is not just one way of solving this issue. We could also put it all into one order, and every OrderMenuItem will be tracked. The reason why I opt for making it into two separate orders (for now) is that this way the kitchen and the bar aren't interfering each other. Another advantage is that when we opt for tracking every individual item, we need to update more records. This also must be registered. We could do a batch update, but that leads to more traffic. These aren't crazy numbers, but I like to keep it simple. For now, I will just add a note to the order table to keep aware of this fundamental logic.

Data balancing

This part of the modelling is based on experience. Someone else might come up with a much better solution to this. What I do want you to keep in mind is that every process must be facilitated by your model, but also the model needs data.

If you model too little, you will not capture the whole scope of the business process. If you model too much, this will result in either to much data entry moments, or an empty data model, which leads to lower data quality. It's a balancing act, really.

At the end of the night, the customer will ask the bill. We will need to know all the ordered items from a particular table. That means we have to relate the OrderMenuItem table to the Bill table.

But as I am drawing it, I am wondering if we should alter the Order and Table relations slightly. The reason for this is that we now have to retrieve the table through the Order table. This is perfectly doable, but this implies we can only move an order to a different table. Having the option to move OrderMenuItems to a different table rather than the whole Order, will help with splitting the bill for a table, rearranging when rain is coming, or when a group splits up. This gives us much more flexibility.

What if we relate the OrderMenuItem directly to a Table? During the creation we know exactly which table we are dealing with. We could still create an order record and relate the OrderMenuItem record to that. I have renamed the OrderMenuItem table to Order, as it isn't modeled as a junction table anymore. It would then look like this. I have removed the Employee table from the ERD, as we don't need that table for the current description (in Dataverse we have the user table for storing who created which record and SharePoint hassimilar functionality).

Note that now every item ordered is a single record in the Order table.

Iterative process

I intentionally switched the Order setup back and forth. The reason why you want to start with drawing an ERD instead of directly building it is that this way you have the flexibility to quickly think of the implicated capabilities of you model. You can draw it and make adjustments when you think this is required based on the requirements / conversation(s) with subject matter experts.

Existing solutions

You now know how to create an ERD for a new solution. But what if you are asked to work on a project that already exists? You want to get a good overview of the tables and how they relate. An ERD would be nice in this case.

With Power BI Desktop you can click get data and then select the Dataverse option. If you log in with the account that has access to your solution, you can see the different environments and the tables in that environment. If you then select the tables that are in your solution (these can be easily found as your tables will have your solution prefix) you can load the tables into Power BI. If I only use it for an ERD, the DirectQuery is the much faster option. The nice thing about this is that the relationships are also included. Below you can see an example of the two tables from last challenge. You can see that that one-to-many relationship is shown as 1-*. Pretty fast.

The cool thing is that you can see the junction table if you modeled a many-to-many relationship in Dataverse. Go try it yourself.

Additional Information

There is much more to modelling than we discussed in this challenge, like naming conventions and choosing the right data type. There are some best practices on those topics that can be found easily. The main goal for this challenge was to go actually go over the iterative process of creating an ERD yourself.

Key Takeaways

👉🏻 Creating a functional data model is an iterative task

👉🏻 Using an ERD drawing tool like draw.io is really helpful

👉🏻 PowerBI Desktop creates nice diagrams for you