How should the database be designed for dynamic forms?

Question

The demand is like this

1. Enter the form information in the background management interface and specify which forms there are (can be added or deleted)
   

2. Show which forms can be filled in on the front end

3. Finally, store the filled in form (real data generated) into the database

4. The last data filled in needs to be convenient for querying, sorting, and filtering data

I found a lot of information on the Internet but none of it met my needs.
Is there any design master who can provide some design ideas? ! !

Answer 1

1. First define a control table, such as single line text, multi-line text, radio selection, multiple selection, time, etc.
2. Then define a configuration table to define which fields a Form has and the field names (use to display, such as name, gender), control type, field default value, field length, whether it is required, etc.
3. Then save the records added to the corresponding configuration table to another record table in the background, and the records stored in the record table There may be multiple single lines of text, multiple radio selections, and other records (redundant)
4. Then display the Form according to the information in the record table,
5. When you finally fill in the Form and submit it, just save the filled in information.
6. When it is necessary to display the filled-in information, display the corresponding information in the corresponding control according to the configuration table and the corresponding record of the Form

---

Supplement

Answer 2

Add a few words about query. The basic idea is the same as the first floor. But data filtering and querying is indeed a problem. In this case, you can consider adding a table specifically responsible for retrieval

id primary key auto-increment search_id is the data id filed_id is the field id data is the data value

When the front end has finished adding this data, execute the callback and insert the relevant data into this table. Just be responsible for the search. If this table needs to be indexed, just add an index.
It should be enough

Answer 3

It is best to store data in mongo. If you store a relational database, it will be a big pit. In addition, you must store a copy of the summary data, and then store a separate copy of the form data. For example, if I create 10 new forms, then store 10 collections.

Answer 4

Supports mongodb storage. It is quite pleasant to use mongodb to store items of various colors and sizes, such as product attributes.
In addition, not all query logic must be written on the SQL side. If some logic is extracted and written at the code layer, you will find performance improvements. Quite a bit, a lot of join operations can be eliminated.