cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Database normalization and PowerApps. Looking for suggestions

On my most recent project, I have a dilemma. Should I normalize my database as much as possible or not. Which one would make it work faster with PowerApps?

 

Here are two databases:

Database 1. Simple SQL database with one table

 

CREATE TABLE TravelRequest (
    TravelRequestID int IDENTITY(1,1) PRIMARY KEY,
    TravelerEmail varchar(100) NOT NULL,
    TravelerDisplayName varchar(100) NOT NULL,
    DateFrom date NOT NULL,
    DateTo date NOT NULL,
    Destination varchar(50) NOT NULL,
    International bit,
    Description varchar(1000),
    CostHotel int,
    CostMeal int,
    CostTransport int,
    CostOther int,
    FirstApproverEmail varchar(100),
    FirstApproverDisplayName varchar(100),
    FirstApproverApproval bit,
    FirstApproverComment varchar(500),
    SecondApproverRequired bit,
    SecondApproverEmail varchar(100),
    SecondApproverDisplayName varchar(100),
    SecondApproverApproval bit,
    SecondApproverComment varchar(500),
    ThirdApproverRequired bit,
    ThirdApproverEmail varchar(100),
    ThirdApproverDisplayName varchar(100),
    ThirdApproverApproval bit,
    ThirdApproverComment varchar(500),
    DateCreated datetime,
    DateModified datetime,
    EmailCreated varchar(100),
    EmailModifed varchar(100)
);

 

 

Database 2. SQL database with relationships and normalization

 

CREATE TABLE Person (
    PersonID int IDENTITY(1,1) PRIMARY KEY,
    Email varchar(100) NOT NULL UNIQUE,
    DisplayName varchar(100) NOT NULL,
    Department varchar(4),
    OfficeLocation varchar(100),
    JobTitle varchar(100),
    DateCreated datetime,
    DateModified datetime,
    EmailCreated varchar(100),
    EmailModified varchar(100)
);

CREATE TABLE TravelRequest (
    TravelRequestID int IDENTITY(1,1) PRIMARY KEY,
    PersonID int NOT NULL FOREIGN KEY REFERENCES Person(PersonID),
    DateFrom date NOT NULL,
    DateTo date NOT NULL,
    Destination varchar(50) NOT NULL,
    International bit,
    Description varchar(1000),
    CostHotel int,
    CostMeal int,
    CostTransport int,
    CostOther int,
    DateCreated datetime,
    DateModified datetime,
    EmailCreated varchar(100),
    EmailModifed varchar(100)
);

CREATE TABLE TravelApproval (
    ApprovalID int IDENTITY(1,1) PRIMARY KEY,
    TravelRequestID int NOT NULL FOREIGN KEY REFERENCES TravelRequest(TravelRequestID),
    PersonID int NOT NULL FOREIGN KEY REFERENCES Person(PersonID),
    Approval bit,
    Comment varchar(500),
    DateCreated datetime,
    DateModified datetime,
    EmailCreated varchar(100),
    EmailModifed varchar(100)
);

 

With high level of normalization it means that PowerApps would have to do multiple calls into database (LookUp() ) for every single record in one table. This would probably slow it down. On the other hand, if no normalization is used PowerApps would have to retrieve information only once and no more calls are needed. For example, if I want to show an approver all requests where he is an approver I can do a simple Filter in Gallery.Items:

 

Filter(
    '[dbo].[TravelRequest]',
    FirstApproverEmail = _UserProfile.Email ||
    SecondApproverEmail = _UserProfile.Email ||
    ThirdApproverEmail = _UserProfile.Email
)

 

And then display easy information for each Gallery Item

Example Label1.Text:

ThisItem.TravelerDisplayName

 

On the other hand, if I use normalized database it will be a lot harder to show all the request.

First I would get all TravelRequestID where he is approver

ClearCollect(
    _TravelRequestIDs,
    Filter(
        '[dbo].[TravelApproval]',
        PersonID = _UserProfile.PersonID
    )
)

Then in Gallery.Items:

 

Filter(
    '[dbo].[TravelRequest]',
    TravelRequestID in _TravelRequestIDs
)

 

And now for each gallery item the Label1.Text:

LookUp('[dbo].[Person]', PersonID = ThisItem.PersonID,DisplayName)

 

That is a lot of database calls comparing to just 1 with using simple one table database.

 

In my head it would be way more efficient to do the one table database even though all my learning on SQL databases says that it’s the wrong way of doing it.

Maybe I am wrong and having that many different calls to database is not that bad in PowerApps but I assume it just increases the risk of getting Server Returned an Error. Imagine if an approver has 100 records that matched. PowerApps would LookUp 100 times to get the name, I am sure this would hit some sort of threshold.

What are your thoughts on Normalization and PowerApps. Would you recommend the 1 table database or more complex database?

2 REPLIES 2
Community Support Team
Community Support Team

Re: Database normalization and PowerApps. Looking for suggestions

Here is my own suggestion:

Create the corresponding tables in SQL DataBase with relationships and normalization, then under PowerApps, generate those tables with the information needed into one table, with the Addcolumns, RemoveColumns function in PowerApps:

AddColumns, DropColumns, RenameColumns, and ShowColumns functions in PowerApps

After that, save this one table into collection and do the query locally in PowerApps.

 

There are still some more things need to be done regarding Data connection for PowerApps. For Example, if currently querying View is supported, then it would be much easier to perform query in PowerApps with the actual data from SQL Server Database.

There are ideas submitted on this:

PowerApps must also see SQL Azure Queries, not only Tables

Please vote for it if it helps.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Database normalization and PowerApps. Looking for suggestions

Hi @v-micsh-msft

 

Thanks for your input, however thinking about using Collections I came to the same problem. When I will be building the one collection I will still have to make same amount of calls to the back end so it does not matter if I use collection or not it will still be same 100 LookUps for each record in the collection?

 

And after that if I have collection I will need to constantly refresh it to make sure that data is up-to-date.

 

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,858)