まず初心者が行ってしまうのは、１レコード(行)に全ての情報をつめこんでしまう設計。
後で一切いじることのない完結した物なら問題ありませんが、大抵は追加追加でどんどん膨れ上がってしまいます。
また、一行に詰め込むにはあまりにも無駄に膨大なカラム数になる場合もあります。
あまり増えると、１行のデータ上限にひっかかり、エラー発生という落とし穴もあります。
TEXT等の長いカラムは、１レコードあたり10カラムもあるといつエラーになってもおかしくありません。
一番やってほしくないカラム構成は
id, data1, data2, data3......
同じ意味のカラムがぽんぽんぽんと、何も考えないで作った感丸出しですね。
こういう場合は、重複しているカラムごとに１レコードにします。
id, key, data
１行ですんでいたのが複数行になり、行が大量になってしまうではないかと思うかもしれませんが、いいんです。
スピードもデータ容量も誤差の範囲です。 扱い方が変わるだけです。
キーバリュー(key-value)方式みたいな単純な構造にすることで、拡張性が飛躍的にあがります。
今までカラムを増やすたびにDB操作だったのが、その必要がなくなるり、動的にカラムを扱えるようになります。
扱い方次第では、行ロックである InnoDB の恩恵も強くうけられるようになります。
次に、どうしても１レコードに収めたい。しかしカラムは今後増えて行く可能性がるから柔軟にしたい。
これを解決できるのは、増えて行くカラムは検索対象にならないというのが大前提です。
id,datadataの値は配列をシリアライズして圧縮をかけた物にします。
配列なので、自由自在にデータ項目を増やせます。
検索できないので出番は少ないですが、大きなシステムでは使うことがあります。
以上の３パーンが基本となり、それぞれのデータをどの方式で格納するかを決めます。
迷ったら２番目が後々楽ですが、最初のコーディングが少し多くて面倒かな。
実際は、マスターテーブルと関連付けて少し複雑になる場合が多いです。
具体例で言いますと、会員情報のテーブル構成では
member_id, pwこのように２つのテーブルにわけます。１つ目はマスターテーブルと呼ばれる物ですね。
member_id, key, value
マスターテーブルでは、ログイン時に必要な会員IDとパスワードのみを保存します。
会員情報本体のカラム構成が変化しないとわかっていれば、１行にまとめても良いです。
もちろんこれは絶対的な設計ではなく、要件によって使い分けてください。
