最適なDB Table作成

質問

現在以下のシステムを作成しているのですが、データベース設計に悩んでいます。

システムはユーザーが自由に作成＆カスタマイズできるアンケートです。

例えばデフォルトはアンケート項目が

名前　数字
年齢　数字
興味のある物　チェックボックス

とします。
でユーザーはこのアンケート項目を自由にカスタマイズできます。
デフォルト時のDBでは

userform
ユーザーID | 変更日 | 項目 | 項目名 | 条件 | 掲載日
userid | 200812 | name/age/interestings/ | 名前/年齢/興味のある物 | num/num/cb/ | allrequired | 200901

と成っています。　
ここでユーザーが名前と年齢の間にフリガナを追加した際、DBでは変更日と項目、項目名、条件が書き換えられます。

アンケートを表示すると、PHPで動作するスクリプトがユーザーのレコードを読み込み、各項目をHTMLに変換、表示します。

そしてデータを収集する方ですが、DB構成は以下になります。
ユーザID | データ入力日(int) | 結果(text) | IPアドレス

データが入力されると
datadb
userid | 20081209 | 佐藤/25/computer | 123.123.123

となり、データ集計プログラムをブラウザで表示すると、SELECT * FROM datadb WHERE userid = "userID"　と　SELECT * FROM userform WHERE userid = "userID"を発行し、PHPでデータをソートし表示します。

しかし以上の構成だと、途中でアンケートの項目を追加＆削除したり変更することはできず、使い勝手が悪くなります。

仮にuserformのカラムをユーザーアンケート項目の追加、削除のたびにAlterで追加、削除てしまう方法も頭に浮かびましたが、そうすると別のユーザーにもその追加したカラムを適用してしまうためNGです。

どのようにすればユーザーアビリティも保て、中規模なシステム利用でも耐えることが出来るできるDBを作成できるのでしょうか？
よろしくお願いいたします。

回答No.3

mitoneko · ベストアンサー

わたしなら、次のような構成を考えるでしょうか・・・
　・ユーザーテーブル
　　userid int
username char2
以下略

　・アンケート質問票
　　userid int
qno int
qcont char2
qstatus bool （必須か否か）

　・アンケート結果表
　　userid int
ansuserid int （回答者の個別ｉｄ・単なる通し番号でｏｋか？）
qno int
　　ans char2

　と３つにわけます。
　アンケート結果表のansuseridは、回答者ごとに通し番号を打っておけばｏｋかと。
　ユーザー表の主キーは、useridで、アンケート質問票の主キーは、（userid,qno)　、アンケート結果表の主キーは、(userid, ansuserid,qno)となります。アンケート質問票の外部キーとして、ユーザー表.useridを、アンケート結果表の外部キーとして、(アンケート質問票.userid, アンケート質問票.qno）を指定することになると思います。

　あるユーザーのアンケートを表示するには、

　select a.userid, b.qno, b.qcont, b.qstatus from ユーザー票 a, アンケート質問票 b where a.userid=b.userid, a.userid=パラメータ

　となりますし、アンケートの結果を表示するには、

　select a.userid, b.qno, c.ansuserid, c.ans from ユーザー表 a, アンケート質問票 b, アンケート結果表 c
　where a.userid=b.userid and b.userid=c.userid and b.qno=c.qno and a.userid=パラメータ
　order by a.userid, b.qno

　くらいとなるでしょうか。
　ユーザーの属性とアンケートの属性、それに結果表の属性をすべて分離し、アンケートの質問は各設問に対して一つのレコードを割り当てているのがポイントです。
　利点は、どんな時点であれ、設問数・内容を各ユーザーが自由に加除できるところでしょうか。（ただし、すでに回答が入っている設問を操作（削除・更新）すれば設問と回答が整合しなくなりますから、そのときには古い回答は削除する処理が別途必要でしょう。でも設問の追加はこのような考慮は不要です。qnoを新たに発行すればすみますから。）

　用途には合いますでしょうか？

回答No.4

kuroizell

LimeSurvey、phpESPなどの既存のOSSアンケートシステムを使うとか。
テーブル構造の参考にもなると思います。

回答No.2

hrm_mmm

正規化を行いましょう。
参考
http://www.ddsystems.info/ex/ex_db/400101.html
最低でも、user 情報table と、項目情報table(アンケート項目ごとに１レコード登録) と、userid に対して項目idを登録するためのtable の３つ必要でしょう。
あとは、適宜join すれば、select文で必要なデータの取り出しは可能です。

回答No.1

denbee

思い付きですが、ユーザがカスタマイズしたい質問項目は
別テーブル（例えばoptiontable)に生成し、
userformには、このテーブルのID項目（例えばoption_id)を追加し、
そちらで連携を取るようにするのはどうでしょうか？

PHPのスクリプトはoption_idを確認し、そちらに項目があれば
その項目をHTMLに追加します。これなら、ユーザごとに
個別の質問項目が作れると思いますが。

補足

ご回答ありがとうございます。

なるほど、別テーブル策ですね。

一応最大項目数は制限するつもりなので、別テーブルに格納するのもいいかもしれません。そうすると以下のテーブルが思いつくのですが、どうでしょう？

Koumoku
userid | 項目1の名前 | 項目１の形式 | 項目１の条件 | 項目２.....

userid | 名前 | Number | required | 出身 | CheckBox | notRequired ...

よろしくお願いいたします。