下面是一个基本的调查问卷微信小程序数据库设计，包含6张表：用户信息表、问卷信息表、问题信息表、选项信息表、回答信息表和统计信息表。
1. 用户信息表
```sql
CREATE TABLE user_info (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(20)
);
```
2. 问卷信息表
```sql
CREATE TABLE questionnaire_info (
questionnaire_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
description TEXT,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
end_time TIMESTAMP,
user_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE
);
```
3. 问题信息表
```sql
CREATE TABLE question_info (
question_id INT PRIMARY KEY AUTO_INCREMENT,
type VARCHAR(10) NOT NULL,
content TEXT NOT NULL,
questionnaire_id INT NOT NULL,
FOREIGN KEY (questionnaire_id) REFERENCES questionnaire_info(questionnaire_id) ON DELETE CASCADE
);
```
4. 选项信息表
```sql
CREATE TABLE option_info (
option_id INT PRIMARY KEY AUTO_INCREMENT,
content TEXT NOT NULL,
score INT,
question_id INT NOT NULL,
FOREIGN KEY (question_id) REFERENCES question_info(question_id) ON DELETE CASCADE
);
```
5. 回答信息表
```sql
CREATE TABLE answer_info (
answer_id INT PRIMARY KEY AUTO_INCREMENT,
content TEXT NOT NULL,
question_id INT NOT NULL,
questionnaire_id INT NOT NULL,
user_id INT NOT NULL,
FOREIGN KEY (question_id) REFERENCES question_info(question_id) ON DELETE CASCADE,
FOREIGN KEY (questionnaire_id) REFERENCES questionnaire_info(questionnaire_id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE
);
```
6. 统计信息表
```sql
CREATE TABLE statistic_info (
statistic_id INT PRIMARY KEY AUTO_INCREMENT,
question_id INT NOT NULL,
questionnaire_id INT NOT NULL,
total_answer INT NOT NULL,
average_score FLOAT,
FOREIGN KEY (question_id) REFERENCES question_info(question_id) ON DELETE CASCADE,
FOREIGN KEY (questionnaire_id) REFERENCES questionnaire_info(questionnaire_id) ON DELETE CASCADE
);
```
注意：上述建表语句是基本的示例，具体实现还需要根据具体业务需求进行调整。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
6237
