Frontend is one thing, but we shouldn't forget about the backend. Designing data structure for Power Apps enables efficient data management and streamlined application development.
Relationships
The main building block in designing a data structure is relationships. There are two relationship types: One-to-many 1:N and Many-to-many M:N.
1. One-to-many (1:M)
One-to-many (1:M) relationships connect one record in a primary table to multiple records in a related table. This structure simplifies data organization and streamlines query performance. E.g. One employee can request Time off several times.
2. Many-to-many (M:N)
Many-to-many (M:N) relationships link multiple records from one table to multiple records in another. This flexible structure supports complex data models and enhances interconnectivity. E.g. Multiple Business partners are dealing with multiple colleagues of yours.
Setting up Relationships in Dataverse
1. One-to-many (1:M)
Many-to-one relationships are defined in a lower granular entity (in our case, "Time off requests"). A many-to-one relationship is established by adding a new lookup column to the related entity (in our case, "Employee").
This setup defines the many-to-one relationship between Time off requests and Employee. M:1 relationship is created automatically.
2. Many-to-many (M:N)
Setting up many-to-many relationships in Dataverse involves creating a link between two tables through an intermediary entity.
This structure allows multiple records from one table to associate with multiple records from another, enhancing data integration and flexibility.
In the backend, an intermediary entity is created – in this case, it's "sbid_businsesspartner_sbid_colleagueset" (the relationship table name with "set" appended at the end). This entity can be referenced in Power Automate, etc.
Best practice for Data modelling
Normalize. Normalize. Normalize.
It´s always better to set up data structure in normalized way. Always go into lower granularity in structuring tables and linking tables together with 1:M relationshis.
In our case, we would have an Employee table with a choice input for Employee status and date inputs for Start Date and End Date. That´s incorrect.
Best practice is to create an Employee table where the Employee status is a calculated column based on the current employment status, and where Start Date and End Date are derived accordingly.
In conclusion, setting up relationships in Dataverse is key to unlocking seamless data integration and robust performance.
By leveraging calculated and rollup columns and following best practices, you ensure your data models are efficient and scalable. This approach empowers you to build smart, flexible applications that drive better business outcomes.
