基于用户属性权限控制的调查系统数据库设计咨询

项目背景 #

我负责的项目是一套类调查系统，包含CMS后台供管理员创建问题，用户登录后可回答问题。核心需求是管理员创建Survey（调查）时，需指定可见用户范围（例如仅15-21岁男性可见），CMS操作流程为：创建问题→创建Survey→关联Survey与问题→设置用户属性匹配条件。

现有数据库表结构 #

Question Types（问题类型表） #

• id
• type（如单选、多选、文本输入等）

Questions（问题表） #

• id
• question（问题内容）
• question_type_id（关联Question Types表）

Question User（用户回答表） #

• question_id（关联Questions表）
• user_id（关联用户表，需补充用户表存储年龄、性别等属性）
• value（用户提交的回答内容）

Survey Question（存疑表） #

• question_id
• survey_id
• value（不确定是否设为JSON字段或采用其他方案）

Survey Question表优化方案 #

首先明确：Survey Question表的核心作用是关联Survey和Question，同时存储该问题在当前Survey中的专属配置（而非用户回答或Survey受众条件），以下是具体优化方向：

方案1：拆分配置字段，按需使用JSON（推荐） #

如果原表的value是用来存储问题在Survey中的配置项（如是否必填、显示顺序、自定义选项等），建议拆分出明确字段，仅在需要灵活配置时用JSON：

CREATE TABLE survey_question (
    survey_id INT NOT NULL,
    question_id INT NOT NULL,
    display_order INT NOT NULL COMMENT '问题在Survey中的显示顺序',
    is_required BOOLEAN DEFAULT FALSE COMMENT '该问题在当前Survey中是否必填',
    custom_options JSON NULL COMMENT '仅针对当前Survey自定义选项时使用，比如单选/多选问题的选项调整',
    PRIMARY KEY (survey_id, question_id),
    FOREIGN KEY (survey_id) REFERENCES survey(id),
    FOREIGN KEY (question_id) REFERENCES questions(id)
);

这种设计兼顾了关系型数据库的可查询性和特殊场景下的灵活性。

方案2：剥离Survey受众条件至单独表 #

注意：Survey的用户可见条件是针对整个调查的，不属于单个问题的配置，因此不应放在Survey Question表，建议新增Survey Audience（调查受众表）：

CREATE TABLE survey_audience (
    survey_id INT NOT NULL,
    attribute_type VARCHAR(50) NOT NULL COMMENT '用户属性类型，如age、gender',
    operator VARCHAR(10) NOT NULL COMMENT '比较操作符，如>=、<=、=',
    value VARCHAR(100) NOT NULL COMMENT '属性值，如15、21、male',
    PRIMARY KEY (survey_id, attribute_type, operator, value),
    FOREIGN KEY (survey_id) REFERENCES survey(id)
);

例如，要设置仅15-21岁男性可见的Survey，只需插入3条记录：

• survey_id=X, attribute_type='age', operator='>=', value='15'
• survey_id=X, attribute_type='age', operator='<=', value='21'
• survey_id=X, attribute_type='gender', operator='=', value='male'
  查询时可通过该表快速筛选出符合用户属性的Survey。

关键澄清 #

• Survey Question表不要承担存储用户回答的职责：用户回答已由Question User表存储，若要查询用户对某个Survey的所有回答，可通过user_id关联Question User表，再通过Question表关联到Survey Question表筛选出对应Survey的问题即可。
• 不要混淆Survey的受众条件和问题配置：受众条件属于Survey的全局设置，应单独存储。

内容的提问来源于stack exchange，提问作者marcmaalouly