はじめに
アンケートは、ユーザーとの接点を得る上で重要な機能です。たとえば、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でグループ化し、アンケート回答の値をまとめています。これはチェックボックスが複数選択されたとき、レコードを１行にまとめるためです。
結果はこんな感じです。
終わりに
テーブル数がかなり多いですが、外部キーでガチガチに制約しているので、整合性は担保できるテーブル設計かと思います。
一例として参考になれば幸いです。
参考