One big Sharepoint List or many sharepoint lists

(1) Share

Report

Posted on by carlosguzmanbpa

137

Hi,

I have a power apps collecting the same information for different departments, every department has its own user and the information showed depends on the user's department, the process and the data structure are the same. Performance wise, is it better to store the information in one big sharepoint list with a column called 'Department' or create one list for each department.

Note: Important to notice that the data must be filtered according to the user's department

Thanks in advance,

Regards,

Carlos

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Static 118 on at

Like (0)

Report
Copy link

Link copied!
Hi,

Well. When deciding whether to store information in one big SharePoint list with a 'Department' column or to create separate lists for each department, you should consider both performance and maintainability:

Single List with Department Column

Advantages:
Simplified Structure: Easier to manage one list instead of multiple lists.
Consistent Schema: Ensures that all departments use the same data structure.
Easier to Perform Aggregations: Aggregating data across departments is simpler with one list.
Disadvantages:
Performance Impact: Large lists can suffer from performance issues, especially when filtering and loading data.
Security and Permissions: More complex to set up row-level security and permissions to ensure users can only see their department’s data.
Separate Lists for Each Department
Advantages:
Performance Improvement: Smaller lists will generally perform better with filtering and data operations.
Simpler Permissions: Easier to manage permissions as you can set them at the list level.
Disadvantages:
Complexity: Managing multiple lists can become cumbersome.
Inconsistent Schema Risk: Risk of schema drift if not carefully managed.
Aggregation Complexity: Aggregating data across lists is more complex and may require additional steps or workflows.
Given your requirement that data must be filtered according to the user's department, here are some recommendations:

Use a Single List with Indexing and Views:
Indexed Columns: Index the 'Department' column to improve performance on filtering.
Filtered Views: Create views for each department that filter data based on the 'Department' column.
Row-Level Security: Implement row-level security using Power Apps or SharePoint permissions to ensure users can only see their department's data.

Delegation: Ensure that your data operations are delegated to SharePoint, which means using supported functions and operators that can be processed server-side.
Threshold Management: Be aware of SharePoint list thresholds (default is 5,000 items). Plan for indexing and potentially partitioning data if you expect to exceed these thresholds.
Best Regards,
Anton N

Was this reply helpful? Yes No
Verified answer

Pstork1 69,026 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Since each connection in a Power App points to one and only one list its usually better for this kind of scenario to have a single list and filter things by department. Otherwise you need to build multiple identical apps or manage multiple connectors in the app. The one downside is security. Filtering by department is easy, but that's not really security. User's will have permission to access the list and will then be able to see all the entries. But if security is not a major concern then one list would be best.

Was this reply helpful? Yes No