Database Design Fundamentals and PowerApps: An Overview

[Drrickryp]

It is amazing that you can actually create a App with PowerApps without understanding the principles of relational database design.  What is not amazing is how inefficiently the app will function with respect to creating new records, retrieving accurate and usable information, updating existing records that need to be changed, and deleting the right records without removing other valuable information from the database.

PowerApps is a tool for creating database apps from raw data.  A relational database typically consists of a front end in which the users can view reports -  organized summaries of data and forms - which allow the user to create new records and modify or delete already existing records.   The back end of the database consists of the data itself, stored in tables that are related to each other.

What PowerApps can't do:  PowerApps cannot create data sources and it cannot permanently modify the underlying structure of the data sources that it uses. It relies on data organized into tables and provided by the Common Data Service (now called Dataverse), Sql server, SharePoint or over 150 other parties. It does not evaluate whether the tables of data provided are structurally sound or flawed or follow good practices in database design.  GIGO (garbage in/garbage out) is a database principle!  

What PowerApps can do: PowerApps creates the front end (User interface) of a database within minutes, It can take a spreadsheet table and create a data entry form for it as well as galleries and data tables for searching and displaying the data. It is particularly designed for creating applications that can be accessed via a variety of devices including smart phones, tablets, portable and desktop computers and these apps can be run through browser windows without special software. This is an enormous leap forward in database design and it has an exceptionally flexible  and attractive user interface. 

Unlike traditional database design programs like Sql or Oracle, the PowerApps designer does not need to know programming language to create an app. This has increased the number of potential designers and allowed them to create and distribute fully functioning applications within their organizations.   Since PowerApps uses databases to create an application, the principles of sound database design apply to every PowerApp. Once the designer is aware of the principles governing relational databases, pitfalls can be avoided and much time saved in fixing problems with the app and developing cumbersome work-arounds.

In a database, the data is organized into tables, each with a common subject, purpose or characteristic and linked together in relationships.   The relationships that can be created among the tables enable a relational database to efficiently store huge amount of data, and effectively retrieve selected data. Entire global business enterprises can function on a single well designed database.

The principles for creating a relational database that functions properly are well established.  In this blog, I will try to address the process for designing and creating PowerApps that follow these principles and will work efficiently and are robust, meaning that they will operate accurately over long periods of time.   Understanding how databases work before trying to create one will save the designer hours in working around problems, starting over again and avoiding common pitfalls. 

As in any complicated endeavor, it is helpful to break the project down into a series of steps, each with a precedent and antecedent.  It is no different for developing a new application in PowerApps.  For this blog, I will provide an overview of the process and in subsequent posts, I will dive more deeply into each step. 

One of the strongest features of PowerApps is that it can use data from many different sources and if the data is organized in columns and rows, it can combine them into a single app.  Such tables are made up of columns and rows like in an Excel spreadsheet but depending on the connector they can be referred to differently. For example in SQL, data is organized into tables that have fields and records.  In SharePoint data is organized into lists with columns and items, In the Common Data Service, the tables are called entities with fields and items representing the Columns and Rows.  In one app, there can be many tables from the same source or even tables, lists and entities from different sources. 

In PowerApps, these sources are referred to as Connectors.  The data groups can have different names for the same thing.  However, conceptually they are all pretty much the same. Ideally, each cell in a table (the intersection of the column and row) should represent a single data point (a discrete unit of information).

The key principle in a well-functioning relational database is eliminating redundancy. “The same piece of data shall not be stored in more than one place.”

The steps involved in creating a relational database using PowerApps are as follows

• Planning: Start with the reports and work backwards.
• Data Gathering: getting all the data needed to create the reports. Then examine each data point with the goal of reducing data to its smallest logical components.
• Organizing the data into tables: Each table should be focused on a single subject, purpose or characteristic. Many small tables are preferable to large tables.  If you have a large table, ie. one with many fields, consider if it can broken down into smaller more focused tables.  
• Assigning a Primary key to each table: This is a column that uniquely identifies every row in the table and prevents any duplication of rows. It is also used to reference the table in other tables as described below. It can never be null and it typically is a series of consecutive integers with no intrinsic meaning. Once assigned to a record, it is never changed or reused.
• Setting the relationships between tables using foreign keys:  a field in a table that links to the primary key of another table.  
• Refining and Normalizing the data in the tables: a process for making sure that the data in a table follows certain rules  in order to reduce data redundancy and improve data integrity. The first rule for normalizing a table is to ensure that every cell of a database contains one piece of data.

Most of this process except for the last step can and should be done before actually creating the app. As for the last step, it is often easier to refine and normalize a table once there is data in it. Please proceed to the next article /Database-design-and-PowerApps-Step-1-Planning