Database normalization and PowerApps. Looking for suggestions

[Anonymous]

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?

[v-micsh-msft]

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

[Anonymous]

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.