Data Management Best Practices

[ericonline]

I'm not a database person so I don't know what the traditional data design pattern is for this problem, but here goes. 

• Users have the ability to add any number of items to a single entity
  • Examples: 
    • Photo entity: User can take 1 photo or 40 photos
    • Activity entity: User can add multiple events throughout the day each with durations and comments
• The problem I have is that I end up with a Sharepoint list with 100 columns like: 
  • event1_type, event1_activity, event1_duration, event1_comments
  • event2_type, event2_activity, event2_duration, event2_comments
  • etc. 

How do I approach this better WITH REPORTING IN MIND? Its getting unruly to maintain. 

Thanks

[RusselThomas · Accepted solution]

Hi ericonline,

I'm no specialist either, but this can get quite complicated to explain so I'll try keep is as simple as possible.

Databases help us to relate data - meaning when you have a one-to-many relationship between entities, it's usually better to keep the "one" in one table and reference it in the "many" table.  This helps segment and manage your entities and avoid 300-column tables.

I consider entities to be a 'category' - so in your example the entities would be Events, Photos and possibly Comments

When entity fields (columns in the table) have a one-to-one relationship, then it's probably better to keep the data together in the same table.  Sometimes you have nested one-to-many relationships, which also complicate matters...

So to illustrate this, I'll try work through a simplified example - it might not be identical to your use case, but it's the concept we're trying to understand here;

• One Event has one Title, one duration, one location - these fields have a one-to-one relationship with the Event entity
  • That same event has many Photos
    • Each Photo may have many Comments about the Photo

Based on the above, I would construct my database as follows;

One Event Table

One EventPhotos Table

One PhotoComments Table

Because we often use SharePoint Lists as our tables, you can just replace "Table" for "List" wherever you see it here - the construct is the same - an entity that has columns and rows.

My Event table would have just these Columns

[ID], EventTitle, EventDuration, EventLocation

Where [ID] is a unique reference column in that Table for that row of data. I've put it in square brackets here because when you're using SharePoint, you don't need to create this column - SharePoint creates it for you.  Every single List has an ID column that is unique for every single row of data.

This unique reference is what you use to link data in other tables to this event and is typically referred to as a Primary Key, meaning the key that identifies data in this table.

If you don't see it in your list, it's just hidden - go into the view settings for the list and tick the ID column 🙂

While it's generated automatically and consecutively, it doesn't need to be consecutive - it just needs all the values in the column to be unique.

Now, for photos, comments or any other data that might be linked to the event you would then build a table that logically groups that particular data;

EventPhotos Table:

[ID], PhotoURL, Name, TakenBy, TakenDate, EventID

Where EventID is the value of the Primary Key column of the Event in the Event Table I want to link that photo to.  I have to create this column, and I have to put the data in.  The idea here is that when I upload Photo's, I should already know which event those Photo's belong to, so I can put that Events [ID] number into this column.  This is referred to as a Foreign Key, meaning data that can identify data in another table.

You would then do the same process for the PhotoComments table, except here it's the one Photo you want to link to - so here you need the Photo's [ID] to link comments.

It's important to do this as you create the data - trying to link stuff afterwards will require quite a bit of filtering and manual updates.  If you have one big table, you would need to either start again, or manually pull the data out and put it into it's separate tables, updating the foreign keys as you do - quite an exercise.

Lastly, with reporting in mind I have two words for you - Power BI 

With it you can ues the same techniques to relate data from different tables.- and it can easily connect to SharePoint lists as well as autorefresh data it gets from them - but that's a post for the Power BI community 🙂

All of this is quite hard to explain with just text, hopefully this image will help;

So for the above, if I have an Gallery in PowerApps named 'eventGallery' with EventTable as Items, I could then have a second gallery with

Filter(EventPhotos, EventID=eventGallery.Selected.ID) 

as Items.  This will populate my second gallery with all the photos linked to whichever event is selected in the first gallery.

This approach allows me to fetch all the data related to a specific event from various places and present it on the same screen.

Hope this helps 🙂

RT

[seadude]

