Struggling to decide where to store your data?
The success of your next project could ultimately come down to where you store your data. Within the Microsoft ecosystem, there lies a heavy decision when deciding between the use of two of their more "user-friendly" options for saving data: SharePoint or Dataverse?
SharePoint is a solid contender for data storage when you understand the boundaries of the tool as it is not a relational database or a database, at all. As a result, it will not grant you the same functionality as the Dataverse. SharePoint is great for projects where lists with limited pivoting to one another may be more appropriate as it supports formulas for columns and is great for small volumes of data. It's also a useful resource that does not incur an additional cost on top of your Microsoft 365 Business Basic license. Cost can't always determine the solution as SharePoint has some significant drawbacks that may not be viable for your project. Before selecting SharePoint, consider:
Delegation queries are limited so the number of records returned will be incomplete
It lacks full audit capabilities
It does not support business rules, logic, and validation
It requires users to recreate lists if importing Power Platform solution into another environment or tenant
When deciding whether to use SharePoint to save my data, I think about the structure of my data set. Is it mainly row-centric where columns are not dependent upon one another. If that's the case, I am not trying to relate columns based on the values in the rows so SharePoint makes sense.
The Dataverse differs from SharePoint in every way as it is a relational database with a cost associated with it. Subsequently, the features and functionality available to you allow for more robust and complex projects. As an added convenience, when packaged within a solution, you do not have to manually configure the database when switching environments or tenants.
The Dataverse supports business process automation logic based on your data and allows its customers to secure their data on multiple levels from a role, database, and field (or column) level. Out of the box, the Dataverse comes with auditing features which is integral for change management.
Despite the advanced features within the Dataverse, it still falls short of expectations as it pertains to limited calculated and rollup column types though SharePoint is similarly limited. As a workaround, you can create these custom calculated fields in Power Bi or PowerApps, but it would be great if the Dataverse functionality was better. Microsoft recently released Power Fx which is available in preview mode within the Dataverse to support formulas.
Given the sophistication of the Dataverse, creation and maintenance may not be as straightforward for a person who has never created a database where SharePoint has a relatively easy and intuitive column creation.
Ultimately, if your data requires the following, choose Dataverse:
Business process logic based on information in your columns
Securing your data on a column level
Auditing capabilities of your data
Large volumes of data (terabytes)
Relating columns to one or more columns
Create once and reuse
Whereas SharePoint may be a better fit for your project if your data:
Is small in volume
Does not have related columns
Does not require complex field types