Design the Backed when using SQL Server Vs using SharePoint Online

john john 791 Reputation points
2022-11-16T17:09:23.223+00:00

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:

Main Library

  1. ID
  2. document content

Document Revision (can only be edited by the PM)

  1. Revision

Document Version (can only be edited bu the admin username, so we can control it inside power automate)

  1. Version

Are my designing points valid?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,741 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
7,034 questions
SharePoint Server Development
SharePoint Server Development
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Development: The process of researching, productizing, and refining new or existing technologies.
1,430 questions
SharePoint Server Management
SharePoint Server Management
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Management: The act or process of organizing, handling, directing or controlling something.
2,500 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Haoyan Xue_MSFT 10,841 Reputation points Microsoft Vendor
    2022-11-17T05:31:43.843+00:00

    Hi @john john ,
    According to my knowledge, using SQL Server should not achieve your needs.
    With Power Apps, the user will use their credentials to interact with the SharePoint list which should be possible, to implement in detail I suggest you start a new discussion in the Power Apps Community.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments