There’s another key step we must take before we start the fun bit of building a canvas app for my wife’s business. We need to scope out what tables we might need for storing data and what the relationships are between them. It’s time to draw out our ERD – Entity Relationship Diagram.
Entity Relationship Diagrams are a critical tool for understanding & designing relational database systems. By visually showing data tables and their relationships, we can articulate data concepts in an easy-to-digest fashion. They should also align with the requirements and user stories for your Power Platform solution.
An ERD is a common step in solution architecture, which should be drafted and agreed before any ‘build’ work starts. It’s no different to a construction company building houses. They’ll have technical diagrams for measurements, plumbing works and electrical wiring before they start any build work (though you might debate this if you’ve ever bought a new build house from Taylor Wimpey).
In solution delivery, it’s absolutely imperative to understand where your data is coming from first. With this understanding, we can better identify relationships and dependencies with the data, using that insight to make the correct design decisions for our solution. Diving straight into building a canvas app or Power Automate flow without real consideration for your data model is likely to cause a degree of technical debt. Safeguard your clients or organisations by taking the time to architect properly – that starts with your data, every time.
Benefits
Aside from being an architectural best practice, there are some key benefits to drafting and agreeing an ERD with stakeholders before anything else:
Database optimisation
This is massive, especially when you’re looking at building a larger Power Platform solution. Understand the mechanics needed to access & cleanse required data, to ensure users of your solution have the best possible experience.
Clarity
A well-constructed ERD will give a clear overview of the data model structure, making it easier to understand complexities.
Visualisation
If you’re anything like me, you like to see things visualised as they’re easier to understand. Drawing out a data model design easily articulates what’s being used and where the relationships and dependencies are.
Documentation
I’m big on documentation. Yes, it’s boring and we all prefer the ‘fun’ stuff of coding and building, but a solution without good documentation is like a scaletrix without cars – useless.
An ERD should be an absolute must for all solution documentation. In some places, I’ve seen this be mandatory as it’ll need to be approved by an internal Design Authority or Architecture Guild, before you’re allowed to start a solution build. Adding a screenshot or URL to your ERD design into your documentation is a great habit to get into.
Key components
A well designed Entity Relationship Diagram should include the following components:
Entities
An entity is a table of information that we want to represent in our data model. It’s an important terminology to get used to when working with Power Platform solutions that reference Dataverse, or other relational database systems such as SQL or Oracle.Â
I stress relational here and sorry to annoy all the SharePoint fans reading, but it’s not a relational database. It simply does not support complex data relationship types and was never intended to.Â
Relationships
Relationships describe how one table relates to another, usually by at least 1 piece of unique information that’s shared.
In our example of my wife’s beauty business solution, a client will have a relationship with scheduled appointments once they’ve booked in for a treatment. An appointment could consist of a single or multiple treatments, so there’s another relationship.
Attributes
Depending on how granular you are with your ERD’s, you might also wish (or need) to show attributes for your entities as well.
In our example, our client table will have attributes for Client name, their email address and a unique client ID.
Relationship types
An Entity Relationship Diagram will show relationships between tables, but to do that we need to understand the types of relationship and how to visualise them. Below is the four most popular relationship types, there are more but I’ll cover those in a future follow-up article.
One-to-One (1:1)
This shows a single entry in one table being related to a single entry in another table. An example of this might be a birth certificate. Each person only has one birth certificate, and each birth certificate belongs only to one person.
This is how a one-to-one relationship is typically visualised on an ERD:
One-to-Many (1:N)
This shows a single entry in one table being related to multiple entries in another table. An example of this might be a professional football squad. Each squad can have many players, but a player can only belong to one squad.
This is how a one-to-many relationship is typically visualised on an ERD, with the ‘many’ symbol being commonly referred to as a ‘Crow’s Foot’:
Many-to-One (N:1)
This shows multiple entries in one table being related to a single entry in another table. An example of this might be students at University. Lots of students can only enroll on one course, but a course can be taken by a number of students.
This is how a many-to-one relationship is typically visualised on an ERD:
Many-to-Many (N:N)
This shows multiple entries in one table being related to multiple entries in another table. An example of this might be patients registered to a Doctors surgery. A surgery will have many patients, whilst patients can be seen by many doctors.
This is how a many-to-many relationship is typically visualised on an ERD:
Examples of ERD's
As I type, I’ve got the wife in my ear asking me “why can’t we just build my app already?”
Because you need to get your data model right first!
The wife's business solution
Let’s use her business as the first example. It’s important for us to get this right, so our eventual Power Platform solution will work as she needs it to.
I’ll start by listing out the potential tables and their relationships with each other:
Clients and Treatments
– Many clients can have multiple treatments
– Many treatments can be offered to multiple clients
This would therefore be a many-to-many relationship, as clients can receive multiple treatments, and each treatment can be offered to multiple clients.
Clients and Appointments
– Each client can have multiple appointments
– Each appointment belongs to a single client
This is a one-to-many relationship, as a client can have multiple appointments, but each appointment is associated with only one client.
Treatments and Appointments
– Each treatment can be scheduled for multiple appointments
– Each appointment can have multiple treatments
This is a many-to-many relationship, as each appointment can include multiple treatments, and each treatment can be scheduled for multiple appointments.
This is what our ERD therefore looks like:
The weekly food shop
Back in my SQL Data Warehouse & Business Intelligence days, I used the weekly food shop as an example to explain star-schema database design. I’d use the date of the shop & its total value as my Fact table, with store, store location & items as my dimensions. This was a real-life example of relationships between data, so feels fitting to recycle it for Power Platform data model design too.
Customer and Items
This is typically a many-to-many relationship, as a customer can purchase multiple items, whilst an item can be purchased by multiple customers.
Customer and Purchase
This is typically a one-to-many relationship, as a customer can make multiple purchases over time, but each purchase is associated with a single customer.
Supermarket and Items
This is typically a one-to-many relationship, as a supermarket can have multiple items for sale, but each item is associated with a specific supermarket.
Purchase to Supermarket
This is typically a many-to-one relationship, as multiple customers can buy items at the same supermarket, but each purchase is associated with a single supermarket.
We might end up with ‘weekly food shop’ ERD similar to below:
Tips for ERDs with the Power Platform
When it comes to Entity Relationship Diagrams for Power Platform solutions, I always try to think about or remember the following:
Entities are tables; as mentioned earlier, this is a key terminology to get to grips with if you’re using Dataverse.
Consider security; a high-level ERD may only show relationships, but not who needs to access the records and with what capacity. Factor table or item-level security into your data model plans.
Remember to use solutions; if you’ll be using multiple Dataverse tables with defined relationships, make sure you’ve got them all inside a solution.Â
Good ERD building tools
There’s quite a few out there. Personally, I’m a fan of Lucidchart which is what I’ve used to construct the images for this post.Â
If that doesn’t float your boat, you can always try draw.io, smartdraw or creately, all of which have ERD templates ready to use and customise.
If working purely with Dataverse, there’s a couple of add-ins to the XRM Toolbox that automate ERD creation for you. Special mentions to Carl Cookson for ERD Visio Builder and Bas van de Sande for Entity Relation Diagram Creator.
Do you use or recommend any other tools for creating Entity Relationship Diagrams? Let me know in the comments below!
GREAT post!!
pro tip: you can use the Entity Relationship Diagrom Creator in the XRM toolbox to visualize solutions in the Dataverse.
In the next period of time I’ll be working on DrawIO support as well 🙂
Thanks Bas! I’ll update this post soon to reference your tool. Just added it to my XRM Toolbox install and it’s awesome!