业务梳理

逻辑建模是数据库设计的重要步骤，它帮助我们确定系统中的实体及其关系。在问卷调查系统的上下文中，我们需要识别核心实体，并为这些实体定义属性和关系。以下是一个简化的逻辑模型示例，以及基于此模型的表设计方案。

逻辑建模

  1. 用户(User): 系统的使用者。

    • 用户ID (UserID)
    • 用户名 (Username)
    • 密码 (Password)
    • 邮箱 (Email)

  2. 问卷(Survey): 用户创建的问卷。

    • 问卷ID (SurveyID)
    • 创建者ID (CreatorID) -> 关联User表
    • 标题 (Title)
    • 描述 (Description)
    • 创建时间 (CreatedAt)
    • 更新时间 (UpdatedAt)

  3. 问题(Question): 每个问卷包含多个问题。

    • 问题ID (QuestionID)
    • 问卷ID (SurveyID) -> 关联Survey表
    • 题目 (Text)
    • 类型 (Type, 如单选、多选、文本输入等)

  4. 选项(Option): 对于选择题，每个问题有多个选项。

    • 选项ID (OptionID)
    • 问题ID (QuestionID) -> 关联Question表
    • 文本 (Text)

  5. 回答(Response): 记录用户的回答信息。

    • 回答ID (ResponseID)
    • 问卷ID (SurveyID) -> 关联Survey表
    • 用户ID (UserID) -> 关联User表（可选，匿名问卷则可能不需要）
    • 提交时间 (SubmittedAt)

  6. 答案(Answer): 每个回答对应多个答案。

    • 答案ID (AnswerID)
    • 回答ID (ResponseID) -> 关联Response表
    • 问题ID (QuestionID) -> 关联Question表
    • 选项ID (OptionID) -> 关联Option表（对于非选择题类型，这个字段可能为空）
    • 文本 (Text) -> 对于文本输入类型的问题使用

表设计

根据上述逻辑模型，可以设计如下数据库表：

  • Users

    UserID | Username | Password | Email

  • Surveys

    SurveyID | CreatorID | Title | Description | CreatedAt | UpdatedAt

  • Questions

    QuestionID | SurveyID | Text | Type

  • Options

    OptionID | QuestionID | Text

  • Responses

    ResponseID | SurveyID | UserID | SubmittedAt

  • Answers

    AnswerID | ResponseID | QuestionID | OptionID | Text

这样的设计能够有效地组织问卷调查系统所需的数据，并且通过外键约束确保数据的一致性和完整性。例如，CreatorID 在 Surveys 表中引用 Users 表的 UserID，确保每个问卷都有一个有效的创建者；同样地，SurveyID 在 Questions 和 Responses 表中分别引用 Surveys 表的 SurveyID，保证了问题和回答都属于某个特定的问卷。

实际建模

好的！下面是根据前面的逻辑建模，使用 MySQL 的语法格式设计的数据库表结构。我们采用规范化的数据库设计，使用 InnoDB 引擎，并为常用查询字段添加索引。

✅ 用户表：users

CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,  -- 推荐使用加密存储（如bcrypt）
    email VARCHAR(100) UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

✅ 问卷表：surveys

CREATE TABLE surveys (
    survey_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    creator_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    is_published TINYINT(1) DEFAULT 0,  -- 是否发布
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (creator_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_creator(creator_id)
) ENGINE=InnoDB;

✅ 问题表：questions

CREATE TABLE questions (
    question_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    survey_id BIGINT NOT NULL,
    question_text TEXT NOT NULL,
    question_type ENUM('single_choice', 'multiple_choice', 'text', 'rating') NOT NULL,
    order_index INT DEFAULT 0,  -- 用于排序问题顺序
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    INDEX idx_survey(survey_id)
) ENGINE=InnoDB;

✅ 选项表：options

CREATE TABLE options (
    option_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    question_id BIGINT NOT NULL,
    option_text VARCHAR(500) NOT NULL,
    order_index INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES questions(question_id) ON DELETE CASCADE,
    INDEX idx_question(question_id)
) ENGINE=InnoDB;

✅ 回答记录表：responses

CREATE TABLE responses (
    response_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    survey_id BIGINT NOT NULL,
    user_id BIGINT,  -- 可为空（支持匿名填写）
    ip_address VARCHAR(45),  -- 记录提交IP防止刷票
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_survey_user(survey_id, user_id),
    INDEX idx_submitted(submitted_at)
) ENGINE=InnoDB;

✅ 答案明细表：answers

CREATE TABLE answers (
    answer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    response_id BIGINT NOT NULL,
    question_id BIGINT NOT NULL,
    option_id BIGINT,  -- 可为空（如文本题）
    answer_text TEXT,  -- 如为文本题则保存在此
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (response_id) REFERENCES responses(response_id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES questions(question_id) ON DELETE CASCADE,
    FOREIGN KEY (option_id) REFERENCES options(option_id) ON DELETE SET NULL,
    INDEX idx_response(response_id),
    INDEX idx_question(question_id)
) ENGINE=InnoDB;

📌 补充建议

1. 索引优化

  • 对于经常用于查询的字段组合（如 survey_id + user_id）建立联合索引。
  • 在统计分析时频繁使用的字段（如 submitted_at）也应加索引。

2. 数据安全

  • 用户密码必须使用加密算法（如 bcrypt、argon2）处理后存储。
  • 对敏感字段（如邮箱、IP地址）进行脱敏或加密处理（视合规要求而定）。

3. 扩展性考虑

  • 如果需要支持多语言问卷，可增加 translations 表。
  • 若需版本控制，可在 surveys, questions 表中增加 version 字段。
  • 若并发量大，可考虑分库分表或引入缓存机制（如 Redis 缓存问卷结构）。