How to design a flexible MySQL table structure to implement the questionnaire survey function?

How to design a flexible MySQL table structure to implement the questionnaire survey function?

1. Introduction
Questionnaire surveys are widely used in data collection and research in modern society. In order to flexibly adapt to different questionnaire survey needs, we need to design a reasonable MySQL table structure. This article will introduce how to design a flexible MySQL table structure to implement the questionnaire function, and provide specific code examples.

2. Needs Analysis

1. The questionnaire includes multiple questions, and each question can have multiple options.
2. Users can choose to fill out the questionnaire and select options for each question.
3. The results of the questionnaire survey need to be saved in the database for subsequent statistics and analysis.

3. Table design
Based on the above requirements, we need to design the following tables:

1. Questionnaire table (surveys)
   Field: id (primary key ), title (questionnaire title), created_at (creation time)
2. Question table (questions)
   Fields: id (primary key), survey_id (questionnaire id, foreign key), content (question content), type (Question type, such as single choice, multiple choice, etc.)
3. Option table (options)
   Fields: id (primary key), question_id (question id, foreign key), content (option content)
4. Answers table (answers)
   Fields: id (primary key), survey_id (questionnaire id, foreign key), question_id (question id, foreign key), option_id (option id, foreign key), user_id (user id) , created_at (fill in time)

4. Specific implementation
The following will use MySQL statements to create the above table and provide some sample code.

-- Create questionnaire table
CREATE TABLE surveys (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

--Create question table
CREATE TABLE questions (
id INT PRIMARY KEY AUTO_INCREMENT,
survey_id INT NOT NULL,
content TEXT NOT NULL,
type TINYINT NOT NULL, -- 1 represents single selection, 2 represents multiple selection
FOREIGN KEY (survey_id) REFERENCES surveys(id)
);

-- Create option table
CREATE TABLE options (
id INT PRIMARY KEY AUTO_INCREMENT,
question_id INT NOT NULL,
content TEXT NOT NULL,
FOREIGN KEY (question_id) REFERENCES questions(id)
);

- - Create answer table
CREATE TABLE answers (
id INT PRIMARY KEY AUTO_INCREMENT,
survey_id INT NOT NULL,
question_id INT NOT NULL,
option_id INT NOT NULL,
user_id INT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (survey_id) REFERENCES surveys(id),
FOREIGN KEY (question_id) REFERENCES questions(id),
FOREIGN KEY (option_id) REFERENCES options (id)
);

For the specific implementation of the questionnaire, we can use PHP or other programming languages ​​​​to handle page display and data processing. The following is a simple sample code:

1. Create a survey page and submit the user's answers through a form.

<input type="hidden" name="survey_id" value="1">
<?php
    // 获取问题列表
    $questions = // 从数据库中查询问题列表的代码

    foreach ($questions as $question) {
        echo '<p>' . $question['content'] . '</p>';
        
        // 获取选项列表
        $options = // 从数据库中查询选项列表的代码

        foreach ($options as $option) {
            echo '<input type="checkbox" name="question_' . $question['id'] . '[]" value="' . $option['id'] . '"> ' . $option['content'] . '<br>';
        }
    }
?>
<input type="submit" value="提交">

2. Create a user PHP file (submit.php) used to process form submissions.

// 获取用户提交的答案
$surveyId = $_POST['survey_id'];
$answers = $_POST; // 获取所有POST参数

foreach ($answers as $questionId => $optionIds) {
    if (strpos($questionId, 'question_') !== false) {
        $questionId = str_replace('question_', '', $questionId);
        
        // 保存答案到数据库
        foreach ($optionIds as $optionId) {
            // 将答案插入到answers表中的代码
        }
    }
}

?>

5. Summary
By rationally designing the MySQL table structure, we can flexibly implement the questionnaire survey function , and facilitate subsequent statistics and analysis. During the specific implementation process, attention needs to be paid to the correlation and logical processing of data tables to ensure the integrity and accuracy of the data.

The above is the detailed content of How to design a flexible MySQL table structure to implement the questionnaire survey function?. For more information, please follow other related articles on the PHP Chinese website!