Relational Database Principles and PowerApps - Step 3 Keys and Relationships

[Drrickryp]

This blog pIt could work!!ost is the fourth of a series and it builds on the concepts and terms discussed in the preceding three posts.  At this point the designer has acquired all of the necessary data points and organized them into tables, each of which defines a single characteristic.  At this stage, it is important to add a Primary Key to each table and then link the tables together using these keys.  There are three possible relationships between the tables.  One to one, One to many and Many to Many.  This post will demonstrate how to connect the tables using the keys and how to deal with each type of relationship in the PowerApps setting.  

Primary and Foreign Keys

Primary Key

In a relational database, every table must have a Primary Key.  The requirements for a primary key are as follows:

• Each row of a table must have a column that is designated as the Primary Key. It is typically is referred to as ID. In some cases, the primary key takes the name of the table for example CustomerID.  However, for use in PowerApps, it is better to simply call the Primary key of a table just ID as if it has the same name in another table, there can be a problem with disambiguation.
• The primary key must be unique
• It cannot be reused even if the row is deleted
• It cannot be null
• It can never be changed once it is assigned.
• Ideally, it should have no significance or meaning.
• Some database programs automatically generate a Primary key like SQL and SharePoint, otherwise the key must be generated manually.
• The Primary key can be numeric or alphanumeric or text, but it must be unique within the table.
• There is a strong preference for the key to be an integer because this minimizes the size of the data.

 Foreign Key

The foreign key defines the relationship between tables.  The foreign key is defined in a second table, but it refers to the primary key or a unique key of another table. Every table must have its own primary key but it may have one or more foreign keys connecting it to other tables.  All tables in a relational database must be connected to each other. Foreign keys are the way tables are connected in the database.  The table containing the foreign key is called the child table and the table containing the Primary key is called the parent table. The foreign key usually takes the name of the table that it comes from.  If the Customers table has an ID column, it is usually referred to as CustomerID in the second table.

Relationships

There are three possible relationships between tables.  They are:

• One-to-one
• One-to-many
• Many-to many

One to one relationship

In a one to one relationship, there is one record in a table linked to one and only one record in a second table.  In the design, the primary key of both tables is the same.  These relationships tend to be rare in relational databases because all of the fields can be combined into one table . However, there are reasons for having one to one relationships.  For example, if only a few records have a detailed description then including this column in the table would result in lots of blank spaces. This could result in performance issues. If a table has 20 attributes, and only 4 of them are used occasionally, it makes sense to break the table into 2 tables to improve performance. Another reason could be security and privacy issues as in the figure.  There are good reasons why someone viewing the employee table should not be able to see the employee’s payroll information.

One to Many Relationship

One to many relationships are the most common type. In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders.

A one-to-many relationship looks like this:

One customer can have many orders, but each order can only have one customer.  In this example the primary key field in the Customers table, ID, is designed to contain unique values. The foreign key field in the Orders table, CustomerID, is designed to allow multiple instances of the same value.

In PowerApps, the user may want to see all of the orders made by a particular customer in one gallery. This would be done by filtering the Orders table on the CustomerID.This relationship returns related records when the value in the CustomerID field in the Orders table is the same as the value in the ID field in the Customers table. In PowerApps they can be represented in many ways.  Usually with a Display form on top showing the one side of the relationship and a gallery below showing the Many side of the relationship.  Alternatively, a Dropdown control could be used to select the One side of the relationship and a datatable filtered on the Primary key in the Dropdown to show the Many side. In the screen shown in the next panel, Orders are selected by their number in the dropdown control. The details are shown in the form beneath the Dropdown and all of the other orders by the same customer are shown in a datatable beneath the Display form.

Many to Many Relationships

The final type of relationship is many to many.  Novice designers have the most problems with this type of relationship. Consider the Student/Class relationship.  One student can have many classes and one class can have many students.  A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table.  A problem would arise if you wanted to assign a grade to a particular student. It wouldn’t fit in either the Students table or the Classes table. To avoid this problem, break the many-to-many relationship into two one-to-many relationships by using a third table, called a junction table. Each record in a junction table includes a matching field that contains the value of the primary keys of the two tables it joins and In the junction  table, these match fields are foreign keys. These foreign key fields are populated with data as records in the join table are created from either table it joins. Then, data like grade or faculty comments would be included as fields in the junction table.

For a report, either side of the Junction table would be the Parent and the Junction table would be the Child.  For example, a report showing all of the students enrolled in a particular class, the class would be selected in a Dropdown control and a gallery with the Junction table as its Items property would show all the students.  The name of the student would be found by Looking Up the corresponding name from the StudentID in the Junction table.

Once the relationships between the tables are modeled and assigned.  The next step is to put some sample data into the tables and check for the following: 

First Normal Form (1NF)

The first Normal Form rule states that all information must be stored in relational tables and that the intersection of each row and column contains atomic values I.e. a single piece of data or one fact in a single database cell.  Also, there cannot be repeating groups of columns.

The SharePoint Atomic bomb in PowerApps.

In SharePoint, lists can contain People, Choice and Lookup type columns. These are an embedded table in a single cell of the list.  This violates the 1NF rule of not having more than one fact or data point in a cell in a table While this may be a great shortcut feature in SharePoint. IMHO, it has been one of the biggest problems that novice designers encounter when they use SharePoint lists as a data source for PowerApps. What may be a helpful short cut in SharePoint is a source of frustration for the PowerApp designer.   It is extremely difficult to add items, edit or modify items in the embedded tables and virtually impossible to delete an item from them from within PowerApps. While numerous work-arounds have been described., I simply find it easier to avoid using these types of columns and instead use separate lists and link them by using native PowerApps functions in one to many relationships. This can easily be done in the Front-end.  You can create galleries, edit and display forms for these lists if you want to add new records or modify the ones already in the list.  There is an excellent You Tube by Shane Young @Shanescows explaining how this is done at https://www.youtube.com/watch?v=43ekj5MlNJU.  

If you create a PowerApp using lists with Choice, People and Lookup type columns, you will likely discover the problems that I have referred to above.   To avoid them, review @WarrenBelz 's blog post here:  https://www.practicalpowerapps.com/data/constructing-your-first-power-app-before-you-start/ 

Summary

By simply following Best Practices in selecting and examining data and building well-designed tables, you will avoid many problems.  Your PowerApps will continue to function over time without corruption and changes can be done without disrupting the entire Back-end.  In the next blog post, I will review the process for creating a PowerApp from well-designed tables. Relational Database Design fundamentals: Implementing a One-to-many relationship

Give my Creation .... LIFE