Normalizing database is a process of organizing data into tables and columns to reduce redundancy and improve data integrity. It is a common practice for relational databases such as SQL, where data is stored in rows and columns. Normalizing database can help avoid data anomalies, improve query performance, and make data maintenance easier.
However, creating as many tables in database as needed for normalization is not always the best option, especially when developing applications using Microsoft Power Platform.
There might be a trade-off between many tables and optimizing development experience
One of the challenges of using Dataverse is to design the data model that suits the app requirements and the user interface. Depending on the type and complexity of the app, there might be a trade-off between creating many tables and optimizing user experience. For example, if the app uses Display and Edit forms to show and edit data, it might be easier and faster to use one big table that contains all the relevant fields, rather than multiple smaller tables that are related by lookup fields. This way, the app development can be done much more faster when there is no need for connecting data using multiple lookups.
This is the approach that I chose for Customer Analysis project for Business Finland. The goal of the project is to create integrations and user interface for personnel to analyze documents and data related to customers. The app also needs to allow users to save and present different versions of the data or the analysis. To achieve this, I decided to create one big table that contains all the fields that are needed for the analysis. I also created a few smaller tables to store some metadata and lookup values, such as analysis or VAT numbers which will not change.
Sometimes tables with many columns, might be the best solution for development
The basic data for company information (Yritystiedot), current status (Nykytilan kuvaus) and growth vision (Kasvuvisio) can all be changed in any part of the company lifecycle. The analysis phase contains different data from the basic data. Some analysis phases could have columns from company information and current status. When using a display form, you attach the form to one row of the big table. Then you just pick the needed columns for the view instead of creating lookups and collecting data from different tables. Sometimes “big tables”, meaning tables with many columns, might be the best solution for development.
Screenshot of the Business Analysis tool. On the left side there is display form which takes some columns from the big table called Company. In the right side there is edit form which saves analysis row to a small table called analysis that is linked (lookup) to company table row..
Using one big table is more suitable than using multiple smaller tables
In conclusion, normalizing database is not a one-size-fits-all solution, and it depends on the context and the requirements of the app. For Power Platform apps, there might be cases where using one big table is more suitable than using multiple smaller tables. As a developer, you should always consider the trade-offs and the best practices of data modeling, and choose the option that works best for your app and your development solutions.