Relational Database Principles and PowerApps - St...

Drrickryp · ‎12-05-2018

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

EdHansberry · ‎12-21-2018

@@Drrickryp wrote:

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.

Hi @Drrickryp,

This is a great 3 part series. Thanks for writing it. I have a question about the above criteria for a primary key.

I've used meaningful data as the unique and primary key in the past with no issues (Access and SQL Server.) For example, in the item master, the item number (alpha-numeric) was the primary key in the database, and is soemthing like a 10 character text field. It was very meaningful, it couldn't be changed once assigned, but it could be deleted and recreated again.

For even non-meaningful ID fields, SQL Server has the "dbcc checkident reseed" command to reset the numbers if there have been deletions and you want it to renumber sequently.

Is there something specific to PowerApps for statements above, or is there some other gotcha I may run into.

I am creating a table that will have just two fields, a customs duty code and a duty rate to a SQL table I will be using with PowerApps. Very small, but I was just going to make the duty code the primary key rather than adding a meaningless column. Thus far, I've not seen any issues with PowerApps and our existing tables that have primarty keys set this way, but I am still very early into PA development, so looking for landmines to avoid.

Thanks.

Drrickryp · ‎12-21-2018

It is ok to use non numeric keys and I used to use a unique composite key in the past but this has been discouraged as a rule by Microsoft in favor of sequential integers. I feel that it is a bad idea to expose the key to end users so there is no need for it to have meaning. Some of the rules are more suggestions as a result of bad experiences I've had in the past.

CNT · ‎05-15-2019

HI @Drrickryp you 3 part blog nicely explains RDBMS principles. In Part 1 you had explained about CRUD. Yes, CRUD is the most basic and important senario when working with RDBMS. Could you also tell us how do we map all this theory into PowerApps. Which controls do we use to implement CRUD in a 1-1, 1-m or m-m senario? At present we have to use Edit Form and wire them all up ourselves and sometimes it is a daunting task with lotes of tables. Secondly, I had issues with SubmitForm(), where they were not geeting executed sequentially (although they were inside OnSucess() of the Previous Form). As a result it was trying to add a record to a detailed table before a record in the master table was created and hence referential integrity problems and the Submit collapsed.

Could you kindly give some concrete examples on the Implementaiton side.

Thanks

Drrickryp · ‎05-15-2019

Hi @CNT

Please take a look at Implementing-a-One-to-many relationship in PowerApps in the Community Blog for an example of how to apply the principles to PowerApps development to a real world example. In general, replace the TextInput box with a Dropdown control for entering the One side of the relationship into the Many side on an Edit Form.

In the example of Customers and Orders, the Edit form is navigated to from the Customer gallery and the CustomerID is passed to the Orders Form as a context variable. However, as an alternative for the Order form, a Dropdown control with the Customer table as its datasource could be used to select the customer and the Update property of the card would save the ID field of the dropdown as foreign field in the Orders table. So while the Dropdown displays the customer name, the Update property in the card would save the ID to the Orders table.

Dropdown.Selected.ID

In a Gallery or DataTable showing the Orders, to view the One side of the relationship (Customer), you would use the Lookup() function in the Text a label to display the result in the Orders gallery ie.

Lookup(Customers,ID=customerID,CustomerName)

To do the same in a DataTable of orders, you need to use the AddColumns() function and a Lookup() to show the One side of the relationship.

duncant · ‎01-09-2020

@Drrickryp

What is your recommendation for the CDS lookup columns? Should users create a new entity rahter than user the lookup column?

Drrickryp · ‎01-09-2020

Hi @duncant

To use a Lookup field in CDS you must have another entity as the target. If you have a small number of items in the target you can use an Options field.

duncant · ‎01-09-2020

@Drrickryp

Thank you.

I'm not too familiar with SharePoint List, but in your blog you mentioned users make the mistake of using the lookup type column. Would this be an issue as the lookup column is not associated to an entity (table)? Again I am not too familiar with SharePoint List.

Drrickryp · ‎01-09-2020

The lookup column in SharePoint functions differently when imported into PowerApps. When PowerApps uses CDS, the relationship between entities use a GUID column that is generally hidden. However, is possible to manually create the Parent:Child relationship using primary and foreign keys.

wpryan023 · ‎10-07-2022

For those of us who have some experience with MS Access (which describes me, albeit to a pretty basic level), would it be better to define the tables and relationship structure in Access, and then when it is seemingly working OK, to import the tables into SharePoint, as a starting point when starting with PowerApps? ...or is this just doubling the workload...?

Drrickryp · ‎10-07-2022

@wpryan023

At this point, there is now a way to migrate Access tables directly to Dataverse. https://learn.microsoft.com/en-us/power-apps/maker/data-platform/migrate-access-to-dataverse This is the preferred back end for PowerApps and it allows for synchronization of the Access tables with Dataverse and PowerApps as the User interface. The Access User interface can also be used with the data immediately available to PowerApps and conversely the Dataverse tables are available to MS Access.

Within MS Access it is possible to upload the tables to SharePoint and then use the resulting lists as a back end to PowerApps but in my opinion, this is far inferior to Dataverse as many of the PowerApps functions that are delegatable to Dataverse are not in SharePoint.

wpryan023 · ‎10-07-2022

@Drrickryp , thanks for your quick response. I understand the limitations of delegatable function limitations in PowerApps... My reality is that I need to build a working model as a "proof of function" to my supervisors, to get their buy in before deciding which model to pursue. Because there are financial considerations when going to a Dataverse model, I need them to see it working on a smaller scale first, hence my interest in getting the backend right first. My thinking is that it "should" shorten the development.

Thanks again...

LostinCode · ‎01-23-2023

You mention just naming the primary key ID. Is that so when you name a foreign key, you can use the table name and ID? Like in your illustration of the one-to-many relationship? However, that does show the PK is called orderID within the Order table..

Drrickryp · ‎01-23-2023

@LostinCode

That was a good pick up. In that illustration I used the data tables that Microsoft provides for the NorthWind Sample database. However, I would recommend simply using ID and not using the same name in both primary and secondary keys. Otherwise, you will have to disambiguate them in PowerApps as the formulas will include OrderID = OrderID and create unnecessary problems when you write the filters.