We want to build a document management system where each document will have the following properties:
1) ID - system generated
2) version - system generated
3) Revision- only user with PM role can modify it (we will have different roles; engineer, PM, QA, Coordinator)
4) the file content it-self (PDF, Excel,Word, etc)
If we use SQL server as a back-end, we can have a single table storing all the fields, then inside the .NET code we can force all the business rules. So if the login user has a PM role they are allowed to update the revision, otherwise the request will be rejected and we will not expose the version to the users, and always manage it inside the code (as it is system generated)--as the end users do not have direct access to the database, and accessing the database can be restricted by the service account.
While using SharePoint we will need to split the tables based on the permission, since SharePoint lists will be accessed by all users and if we use Power Apps, then users will interact with the SharePoint lists using their credentials (unlike SQL server where users interact with it using the service account). So in SharePoint we will need to split the fields into those libraries and lists:
- document content
Document Revision (can only be edited by the PM)
Document Version (can only be edited bu the admin username, so we can control it inside power automate)
Are my designing points valid?