C# ASP.NET Core building dynamic forms

Question

I want to implement something similar like SharePoint online does and that is the list functionality. I want to create dynamic lists add new columns, etc. The user can fill in the form and save the data.

The bottle neck is the database, I have some hard time understand how this should be built.

I was thinking to create dynamic SQL Server tables, columns, etc, but maybe this is not the good approach. Can somebody advise on how the database should look like?

I was thinking about creating one table called Lists and there store List properties. Then a many to many relation to another table called Fields where I can store all fields related to a specific form. Many to many relation because some fields are common for all forms like: Id, CreatedBy, Created, Modified, ModifiedBy, etc. Now comes the difficult part. Where should I store the actual data? Should I create another table called FormData and store in there the data? And what relation should it have with Lists and Fields tables?

Answer 1

NoSql approach seems to be a simple one. If you think about databases only as IO devices, which shouldn't contain any "business" logic, the fact that memory cost and memory reading/writng time(SSD) become lower - you will have little bid more options for your issue.

For example you can save field's collection in one object

{
    "key": "form1",
    "fields" : [ "Id", "CreatedAt" ]
}

Based on that object you can generate view with list or form of created fields. You can introduce own "object" for field with more data you need for view generation and for data processing.

{
    "key": "form1",
    "fields" : [ 
        { "Name": "Id", "Type": "string" }, 
        { "Name" "CreatedAt", "Type": "DateTime" } 
    ]
}

The actual data can be saved in one object too

{
    "formKey": "form1",
    "Name": "FirstName LastName",
    "CreatedAt": "2017-04-23T18:25:43.511Z"
}

On client side this data can be easily saved as json object and sended to the server.
On server side you can deserialize that object as Dictionary<string, object> and handle it in dynamic way

var formEntity = JsonConvert.DeserializeObject<Dictionary<string, object>>(requestBody);

var name = formEntity["Name"].ToString();

If you tightly attached to the relational database, you can save "raw json" in NVARCHAR column and one identity column.
For processing data you will be able to deserialize it to the Dictionary<string, object>.
With field object { "Name" "CreatedAt", "Type": "DateTime" } you can convert values to the expected types for correct validation and processing.

You will be able to search for data based on dynamic fields ro create dynamic reports, where user can create his own reports.

Because structure of fields not dynamic you can save forms and fields structure in relational way. Below is my suggestion for sql server database.

CREATE TABLE Forms (
    Id INT IDENTITY(1,1) NOT NULL
)

CREATE TABLE Field(
    Id INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(30) NOT NULL,
    TypeName NVARCHAR(30) NOT NULL, -- Or INT -> can represent SqlDbType enum in c#
)

-- many to many relation of Form and Fields
CREATE TABLE FormFields (
    FormId INT NOT NULL,
    FieldId INT NOT NULL,
    PRIMARY KEY (FormId, FieldId)
)

-- because data is "dynamic" it should be saved  as string (json format)
CREATE TABLE FormData(
    Id INT IDENTITY(1,1) NOT NULL,
    FormId INT NOT NULL,
    Data NVARCHAR(MAX) NOT NULL, -- json format
)

And consider to use Microsoft version of NoSql - DocumentDB

Answer 2

I would structure it like the following pseudo-SQL (table names in bold):

Lists:

Id int
Name nvarchar(255)  -- list name
CreatedBy, Created, Modified, ModifiedBy (audit for the List creation/changes)

ListFields:

Id int
ListId int  -- foreign key to associated list
Name nvarchar(255)  -- column name
DataType nvarchar(3) -- abbreviation of data type - string, integer, float, bool etc.
Sort int -- column order
CreatedBy, Created, Modified, ModifiedBy (audit for List Field creation/changes)

That defines the lists themselves.

Now when the user enters data...

ListEntries: (groups the data entered - would be a record per row if these are columns in a table, or a single record for data entered into a form)

Id int
ListId int  -- foreign key to associated list
CreatedBy, Created, Modified, ModifiedBy (audit for Data creation/changes)

ListValues: (actual data entered into each field)

Id int
ListEntryId int  -- foreign key to the associated group of values
ListFieldId int  -- foreign key to associated field
Value nvarchar(max)  -- data (stored as text regardless of whether text or not - it could be int, double etc.)

Personally I wouldn't try to recycle common fields. Having the audit fields on each table gives a fine grain to change tracking (if needed) and is easy to follow when looking at the records. I left auditing off the individual values because often it is sufficient to track changes as a collection (via ListEntries). Of course, auditing could exist here too if desired.