意外と難しいアンケートのテーブル設計 #MySQL

はじめに

アンケートは、ユーザーとの接点を得る上で重要な機能です。たとえば、ECサイトでは、商品に関する個別のアンケートを作成し、ユーザーに回答を求めることがあります。
そこで問題となるのが、アンケート機能のテーブル設計です。この設計は結構難しいもので、特に以下の点が課題となります

各アンケートごとに設問が異なる
設問の種類が多様で、チェックボックス、セレクトボックス、テキスト欄などがある
チェックボックスを使用する際には、複数の回答を保存する必要がある

これらの要件を満たしつつ、シンプルで拡張性のあるテーブル設計を目指します。

備考

本記事のテーブル設計はあくまでも一例です。
「こうした方がいいよ！」」「自分だったらここを修正するかな」などご意見がありましたらぜひコメントをいただけると嬉しいです！

テーブル設計

高い拡張性が必要な機能に対するアプローチとして、クラステーブル継承という考え方があります。
クラステーブル継承はテーブルをオブジェクト指向のクラスであるかのようにみなし、継承を模倣するという方法です。

まず、すべてのサブタイプに共通する属性を含む基底型のテーブルを1つ作ります。設問（Questions）と回答（Answeｒs）テーブルが基底型に該当します。
次にサブタイプごとに１つずつ追加のテーブルを作成し、基底型テーブルに対する外部キーの役割を持つ主キーを設定します。

アンケートと設問が紐付き１対多の関係になります。
テキスト設問、セレクトボックス設問、チェックボックス設問はそれぞれ設問IDを外部キーに持ちます。外部キー制約があることにより、基底テーブルである設問テーブルを必ず参照します（クラス継承のように）。

回答テーブルも同様に、テキスト回答、セレクトボックス回答、チェックボックス回答はそれぞれ回答IDを外部キーに持ちます。

サブタイプの設問テーブルと回答テーブルはそれぞれ対応しており、テキスト設問/テキスト回答を例にすると、ぞれぞれのテーブル構造は次のようになります。

QuestionText (テキスト設問)

Column Name	Data Type	Constraints	Description
text_id	INT	PRIMARY KEY, AUTO_INCREMENT	テキスト設問の主キー
question_id	INT	FOREIGN KEY	設問ID

AnswerText (テキスト回答)

Column Name	Data Type	Constraints	Description
answer_id	INT	PRIMARY KEY, FOREIGN KEY	回答ID
text_id	INT	PRIMARY KEY, FOREIGN KEY	対応するテキスト設問ID
text_answer	TEXT		ユーザーが入力したテキスト回答

ER図

ER図はこのようになります。全部で９テーブルです。

クエリ

アンケート結果を取得するクエリを考えます。

SELECT 
    s.survey_id,
    s.title AS survey_title,
    q.question_id,
    q.question_text,
    GROUP_CONCAT(at.text_answer) as 'テキスト',
    GROUP_CONCAT( asb.selectbox_id) as 'セレクトボックス',
    GROUP_CONCAT( acb.checkbox_id) as 'チェックボックス'
FROM 
    Surveys s
INNER JOIN 
    Questions q ON s.survey_id = q.survey_id
LEFT JOIN 
    Answes a ON s.survey_id = a.survey_id
LEFT JOIN 
    QuestionText qt ON q.question_id = qt.question_id
LEFT JOIN 
    AnswerText at ON qt.text_id = at.text_id AND a.answer_id = at.answer_id
LEFT JOIN 
    QuestionSelectbox qsb ON q.question_id = qsb.question_id
LEFT JOIN 
    AnswerSelectbox asb ON qsb.selectbox_id = asb.selectbox_id AND a.answer_id = asb.answer_id
LEFT JOIN 
    QuestionCheckbox qcb ON q.question_id = qcb.question_id
LEFT JOIN 
    AnswerCheckbox acb ON qcb.checkbox_id = acb.checkbox_id AND a.answer_id = acb.answer_id
GROUP BY q.question_id ;

かなり長いクエリですが、中身自体はそこまで難しくありません。アンケートテーブルと設問テーブルを内部結合した後、他のテーブルは全て外部結合しています。

また、GROUP BYを用いて設問IDでグループ化し、アンケート回答の値をまとめています。これはチェックボックスが複数選択されたとき、レコードを１行にまとめるためです。

結果はこんな感じです。

終わりに

テーブル数がかなり多いですが、外部キーでガチガチに制約しているので、整合性は担保できるテーブル設計かと思います。

一例として参考になれば幸いです。