固定问题问卷的mysql设计方案

Question

我正在为有固定问题的问卷设计方案。

有几个独立的调查问卷(例如家庭/朋友/学校等)。

调查问卷不需要注册，所以我们不需要处理用户关系。

调查问卷如下所示：

​

然而，不同问卷的选项是不同的。

​

我在考虑只使用一个表Survey来存储所有数据，比如使用数组存储调查问卷的答案，因为调查问卷是不会改变的。

{
   surveyId: 123 (which is the primary key),
   type: "School",   // values: Family/School etc
   answer: [0,1,0,1,2....]
}

此外，还需要根据问卷结果进行一些分析。

1. Count scores for a specific questionnaire
2. Count the percentage of answer selection for a certain question

对模式有什么建议吗？

Answer 1

虽然我不能说这是最终的解决方案，但我希望看到以下内容：

CREATE TABLE questionaire_types
(questionaire_type_id SERIAL PRIMARY
,questionaire_type_name VARCHAR(30) NOT NULL UNIQUE
);

INSERT INTO questionaire_types VALUES
(1,'fortnight frequency'),
(2,'agreement');


CREATE TABLE questionaire_type_detail
(questionaire_type_id SERIAL PRIMARY KEY
,option_id INT NOT NULL UNIQUE
,option_value VARCHAR(30) NOT NULL 
);

INSERT INTO questionaire_types VALUES
(1,1,'not at all'),
(1,2,'occasionally'),
(1,3,'often'),
(1,4,'nearly every day'),

(2,1,'strongly agree'),
(2,2,'agree'),
(2,3,'neutral'),
(2,4,'disagree'),
(2,5,'strongly disagree');

CREATE TABLE questionaires
(questionaire_id SERIAL PRIMARY KEY
,questionaire_type_id
,question VARCHAR(50) NULL
);

INSERT INTO questionaires VALUES
(1,1,1,"Over the last 2 weeks, how often have been bothered by any of the following problems?"),
(2,2,NULL);

CREATE TABLE questionaire_subquestions
(subquestion_id SERIAL PRIMARY KEY
,questionaire_id INT NOT NULL
,subquestion VARCHAR(50) NOT NULL
);

INSERT INTO questionaire_subquestions VALUES
(1,1,'Little interest or pleasure in doing things'),
(2,1,'Feeling down, depressed, or hopeless'),
(3,1,'Trouble falling/staying asleep, sleeping too much'),
(4,1,'Feelng tired or having little energy'),
(5,1,'Poor appetite or overeating'),
(6,1,'Etc...'),
(...)
(10,2,'The store is accessibly located'),
(11,2,'Store hours are convenient for my dining needs'),
(12,2,'Advertised dish was in stock'),
(13,2,'A good selection of dishes was present'),
(14,2,'Etc...'),

CREATE TABLE responses
(response_id INT NOT NULL
,subquestion_id INT NOT NULL
,selected_option_id
,PRIMARY KEY(response_id,subquestion_id)
);