ざっくりSQL入門 #1：データベース設計＜前編＞
仕事で、データベースまわりの簡単な講座的なものを残す必要ができたのでまとめてみる。
データベースに全く触れたことがないという人より、
RDBMSの概念がなんとなくわかっている、レベルの人向けかと思います。
また、わたし自身もほとんど独学というか業務でやりながら覚えただけのため、
系統立ててまとめた講座ではなく
「こういう作り方をしてみてはどうですか」というスタンスです。
しっかり勉強したいという人はそれらしい文献を見てください。
今回はタイトルどおり、まずはデータベース設計について。
基本的なところはどんなRDBMSでも一緒だと思うけど、MySQLを前提にして書いています。
まずはざっくりとした仕様を把握して骨組みを作る
当然のことではありますが、まずはここから。
ここではネット通販サイト（のようなもの）を例に挙げていきます。
Amaz○nみたいなやつ。
設計に慣れてくると、仕様をきちんと読み込んでからカッチリ設計することもできてきますが、
まずは大まかな仕様を把握して重要そうなテーブルから考えていきます。
（細かいカラムなども全部揃える必要はなく、必要最低限でOKです）
例なので本当にざっくりですが、こういう感じの仕様だとします。
- 購入するためには会員登録が必須
- 商品は複数種類・複数個をまとめて購入することができる
この仕様から、どのようなテーブルが必要になるか、想像がつくでしょうか。
例えば、以下のような形ではいかがでしょう。
必ずしもこれが正解になるわけではありませんが、少なくとも挙がっている仕様は満たせると思います。
- 会員（会員ID、メールアドレス、パスワード、氏名、住所、電話番号、…）
- 商品（商品ID、商品名、価格、説明文、…）
- 注文（注文ID、会員ID、購入日時、決済方法、購入商品情報、…）
- 購入商品情報とは、例えば購入した商品IDや個数を配列にまとめてjson化したテキスト、などの形
細かいところを詰めていく
上記のものは本当に骨組みレベルの設計ですが、
ここからもっと細かい仕様とすり合わせて詰めていきます。
補足仕様1：サイト運営者側で、購入された商品の統計（個数など）を見れるようにしたい
こういう仕様があった場合、元の形でも不可能ではありませんが、
統計を取りやすいよう、注文レコードを以下のような形にするほうがベターでしょう。
（そうでなくとも、拡張性や保守性を考えると、この方が扱いやすい可能性が高いですが）
- 注文（注文ID、会員ID、購入日時、決済方法、…）
- 注文サブ（注文ID、商品ID、個数、…）
- ひとつの注文に対して、購入された商品種類の数だけのレコードができる
補足仕様2：住所の入力時、郵便番号を入力したら都道府県～地域名が自動で入力されるようにしたい
よく見かける機能ですが、これだけのためにテーブルが最低でも一つ増えることになります。
- 郵便番号と住所の対応（郵便番号、都道府県～地域名）
「郵便番号を7桁にするか3桁＋4桁にするか」や「都道府県、市区町村、地域、に分けるか」など
入力フォームの形などに応じて調整することになりますが、
個人的には、拡張性を考慮し、分ける方を推奨したいです。
（郵便番号くらいなら機械的に分けられますが、
住所文字列を分割したりデータを作り直したりするのはコストが大きいです）
郵便番号や住所地名の保守は面倒なので、正直こういう入力フォームは回避したいです
…あと、実務ではこういうの（ YubinBango ）を使うことも検討しましょう。
今回はあくまでデータベース設計の話なのでこのままいきます。
さて、ここまでで改めてデータベース全体を見てみるとこんな感じです。
- 会員（会員ID、メールアドレス、パスワード、氏名、住所、電話番号、…）
- 商品（商品ID、商品名、価格、説明文、…）
- 注文（注文ID、会員ID、購入日時、決済方法、…）
- 注文サブ（注文ID、商品ID、個数、…）
- 地域（郵便番号、都道府県～地域名）
「正規化」「敢えて非正規化」を意識する
正規化とは何かというものを説明すると長くなるのですが、
一言で言うと「同じデータを複数の場所に置かないこと」です。
例えば先ほどの「地域」テーブルですが、
「都道府県、市区町村、地域」に分けるとして、
都道府県や市区町村をどう扱いますか？
「東京都 新宿区 西新宿」のように文字列で扱うこともできますが、
都道府県マスタや市区町村マスタを用意する方法もあります。
構造は複雑になりましたが、こちらの方が保守性は向上します。
例えば市名に変更があった場合は市区町村テーブルのレコードを修正するだけでよいのです。
文字列で扱っている場合は関連する全レコードの更新が必要になる上、
余計なミスやトラブルを招きかねません。
（例えば東京都と広島県の両方に「府中市」があります。
それを知らずに市区町村の文字列指定のみでUPDATEしてしまうとか…）
ですが、敢えて非正規化、つまり同じデータを複数の場所に置く方が有用な場合もあります。
上記のようなテーブル構造にした場合、
そもそも必要としている「郵便番号に該当する住所を取得する」クエリが…
SELECT `都道府県`.`都道府県名` ,`市区町村`.`市区町村名` ,`地域`.`地域名` FROM `地域` INNER JOIN `市区町村` ON `地域`.`市区町村ID` = `市区町村`.`市区町村ID` INNER JOIN `都道府県` ON `市区町村`.`都道府県ID` = `都道府県`.`都道府県ID` WHERE `地域`.`郵便番号` = 【入力された郵便番号】
こんな感じですかね。
もちろん、ひとつのテーブルで文字列で保持しているだけならこのとおり。
SELECT `地域`.`都道府県名` ,`地域`.`市区町村名` ,`地域`.`地域名` FROM `地域` WHERE `地域`.`郵便番号` = 【入力された郵便番号】
クエリが長いだけならともかく（関数などで適当に共通化すれば1回しか書かないし）、
場合によっては「重たいクエリ」になりかねません。
今回の例であれば適切なインデックスを張りさえすれば重くなることはないでしょうが、
「正規化すればするほど必ず良くなる」というわけではありません。
今回の例であっても、
「自治体名の変更なんてそう頻繁に発生しないからクエリの可読性と速度を優先する」
という理由で単純な文字列のみを扱うテーブルで、という選択もあり得るのです。
尚、両方のいいとこ取りをする方法もあります。
例えば、
- データの追加更新は、正規化したテーブルで行う
- 定期的に、非正規化した一時テーブルを生成する
- データの参照時（特に速度優先時）は一時テーブルを用いる
といった手段ですね。
例えば売り上げの統計情報はもちろん注文テーブルのデータを集計することになりますが、
統計ページを閲覧するたびに集計処理を行うのではなく、
定期的に更新するサマリテーブルを参照するだけにしてもよいのではないでしょうか。
また、非正規化とは別に、
「内容が同じに見えるが意味が異なる」データには注意する必要があります。
例えば、会員テーブルと注文テーブルの両方に「氏名」や「住所」などを持つ必要があるかどうか。
もしくは、商品テーブルと注文テーブルの両方に「商品名」「単価」などを持つ必要があるか。
いずれも仕様によりますが、基本的には、それぞれ持つ必要があるという結論になるのではないでしょうか。
ふつうは全く同じデータが入るように見えますが、
例えば、会員が注文を完了した直後に会員情報の氏名と住所を変更した場合、
注文商品はあくまで注文時に入力した氏名・住所宛てに送るべきでしょう。
また、途中で商品名や価格を変更したとき、
売上統計だとか注文履歴だとかに反映されてしまっては困るでしょう。
運用上商品名や価格の変更は絶対にあり得ないのであれば一考の余地はありますが、
ふつうは注文情報はそのときのものを残しておくべきといえます。
そういった内容をいろいろ織り込んだものがこちら。
後編ではこのゆるふわ設計を元に、物理設計レベルまで落とし込んでいきます。
過去のエントリ
データベース設計に関連するものを貼っておきます。
テーブルの呼び方の話（わりとどうでもいい）
NoSQLの話
サマリテーブルにはこちらを併用するという手もあります
