Should I realize my database model in Dataverse or should I use an SQL database as data source?

[MSchd]

I am new to Dataverse, so apologies for my possible ignorance

I am creating a Power Apps application for which I created a database diagram. The system is not very large and consists of 12 tables. In the database diagram I drew up the tables, relations and constraints. I have little experience with Dataverse, so I assumed it would be possible to create a database schema similar to SQL. However, now I see you should manually create tables in Dataverse via the UI. This process seems a bit tedious and feels like it would have some drawbacks. I was wondering whether it might be a better idea to run a small SQL instance and use that as a database for the PowerApps application or if it would be better to manually create the tables in Dataverse.

I wonder if operations like dropping and creating all tables during development when making changes will be missed in Dataverse. What is your experience with this?

[cchannon · Accepted solution]

You are correct that setting up schema in Dataverse is comparatively quite tedious, and you are also right to be concerned about things like DROP because that can be tricky sometimes in Dataverse.

The reason to go with Dataverse for your database is not for the core db management or operation, but for all the ancillary capabilities that go along with it. If you have no requirements at all beyond "store data in these tables and make sure the model is flexible," then SQL is a MUCH simpler choice. But if you need complex role-based, row-wise security, or if you need a flexible API that can be easily invoked from REST and can be safely exposed to public endpoints, or if you want to consider Model Driven Apps in addition to Canvas Apps, or if you want to leverage Dataverse extensibility capabilities such as Plugins, WebHooks, or Custom APIs, or if you want to more easily tie into events as Triggers and Actions in power automate... then SQL would put you in the position of needing to build those capabilities custom to the app.

When that's the choice you're looking at, the tedious nature of schema building becomes quite trivial.