We are in the Design phase of a Project Management system, and we have 2 technical approaches to follow:-
SharePoint as the Back-end + Power Apps & Power Automate
SQL server as the Back-end + ASP.NET Core MVC
Now at the beginning, I thought that the requirements can be achieved using the first approach (SharePoint). but the customer start giving us some requirements which i do not think SharePoint will support. one of those requirements are:-
We will have 2 SharePoint lists; Projects + Project Personnel.
Project will have those main fields:-
- ID
- Project Title
- Description
Project Personnel
- ID
- UserName
- Role (Engineer, PM, Data Entry, Senior Engineer)
- ProjectID (reference to the Project list)
Now in our case we have those business rules:-
- Only users who are defined inside the IT Admins Office 365 group can add projects (this is achievable in SharePoint by setting the list permissions)
- User inside the IT Admins Office 365 group can add PM and other Personnel roles (this is achievable in SharePoint by setting the list permissions)
- PM defined inside a Project can add other users to the project, but can not add others as PM. and PM defined inside a project can not add other users to other projects.
so not sure if requirement 3 can be achieved in SharePoint? i know i can do this inside Power Apps, but if a PM user access the Project Personal SharePoint list directly the PM can add users to any project.. now the customer want us to provide a secure solution, so we need to secure the back end. so i do not think SharePoint will achieve this. while if we use SQL we can secure the back-end using a server-side language such as ASP.net core mvc, and we can prevent users (PM for example) from directly accessing the SQL server databases and only allow direct access to the service account.. so can anyone advice if we should use SQL instead of SharePoint ? to implement our requirements in a secure way?
Thanks