Storing Dynamic Form data in SQL and retrieving as a View or possibly as a stored procedure

Question

We are reengineering our Delphi 7 Professional based product in ASP.NET Core using Blazor. We are developing a dynamic form that allows users to add new worksheet types into the system, and then consume those worksheet types. Our DynForm as we coined it.

I am writing the backend side of the DynForm assembly in Azure SQL. Previously, when a new field was added to the dynamic form (Delphi app), we just append a new column to the forms SQL table. That grew way out of control, so I am instead using the following data structure (I do not want to use JSON to store the data) as this data is needed for enterprise reporting from many directions.

THIS IS STILL IN THEORY, so WHEN I SAY I HAVE, I have in my design...

I have the following tables;

1. DynData_Values_Int
2. DynData_Values_Money
3. DynData_Values_DateTime
4. etc....

I then have a stored procedure that first goes through the DynForm setup tables to build the result set columns, and then It will lookup each columns value in the appropriate DynData_Values table.

I expect that there are a million ways to do this, does anyone have experience doing it themselves or any great ideas here? Code examples are wonderful if you have one.

I truly appreciate anyone taking a moment to responding to this old code warrior. Some of us were happy in DOS, what happened :) !

Answer 1

I suspect the approach of using an nvarchar column to hold the data regardless of the source type is ultimately going to be the right one unless there's some reason you absolutely have to have them stored by data type. There's another question with a similar premise that might help you as there is a decent amount of example code: SQL Server : Pivot with custom column names

I've done something like this in the past, but I used a single table with one column per datatype. Unfortunately I don't have the code anymore. The row in the control table had the information about what data type the response was so I knew which column in the responses table to use. It complicates the SQL, but it is an approach that sits between one table per type and one column with everything. If I were to do it again I'd put each form submission in a NVarChar column in JSON format, but I know that isn't the direction you want to go.

Feel free to skip the rest if you are dead set against JSON ;)

That being said, JSON is going to be much, much simpler. I'm not sure if you are thinking about putting an entire form submission in the NVarChar(Max) column or just a single entry. If you are putting the whole submission in there, why not use JSON formatting? Azure SQL has good JSON support, so you can let it do the lifting to get it into and out of the JSON format instead of relying your reporting apps if they aren't able to consume the JSON formatted data.

You can index on JSON properties to help speed up the queries where appropriate. If your data is expected to get large enough, enabling columnstore indexes may help with data compression and performance.