Great write up and explanation @RusselThomas! This is exactly what I was looking for (this is @ericonline's non-work persona 🙂 ) Looks like I have to dust off my old SQL notes and get into 1st Normal, 2nd Normal, etc!

How are you mocking up your data relationships (the table images) for Sharepoint? In Excel then screenshot or some Visio stencils? I'd like to approach this part of app building more formally and it really looks like you have a lead on this. Care to share your workflow for building data tables?

[RusselThomas · Accepted solution]

Hi seadude,

I'm definitely no DB designer, so calling my process a workflow is a little insulting to workflows! 🙂
I'm pretty lazy, and nowhere near as structured as I should be.  I tend to build on the fly, which is admittedly not the best practice and has gotten me into some tight corners where I've had to redesign. 

This is an area I should also work on for better apps - I can share my approach, but it's by no means the best, or even a good approach - that's my disclaimer done with!

Designing for me is about finding the right balance between categorising data into logical groups or entities while keeping the number of lookups (joins) between them to the absolute minimum required - then it's about performance, like indexing the columns you're likely to use most for filtering or lookups and using as flat as possible column types - like plain text or number.  This is especially important for delegation limitations and list view thresholds on SharePoint lists when using PowerApps.

It's useful to know what data you need 'at rest' vs what you can derive efficiently at 'runtime'.

For example, person/group columns in SharePoint are a great integration for identifying AD users, but you actually just need the email address of the user to get more profile data in your app using something like the Office365Users connector. 

Filtering on a person/group column from PowerApps always used to cause delegation issues for me, but filtering on an indexed plain text "userEmail" column that you populate yourself is far easier. 

I also stay away from complicated columns like choice and lookups in SharePoint - just personal preference, as I prefer to use PowerApps to define and manage user choices and/or data lookups.

For design and planning, I usually start with pen and paper (or draw in OneNote).  I start with the basic functions of the app, get an idea of the screens, then drop down to required data sources and outline the entities and their relationships - from there, Excel for quick concepts and dummy data creation, MS Access for more specific table linking visualisation.  

The nice thing about using Excel or Access with SharePoint is that you can easily export your tables to SharePoint Lists once you've built them. There are a few drawbacks to using this method though - this is a once-off export which creates the list - I haven't seen a way to update a list from excel without using Flow. 

Excel is also not great for showing relationships, but MS Access has a great visualisation view for relating and linking tables. 

If you're using SQL, the database designer also has a good design view, but this might be overkill.  

Power BI desktop has a similar visualisation capability, but it's more predisposed to having tables with data already available that you can then link, as opposed to designing from scratch. For something more complicated you could check online for free design tools like SQLdbm (I'm sure there are plenty of others), but I can't speak to usability or exportability there.

Other people on this forum likely have much more efficient and structured approaches than I do, so I'd love to see their responses - (I'm looking at you @mr-dang ;)) but in the end it comes down to what works for you and your particular app/data 

Hope this helps, and watching this thread with interest 🙂

RT

[ericonline]

@RusselThomas, 

I'm in the same boat, PowerApps off-the-cuff, everytime. I like the term "canvas" app because I feel like an artist when building! Unfortunately, like you mentioned, this can sometimes lead to rework further down the road. 

I'll look into using the Access table tool (I think I have Access) and at the least, work with Excel to model my data out a bit more. I really appreciate the insights and look forward to working with you in the forums as time goes on. 

Chat soon

[ericonline]

Just jumped into Access 2016 Desktop app to mess around with designing tables and data relationships. I do believe this will take my PowerApps to the next level. This IS indeed a nice tool. 

(Hint: Click "Database Tools" / "Relationships" or / "Database Documenter" to get creative!)

[RusselThomas]

Yep, used to use Access to design way back before SQL got it's own designer - still love it 🙂

[ericonline]

Hi @RusselThomas, 

Actively referring to this conversation here and have another question for you. 

• What about many-to-many relationships? How do I create table connections for these? 

Example: 

• Each event has many volunteers and I might use many volunteers for an event. 

I'm trying apply what you wrote above, but I can't seem to get away from having a column with multiple values in each cell. This is tough to parse and VERY tough to write to. Am I missing something or is this a tough thing to do with Sharepoint lists in general? 

Thanks for your time!

[wyotim]

If I may be so bold, I'll offer my take on the many-to-many. In SQL, I would make a table that has three items: a primary key (ID), a foreign key reference for the event ID, and a foreign key reference for the volunteer ID. That way, you could tie events to any number of volunteers and any number of events to each volunteer. Example:

Events Table:

ID          Event          Date

1           Bowling       2018-08-21

2           Fishing        2018-09-25

3           Curling        2018-10-23

Volunteers Table:

ID          Name

1           Bill

2           Ted

3           Rufus

EventVolunteers Table:

ID          EventID          VolunteerID

1           1                     1

2           1                     2

3           2                     1

4           2                     2

5           3                     1

6           3                     2

7           3                     3

In the EventVolunteers table, we see that Bill and Ted go Bowling (Volunteer ID's 1 & 2 with Event ID 1) and Fishing (Volunteer ID's 1 & 2 with Event ID 2), but for Curling they take Rufus along (Volunteer ID's 1, 2, & 3 with Event ID 3). Many Events, many Volunteers, able to be filtered by either column to check Events to Volunteers or Volunteers to Events. 

To use @RusselThomas 's first example, it would be like if you made a table with just the yellow highlighted columns and an auto-generated ID column. 

I don't know SharePoint that well, so I don't know if you can do those types of relationships or how it handles them, but with SQL it isn't bad at all. I would imagine that you could create a pseudo-connecting table by using a PowerApp to write the ID's as integers to a list in SharePoint that has an auto-generated ID and then use PowerApps/PowerBI to reference them with filtering. They may not be formally connected, but it could work in a pinch. Problems may arise if you delete Volunteers or Events that have IDs in the EventVolunteers table, leaving them orphaned. SQL has ways to deal with this but I don't know about SharePoint. The pseudo-connecting table idea would have to be dealt with manually or using PowerApps to look for the orphaned keys and remove them.

Forgive the intrusion but hopefully this is relevant. 

[RusselThomas]

@wyotim's response is what I would have said - I call it a sandwich table, I think it's actually called a junction table, so another way of looking at his example;

Events:

[ID], Event, Date

Volunteers:

[ID], Name, Surname, Phone Number

EventVolunteers:

[ID], EventID, VolunteerID

Then it's really a matter of what you're trying to show in which control and how you want to show it that will determine your filter/lookup commands.

Kind regards,

RT