I am curious to hear about other's experience...

[Christian_Farle]

I am curious to hear about other’s experience migrating their data source to an SQL DB. As I am currently trying that (for performance gain reasons), I am wondering if there are best practices specific to Appsheet. For instance, since by default Appsheet handles the relational referencing between tables, does this mean I only use the SQL DB as a set of flat tables? Or are there any benefits in redesigning the DB structure with relational views?

[Harry2]

@Christian_Farley2 Hi Christian, when you clone an existing spreadsheet app to a SQL database, AppSheet will do three things:

1/ Infer the SQL data type for each column based on the AppSheet column type.

2/ Create a new SQL table for each table in the app using the inferred data types of the columns.

3/ Copy the app’s data from the spreadsheet to the newly created SQL tables.

Since there is no one-to-one match between SQL types and AppSheet types, the type-inference done by AppSheet should only be treated as a best guess. This means that you should double-check the column types in the database and modify them if necessary. This is usually done if you want to fine tune the data types in the column to reduce storage space or to improve the precision of certain special column types, such as floating point or decimal columns.

Also, AppSheet does not create SQL foreign key constraints. As such, you might want to add foreign key constraints between the newly created SQL tables based on the table references in the app. Roughly speaking, SQL foreign key constraints are analogous to table references in AppSheet. However, table references are not strictly enforced, while foreign key constraints are. This is why AppSheet does not automatically create foreign key constraints when cloning apps to SQL databases: if the app contains inconsistent data that violates foreign key constraints, the clone operation will fail. If you decide to add foreign key constraints yourself, this will help ensure that data in your SQL tables is consistent.

Finally, you can always create your own triggers, stored procedures, or scripts in the database for more advanced use cases.