Database Table

When managing forms in the FormCreate environment, a reasonable database design is crucial for storing form design rules and user-submitted data. The following content details several core database table designs to help you organize and manage data more effectively.

1. Forms Table (forms)

This table stores overall information for each form, including name, description, and design rules:

Field Name	Data Type	Description
id	INT	Primary key ID, auto-increment
title	VARCHAR	Form name, used to identify the unique identifier of the form
description	TEXT	Form description information
rules	JSON	Form's rules and overall configuration data of fields, usually contains configuration of multiple fields
options	JSON	Form configuration data (e.g., layout, size, global data, etc.)
created_at	DATETIME	Form creation time
updated_at	DATETIME	Form update time

CREATE TABLE forms (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Primary key ID, auto-increment',
    title VARCHAR(64) NOT NULL UNIQUE COMMENT 'Form name, used to identify the unique identifier of the form',
    description TEXT COMMENT 'Form description information',
    rules JSON COMMENT 'Form's rules and overall configuration data of fields, usually contains configuration of multiple fields',
    options JSON COMMENT 'Form configuration data (e.g., layout, size, global data, etc.)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Form creation time',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Form update time'
) COMMENT='Form information table';

Example Code:

Insert data object for form component, containing key information such as title, description, form configuration, etc., used to serialize and store form design data to the backend database.

//rules is the component design rules of the form
//options is the form configuration
const insert = {
    title: options.formName || '',
    description: options.description || '',
    rules: JSON.stringify(rules),
    options: JSON.stringify(options),
}

2. Field Data Table (form_data)

This table stores specific values for each field submitted by users. Each form submission generates a new record for tracking form history.

Field Name	Data Type	Description
id	INT	Primary key ID, auto-increment
form_id	INT	Form ID, foreign key associated with forms table
field_id	INT	Field ID, points to fields table
field_name	VARCHAR	Field name, points to field in fields table
field_value	JSON	Field value submitted by user
created_at	DATETIME	Data creation time
updated_at	DATETIME	Form update time

CREATE TABLE form_data (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Primary key ID, auto-increment',
    form_id INT NOT NULL COMMENT 'Form ID',
    field_id INT NOT NULL COMMENT 'Field ID, points to fields table',
    field VARCHAR(32) NOT NULL COMMENT 'Field name, points to field in fields table',
    value JSON COMMENT 'Field value submitted by user',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Data creation time',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Form update time'
) COMMENT='User submitted form field data table';

Example Code:

Convert form submission data to a structure that can be inserted into the database. By iterating through each field of the form data, generate an array of objects containing field ID, field name, and serialized values, facilitating batch storage of form submission records.

//formData is the form submission data
const inserts = Object.keys(formData).map(field => {
    return {
        field_id: getFieldId(field), //Query corresponding field_id through field
        field: field,
        value: JSON.stringify(formData[field]),
    };
})

Through the above database table structure examples, you can build a good data storage foundation for FormCreate-based applications, facilitating storage and management of form designs and user data.