Database Table

Proper database design is crucial for storing form design rules and user-submitted data in FormCreate. This guide covers core database table designs to help you organize and manage data effectively.

1. Form 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 uniquely identify the form
description	TEXT	Form description information
rules	JSON	Form rules and overall configuration data for fields, usually containing configurations for 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 uniquely identify the form',
    description TEXT COMMENT 'Form description information',
    rules JSON COMMENT 'Form rules and overall configuration data for fields, usually containing configurations for 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 the form component, containing title, description, and form configuration for serializing and storing form design data in the backend database:

// rules is the component design rules for 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 the specific values submitted by users for each field. Each form submission creates 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 referencing the forms table
field_name	VARCHAR	Field name
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 VARCHAR(32) NOT NULL COMMENT 'Field name, references the field in the 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='Form field data table for user submissions';

Example Code:

Convert form submission data into a structure for database insertion. Iterate through each field to generate an array of objects containing field ID, field name, and serialized values for batch storage:

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

These database table structures provide a solid foundation for FormCreate-based applications, making it easy to store and manage form designs and user data.