ざっくりSQL入門 #2：データベース設計＜後編＞
前編で書いたとおり、これやります。
後編ではこのゆるふわ設計を元に、物理設計レベルまで落とし込んでいきます。
物理名を付ける
物理名というのは、平たく言うと、実際に使うときの名称ですね。
これまで日本語で考えていた、要するに便宜上の表現については論理名と呼ばれます。
例えば会員テーブルであれば
会員（会員ID、メールアドレス、パスワード、氏名、郵便番号、住所、電話番号）
↓
user（user_id, mail, password, name, zip, address, tel）
という感じですね。
業務での設計であればアプリ側の命名規則や慣習も含めて考慮してください。
また、予約語はできるだけ避けましょう。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.3 予約語
ゆるふわ設計に物理名を付けたものがこちら。
- user（user_id, mail, password, name, zip, address, tel）
- product（product_id, name, price）
- order（order_id, user_id, order_datetime, price_sum, name, zip, address, tel）
- order_product（order_id, product_id, product_name, price, count）
- pref（pref_id, name）
- city（city_id, pref_id, name）
- area（area_id, zip, city_id, name）
- product_summary（product_id, order_date, count）
インデックスを設定する
ここでは「プライマリ」「ユニーク」「(通常の)インデックス」の3種類を設定します。
プライマリ
主キーとも呼ばれるものですね。ひとつのテーブルにひとつ設定します。
これは何かというと、
「一意かつ変更が発生しない、レコードを特定するためのデータ」
という認識でよいかと思います。
自動採番のIDや、そういったIDの組み合わせで成り立つ複合インデックスがよいでしょう。
必要がなければ設定しなくてもよいのですが、phpMyAdminからの更新操作ができなくなったりします。
ここでは、例えばuserのuser_idや、order_productのorder_idとproduct_idの複合などになるでしょう。
ユニーク
単純に「仕様上、一意に制限したいデータ」です。
userテーブルのmailをログインIDとしても使用する場合、ユニーク設定した方がよいでしょう。
これは変更の可能性がある値なので、プライマリには向きません。
また、prefテーブルのname、つまり都道府県名はまず間違いなく重複しませんが、
わざわざユニーク設定する必要はないでしょう。
インデックス
プライマリやユニークにも言えることですが、辞書の索引のようなものです。
それらと異なるのは一意のデータである必要がない点で、検索や並び替えを高速に行うことを目的とした設定です。
例えば、「商品一覧ページは商品名順に表示する」という仕様ならproductのnameに張ったり、
郵便番号から地域を検索するためareaのzipに張ったりすることになります。
インデックスは設定すればするほどよいわけではなく、
多すぎる場合は更新系が遅くなるため、状況に合わせて適切な設定を行う必要があります。
クエリに慣れていないうちは後回しにして、
アプリケーションの実装時に随時追加していく感じでOKです。
データ型を決める
例えば会員IDは数値(整数)、氏名は文字列…というふうに、型とサイズを割り当てていきます。
数値
「整数ならINT、小数ならDECIMAL」を覚えておけばOKです。
INTは4バイト（-2,147,483,648～2,147,483,647）、DECIMALは全体で65桁・うち小数部30桁まで対応できます。
桁数が足りなくなったらBIGINTなど他の型を検討しましょう。
最大5桁の整数ならINT(5)、99.999まで対応する小数ならDECIMAL(5,3)、という設定になります。
また、BOOLEAN型（つまりtrue/false）も一応存在するのですが、
中身はINTのコンパクト版である「TINYINT」と同じ扱いで、
0がfalse、それ以外がtrueという扱いになります。
個人的には、明示的にただの整数として「TINYINT(1)」に設定し、
falseなら0、trueなら1を入れるようにしています。
日時
「日付だけならDATE、時刻だけならTIME、日時ならDATETIME」を覚えておけばOKです。
文字列
とりあえずは「VARCHAR」だけ覚えておけばOKです。
最大65535文字。
最大16文字に設定する場合はVARCHAR(16)、となります。
最大65535文字というのは、プレーンな文章で考えると、一般的な論文くらいなら入るサイズです。
仮にそれより長くなる場合は「MEDIUMTEXT（最大16MB）」「LONGTEXT（最大4GB）」を検討します。
（HTMLタグを含めたソースを丸々保存するとか、
大量のデータをJSON化したテキストを保存するなどといった用途で
65535文字をオーバーしたことはあります…）
また、例えば郵便番号のように、
どんなデータでも概ね長さが同じものであれば「CHAR」を使用する方が高速になる可能性があります。
こちらは最大で255文字です。
照合順序を決める
これは完全にMySQLの話なのですが、
データベース・テーブル・文字列カラムに「照合順序」を設定します。
照合順序とは「文字コード＋ソート順」です。
まずデータベース自体に設定すると、テーブルやカラムはデフォルトでその設定に揃います。
基本的には「utf8mb4_general_ci」をオススメします。
（4バイト文字を含めたutf-8、かつアルファベットの大文字小文字を区別しない）
「○○_unicode_ci」にすると、大文字小文字だけでなく全半角やひらがなカタカナも区別しなくなりますが、
「○○_general_ci」系に比べるとパフォーマンスが落ちるようです。
例えば、商品名で検索する機能があるなら、
productのnameを「utf8mb4_unicode_ci」にしてもよいかもしれません。
（あいまいな検索は設定ではなくクエリで対応することも可能なので、generalでもOKです）
NULLの可否とデフォルト値を決める
NULLの可否というのは、つまり「そのカラムが空っぽ（NULL）でも良いかどうか」ですね。
例えば、郵便番号160-0000は「東京都新宿区」なので、areaのnameは空になります。
その仕様を許すのであれば「NULL可」とします。
デフォルト値は読んで字のごとく、
値を指定せずにデータ追加したときに勝手に入れてくれるデフォルトの値です。
例えば、orderのorder_datetimeのデフォルト値に「現在日時」を指定しておくことで、
orderへのレコード追加をする際、order_datetimeを指定しなくても現在日時が入ってくれます。
もちろん決まった数値や文字などの固定値もOKです。
その他の設定
あとは、以下のような機能を使うかどうか決めていきます。
- IDの用途で自動で採番してくれる「auto_increment」設定
- 整数型について、正の数だけを許可する「UNSIGNED」属性
- 負数を使わない分、最大値が2倍程度になるというメリットがあります
- 最大桁数に満たない場合はゼロで埋める「UNSIGNED ZEROFILL」属性もあります
- 例えばINT(3)に「1」を入れると「001」になる
- 日時型について、更新日時を自動上書きする「on update CURRENT_TIMESTAMP」設定
- レコードの更新が発生したときのみ、そのときの日時で更新される
完成
ここまで決めれば概ね完成です。
あとは何かあれば随時直していきましょう！
アンダーラインがついたカラムはプライマリキーです。
- user（会員）
- mailにユニークキー設定
- 個人情報は注文を行うまでは非必須のためNULL可
| 物理名 | 論理名 | 型 | NULL | デフォルト値 | その他 |
| user_id | 会員ID | INT(11) | - | - | auto_increment, UNSIGNED |
| メールアドレス | VARCHAR(255) | - | - | - | |
| password | パスワード | VARCHAR(16) | - | - | - |
| name | 氏名 | VARCHAR(16) | ○ | NULL | - |
| zip | 郵便番号 | CHAR(7) | ○ | NULL | - |
| address | 住所 | VARCHAR(64) | ○ | NULL | - |
| tel | 電話番号 | VARCHAR(16) | ○ | NULL | - |
- product（商品）
| 物理名 | 論理名 | 型 | NULL | デフォルト値 | その他 |
| product_id | 商品ID | INT(11) | - | - | auto_increment, UNSIGNED |
| name | 商品名 | VARCHAR(32) | - | - | - |
| price | 単価 | INT(11) | - | - | UNSIGNED |
- order（注文）
| 物理名 | 論理名 | 型 | NULL | デフォルト値 | その他 |
| order_id | 注文ID | INT(11) | - | - | auto_increment, UNSIGNED |
| user_id | 会員ID | INT(11) | - | - | UNSIGNED |
| order_datetime | 注文日時 | DATETIME | - | CURRENT_TIMETAMP | - |
| price_sum | 決済金額 | INT(11) | - | - | UNSIGNED |
| name | 氏名 | VARCHAR(16) | - | - | - |
| zip | 郵便番号 | CHAR(7) | - | - | - |
| address | 住所 | VARCHAR(64) | - | - | - |
| tel | 電話番号 | VARCHAR(16) | - | - | - |
- order_product（注文サブ）
| 物理名 | 論理名 | 型 | NULL | デフォルト値 | その他 |
| order_id | 注文ID | INT(11) | - | - | UNSIGNED |
| product_id | 商品ID | INT(11) | - | - | UNSIGNED |
| product_name | 商品名 | VARCHAR(32) | - | - | - |
| price | 単価 | INT(11) | - | - | UNSIGNED |
| count | 個数 | INT(11) | - | - | UNSIGNED |
- pref（都道府県）
| 物理名 | 論理名 | 型 | NULL | デフォルト値 | その他 |
| pref_id | 都道府県ID | INT(11) | - | - | auto_increment, UNSIGNED |
| name | 都道府県名 | VARCHAR(4) | - | - | - |
- city（市区町村）
| 物理名 | 論理名 | 型 | NULL | デフォルト値 | その他 |
| city_id | 市区町村ID | INT(11) | - | - | auto_increment, UNSIGNED |
| pref_id | 都道府県ID | INT(11) | - | - | UNSIGNED |
| name | 市区町村名 | VARCHAR(16) | - | - | - |
- area（地域）
| 物理名 | 論理名 | 型 | NULL | デフォルト値 | その他 |
| area_id | 地域ID | INT(11) | - | - | auto_increment, UNSIGNED |
| zip | 郵便番号 | CHAR(7) | - | - | - |
| city_id | 市区町村ID | INT(11) | - | - | UNSIGNED |
| name | 地域名 | VARCHAR(16) | - | - | - |
- product_summary（商品別売上個数サマリ）
| 物理名 | 論理名 | 型 | NULL | デフォルト値 | その他 |
| product_id | 商品ID | INT(11) | - | - | UNSIGNED |
| order_date | 購入日 | INT(11) | - | - | - |
| count | 個数 | INT(11) | - | - | UNSIGNED |
…外部キー制約は？
あー聞こえない聞こえない
設定したい場合はググってください。
設定しなくて困ったことはない。
設定しない場合、今回の例であり得そうな不具合例は
「注文取消し時、orderから削除したがorder_productは残したままで、
producr_summaryでの計算に使われてしまった」
あたりですかね。
そもそも注文取消しであればいきなり削除するのではなく
注文ステータス的なもので管理する気がします。
