How we can manage our application for leave/vacation requests to stay below 50,000 unique items per list

Question

As per the SharePoint online limitation, it is recommended to stay below 5,000 unique items per list https://learn.microsoft.com/en-us/office365/servicedescriptions/sharepoint-online-service-description/sharepoint-online-limits , but as i know we can have up to 50,000 unique items per list.

Now I want to start a new project for a company which have 800 employees, to manage the employees Leave & Vacation requests. as follow:-

• Employee submit a leave or vacation request to his/her manager.

• After that, the request can only be edited/Approved by the manager & read-only by the employee who submitted the request

• After approving it >> both the manager and employee will have read-only permission + the HR Team.

Now we need to secure the SharePoint lists >> by defining unique permissions for the items as the process move (as per the above 3 steps). now i am afraid that the limitation of 50,000 unique items can be reached within less than 2-3 years. because we have 800 employees , so each employee can only have 60 leave & vacation requests. so how we can handle this? i read some articles which mention to use multiple SharePoint lists, but this will not be a practical appraoch because we will have to manage many lists, and we are planning to build the UI using Power apps which connect to the SharePoint lists. any advice please?

Answer 1

Why not use folder architecture to keep number of unique permissions lower:

1 Folder for each user. Inside that folder 1 Folder for each status of the approval process

1- User (Folder)
2-- Pending Approval (Folder) With unique permissions for User + Manager + HR Team
3--- ListItems Here
2-- Approved (Folder) With unique permissions for User + Manager + HR Team
3--- ListItems Here
2-- Rejected (Folder) With unique permissions for User + Manager + HR Team
3--- ListItems Here

This way instead of have unique permissions for each item, you will have total of: Users * (Number of approval steps).

800 + 3 = 2400 unique permissions.

Since each folder only counts for one unique permission.

After that, just keep moving the list items between each folder to manage the permissions for the item.

Answer 2

The way I would handle this situation is by trimming old items using a scheduled Power Automate Flow.

A leave request is going to have a start date and an end date. Once the end date arrives and the employee is back from their leave, what use is the leave request?

If there is no need to keep a record / archive of old leave requests, you could set up a scheduled Flow to run after business hours and just delete any leave requests who's end date is today or older. (Or yesterday or older, if you want to give an extra day buffer.)

If there is some need to keep the historical data of old leave requests, you could set up an "archive" list, and copy the old items from the main leave request list to the archive list, and delete the item from the main list, and set up permissions on the archive list at the list level to give permissions only to those who might need to access the historical data.

That would make it so that the list that grows and grows over time has permissions set at the list level and therefore won't ever run into the unique item limit, and the list that has permissions set at the item level is continually trimmed of items to stay below the unique item limit.