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?
