アンケート
アンケートを自由に作ることができ、回答を集計してみせる
モデル
よくあるケースをカバーするには以下のような、タイプに分類できる。
選択式と記述式が存在する。
選択式は複数回答できる。
選択式は「その他」などのラベルと詳細を記述させることができる。
設問は用意されたものを再利用できる。
ビューの種類までカスタマイズできる(選択肢をチェックボックス表示するか? プルダウン表示するか? など)場合は、表示型を定義し、設問と結びつけるとよい。
Stackoverflowのこのモデルをベースに考える。これは選択肢も再利用できる形で書いてあるが、業務上設問ごとに選択肢も作るのが多いパターンなので、その部分を変える。
https://gyazo.com/4da23c792c46e3ed42996d9c0d674cf7
実装
code:sql
SELECT KS.回答者ID, S.設問文, ANS.回答内容
FROM 回答 K
JOIN 回答者 KS ON KS.回答者ID = K.回答者ID
JOIN 設問 S ON K.設問ID = S.設問ID
JOIN (
SELECT 回答ID, 回答内容
FROM 記述式回答
UNION ALL
SELECT SK1.回答ID, array_to_string(ARRAY(SELECT unnest(array_agg(SS.選択肢本文))), ',') AS 回答内容
FROM 選択式回答 SK1
LEFT JOIN 回答選択 SL ON SK1.回答ID = SL.回答ID
LEFT JOIN 設問選択肢 SS ON SS.設問選択肢ID = SL.設問選択肢ID
GROUP BY SK1.回答ID
) ANS ON K.回答ID = ANS.回答ID
ORDER BY KS.回答者ID, K.設問ID
https://gyazo.com/637e4952bd4532971f0ee21305693fb5
code:sql
CREATE TABLE アンケート(
アンケートID BIGINT PRIMARY KEY,
開始予定日時 TIMESTAMP NOT NULL,
終了予定日時 TIMESTAMP NOT NULL
);
CREATE TABLE 設問(
設問ID BIGINT PRIMARY KEY,
設問文 VARCHAR(1000) NOT NULL
);
CREATE TABLE 選択式設問(
設問ID BIGINT PRIMARY KEY,
選択可能数 INTEGER NOT NULL
);
CREATE TABLE 設問選択肢(
設問選択肢ID BIGINT PRIMARY KEY,
設問ID BIGINT NOT NULL,
選択肢本文 VARCHAR(1000) NOT NULL,
自由記述可 BOOL NOT NULL DEFAULT FALSE
);
CREATE TABLE アンケート設問(
アンケートID BIGINT NOT NULL,
設問ID BIGINT NOT NULL,
回答必須 BOOL NOT NULL,
PRIMARY KEY (アンケートID, 設問ID)
);
CREATE TABLE 回答者(
回答者ID BIGINT PRIMARY KEY
);
CREATE TABLE 回答(
回答ID BIGINT PRIMARY KEY,
アンケートID BIGINT NOT NULL,
設問ID BIGINT NOT NULL,
回答者ID BIGINT NOT NULL
);
CREATE TABLE 記述式回答(
回答ID BIGINT PRIMARY KEY,
回答内容 VARCHAR(1000) NOT NULL
);
CREATE TABLE 選択式回答(
回答ID BIGINT PRIMARY KEY
);
CREATE TABLE 回答選択(
回答ID BIGINT NOT NULL,
設問選択肢ID BIGINT NOT NULL,
回答内容 VARCHAR(1000),
PRIMARY KEY(回答ID, 設問選択肢ID),
FOREIGN KEY(回答ID) REFERENCES 選択式回答(回答ID),
FOREIGN KEY(設問選択肢ID) REFERENCES 設問選択肢(設問選択肢ID)
);
INSERT INTO アンケート(アンケートID, 開始予定日時, 終了予定日時) VALUES
(1, '2019-07-01 00:00:00', '2019-07-08 00:00:00');
INSERT INTO 設問(設問ID, 設問文) VALUES
(1, 'この設計はいけてますか?'),
(2, 'どういうところがいけてますか?'),
(3, '他にも取り上げて欲しい設計がありますか?');
INSERT INTO 選択式設問(設問ID, 選択可能数) VALUES
(1, 1),
(3, 3);
INSERT INTO 設問選択肢(設問選択肢ID, 設問ID, 選択肢本文, 自由記述可) VALUES
(1, 3, '在庫', FALSE),
(2, 3, 'カート', FALSE),
(3, 3, 'お気に入り', FALSE),
(4, 3, 'リコメンド', FALSE),
(5, 3, 'その他', TRUE),
(6, 1, 'はい', FALSE),
(7, 1, 'いいえ', FALSE);
INSERT INTO アンケート設問(アンケートID, 設問ID, 回答必須) VALUES
(1, 1, TRUE),
(1, 2, FALSE),
(1, 3, TRUE);
INSERT INTO 回答者(回答者ID) VALUES (1), (2), (3);
INSERT INTO 回答(回答ID, アンケートID, 設問ID, 回答者ID) VALUES
(1,1,1,1),
(2,1,2,1),
(3,1,3,1),
(4,1,1,2),
(5,1,2,2),
(6,1,3,2),
(7,1,1,3),
(8,1,2,3),
(9,1,3,3);
INSERT INTO 記述式回答(回答ID, 回答内容) VALUES
(2, '具体的なデータがあってわかりやすい'),
(5, 'ER図がわかりやすい'),
(8, 'ここまで複雑なモデルが必要なの?');
INSERT INTO 選択式回答(回答ID) VALUES
(1), (3), (4), (6), (7), (9);
INSERT INTO 回答選択(回答ID, 設問選択肢ID, 回答内容) VALUES
(1, 6, NULL),
(3, 1, NULL),
(3, 2, NULL),
(4, 7, NULL),
(6, 4, NULL),
(7, 6, NULL),
(9, 5, '検索');