cancel
Showing results for 
Search instead for 
Did you mean: 
Drrickryp

Database design and PowerApps – Step 1 Planning

how-i-did-it.jpg

“Start with the customer and work backwards” – Jeff Bezos

 

“If you don’t know where you are going, it’s unlikely that you will get there except purely by accident, involving innumerable trials and errors” – Me

 

Sometimes it is difficult to know where to start when attempting to create something new.  While PowerApps is a relatively new tool for creating applications, the underlying process, structures, and best practices for designing a database are well-defined and have stood the test of time.  They govern the best practices for creating the part of the database that the End Users never see, the tables of data and the relationships between the tables.   How does this apply to PowerApps? All databases, including PowerApps, consist of two parts.

  • Front-end or User interface
    • Reports that show the data summarized and organized in a logical structure and
    • Forms for inputting and modifying data already in the database.
  • Back-end.
    • Tables of data
    • Relationships between the tables  

The process for developing a PowerApps database application is:

  1. Determining the purpose of the app
  2. Identifying the requirements for meeting that purpose
  3. Obtaining data and breaking it down into its smallest logical parts.
  4. Organizing the data into tables
  5. Defining the relationships between the tables
  6. Refining the tables and clarifying the relationships
  7. Using PowerApps to import the tables and produce the Reports that organize the data and the Forms for entering new data and modifying existing data.

Planning the application starts with the user interface (UX).  THIS IS, BY FAR, THE MOST IMPORTANT STEP IN THE ENTIRE PROCESS!!   It involves assessing the needs of the End User and then formulating the requirements for the app. The more time and thought that is put into this part of the process, the fewer revisions will need to be made later in developing the app. This must be done by consulting with the end users of the application.  Find out who is going to be looking at the reports. Try to engage all the stakeholders who will be involved in reviewing the output of the application to get their input.  Almost inevitably, someone will come out of the woodwork and want to know why nobody asked them about what they wanted and hopefully it won't be after the app is in production.

Start by asking “What is the purpose of the application?” and “What exactly do you expect to get out of it?”. Will it be to send invoices to customers?  Will it be to keep track of the courses that a student takes or keep a record of which students are enrolled in a particular course?  The potential requirements are infinite but are specific and unique to each database.   Only the end users will know exactly what they want to see.  

Get list of the problems they have had before.  Ask “What is wrong with the current process?”.  If there is previous data from a prior partially successful or unsuccessful effort, see if they will show it to you.  If it was a paper-based system that the user wants to convert to a digital one, get copies of the reports and forms that they used.  The best way to work this part of the planning process will be to concentrate on the Reports and work backwards from there to determine what data will be needed.  Sketch out the report or reports and share them with the end user.  Does it have everything they want to see?   Keep refining the process until you as the designer are crystal clear on the what the user wants.  

Once you have an agreement on the requirements, put them in writing so there can be no arguing later that the resulting application doesn't meet the needs.  Include any sketches of the reports that will be produced by the application.  There is nothing more frustrating than working the problem, coming up with an app and then having the customer reject it because it doesn't meet their requirements. It is wise not to proceed until all parties are satisfied.

How does this relate to PowerApps?  If you have had some experience working with PowerApps, then you know that the typical simple app consists of three screens, each that contains several controls. The BrowseGallery screen is a report.  It shows the information in a datasource summarized, sorted or filtered.  The View Screen is also a report that shows the details of a single record.  The Edit screen contains an Input form for adding new information or modifying existing information. PowerApps will produce an application from almost any source table.  well-designed PowerApp will include the following 4 basic functions commonly referred by the acronym CRUD (I kid you not!).  These functions are common to all relational databases..   

  • Creation (Adding a new record)
  • Reading (Retrieving records from the database accurately and efficiently)
  • Updating (Editing and modifying records already in the database)
  • Deletion (Removing records without deleting other information that should be retained)

A poorly designed app will have problems with one or more of these functions and will either not work as expected or become corrupted and increasingly unstable over time. 

 

Summarizing, by far the most important step in creating a new PowerApp is planning with the end users.   Find out the purpose of the proposed application.  Ask the users what they would like to see in the reports and work backward from there. Imagine how the reports will look.  Sketch them out on a piece of paper.  Reach an agreement with the users as to what the app will do.  Once you have a complete idea of what information will be required, move on to the next step,  Relational-database-design-and-PowerApps-Step-2-Data-Gathering?

Take the time and effort to plan your app up front??
goes without saying.gif