本文へスキップ
BecomeCoder
ブログ一覧Wikiコース一覧

テーブル設計のやり方｜正規化（第1〜第3正規形）をわかりやすく

#データベース#テーブル設計#正規化#SQL#初心者

結論：テーブル設計は「登場する“モノ”を洗い出す → モノごとに表を分ける → 各行を1つに特定できるキーを決める → 表と表を外部キーでつなぐ → 制約で守る」の順に進めます。正規化とは、この「モノごとに表を分ける」作業に第1〜第3正規形という段階の名前を付けたものです。 難しい理論のように見えますが、原則はたった一つ、「1つの事実は1か所にだけ書く」 だけです。

この記事は「テーブルをどう設計するか」という作り手側の手順に絞っています。SQLの学習順序そのものは データベース初心者は何から始めるか、JOIN の書き方は SQL JOINがわからない人向け で扱っています。

なぜ「1枚の表」ではダメなのか

設計の話は、悪い例から入ると一気にわかります。ショップの注文を、何も考えず1枚の表で持ったとします。

注文ID会員名都市商品（複数）単価
5田中福岡ノートPC, USBケーブル128000, 800
6田中福岡USBケーブル800

一見これで用は足りそうですが、運用を始めた瞬間に3種類の事故が起きます。教科書では更新時異常・挿入時異常・削除時異常と呼ばれるものです。

つまり、「事実が複数の場所に書かれている」ことと「別々の事実が1つの表に同居している」ことが原因です。表を分ければ全部防げます。この出発点は なぜ表を「分けて」設計するのか でそのままSQLを打ちながら確認できます。ブラウザ上で本物のSQLエンジンが動くので、インストールは要りません。

正規化の手順 ― 第1〜第3正規形

上の表を、3段階で整えていきます。これが正規化です。

第1正規形（1NF）― 1マスに1つの値

「商品（複数）」の欄に ノートPC, USBケーブル と2つ詰め込むのをやめ、1商品＝1行にばらします。

なぜかというと、1マスに複数値が入っていると「USBケーブルを買った人を数える」ことすらできないからです。SQLは列の値を比較する言語なので、WHERE 商品 = 'USBケーブル' はカンマ区切りの文字列にヒットしません。

ここで生まれるのが order_items（注文明細）という表です。「注文1件に明細が複数ぶら下がる」という関係で、これを1対多と呼びます。関係の種類は リレーションの種類 ― 1対多という関係 で図解しています。

第2正規形（2NF）― 主キーの一部で決まる列を追い出す

注文明細のキーは「注文ID＋商品ID」の組み合わせです。ではその行にある「単価」は何で決まるでしょうか。商品IDだけで決まります。注文が変わっても商品の定価は変わりません。

このように「キーの一部だけで決まってしまう列」は、その一部をキーとする別の表へ移します。単価は products（商品マスタ）へ引っ越し、商品マスタが独立します。

これを守らないと、商品の値段を改定したときに、その商品を含む全明細を書き換える羽目になります。

第3正規形（3NF）― キー以外の列にぶら下がる列を追い出す

注文表に「会員名」「都市」を持っているのが最後の問題です。注文IDが決まれば会員IDが決まり、会員IDが決まれば都市が決まる。都市は注文ではなく会員の属性です。キーではない列（会員ID）に別の列（都市）がぶら下がっている状態を推移的関数従属と呼び、これを切り離すと users（会員マスタ）が独立します。

結果、1枚の表は users / products / orders / order_items の4つに分かれ、最初に挙げた3つの異常がすべて消えます。この流れは 正規化 ― 重複を断つ設計の型 で、実際のデータを結合し直しながら追えます。

3つの正規形を一言でまとめる

段階やること覚え方
第1正規形1マスに1値・繰り返しを行に分解表の形を整える
第2正規形キーの一部で決まる列を分離商品マスタが生まれる
第3正規形キー以外に依存する列を分離会員マスタが生まれる

「とりあえず第3正規形まで」とよく言われるのは、この3段階で更新・挿入・削除の異常がほぼ起きなくなるからです。第4・第5正規形もありますが、まず3NFを手で作れることが先です。

キーと制約が設計を守る

正規化して表を分けたら、次は「その分け方をデータベース自身に守らせる」段階です。ここを飛ばすと、せっかく分けた表が数か月でぐちゃぐちゃになります。

主キー（PRIMARY KEY） は「この列の値を見れば行が1つに特定できる」という宣言です。会員なら会員ID、注文なら注文ID。名前や電話番号を主キーにしないのは、改名・改番があり得るからで、多くの場合は連番のIDを自動採番させます。作り方は 主キーと自動採番 ― IDを自動で振る を実際に動かすのが早いです。

外部キー（FOREIGN KEY） は「この列の値は、あちらの表に必ず存在する」という宣言です。存在しない会員IDの注文を入れようとするとデータベースが拒否してくれるので、迷子データが生まれません。外部キーと参照整合性 ― 迷子のデータを防ぐ では、わざと違反させてエラーを出しながら確かめられます。

そのほか、NOT NULL・UNIQUE などの基本的な制約は 制約 ― 正しいデータだけを守る、値そのものの範囲を縛る CHECK や既定値の DEFAULT は CHECK と DEFAULT ― 値そのものを縛る にまとまっています。テーブルを作る文法自体があやふやなら データ型と CREATE TABLE ― テーブルを作る から。いずれもブラウザ上でそのまま CREATE TABLE を実行できる回です。

制約は「面倒なもの」ではなく「バグを本番前に止めてくれる仕組み」です。制約に弾かれたときのエラーは決まった文言なので、FOREIGN KEY constraint failed や UNIQUE constraint failed を読めば、何がまずかったかすぐ分かります。

多対多はどう設計するか

「1人の会員が複数の商品を買う」「1つの商品が複数の会員に買われる」のような多対多の関係は、そのままでは表現できません。答えは決まっていて、間に中間テーブルを1枚置くことです。先ほどの order_items がまさにそれで、注文と商品を結ぶ役をしています。

タグ付け（記事とタグ）、受講登録（学生と授業）など、業務システムの多くの「〜が〜を複数持つ」は中間テーブルで解けます。作り方は 多対多（N:M）と中間テーブル を参照してください。

どこまで正規化するか ― 実務の落としどころ

正規化は万能ではありません。表を細かく分けるほど、データを取り出すときの JOIN が増えて、クエリは複雑になり読み取りは遅くなります。

現場での基本方針はシンプルです。

  1. まず第3正規形で設計する。 迷ったら分ける。正しさを先に確保する。
  2. 遅い場所が実際に測れてから、非正規化（あえて重複を持つ）を検討する。想像で先回りしない。
  3. 非正規化するなら、重複を同期する責任が誰にあるかを決めてからにする。集計値を別表に持つなら、それを更新する処理を必ず用意する。

「遅い」の対処は、設計を崩す前にまず索引で解決できることが多いです。検索に使う列に索引を張る話は インデックス ― 検索を速くする索引、複合索引の考え方は 複合・ユニークインデックス にあります。索引を増やせば読み取りは速くなる一方で書き込みは重くなる、というトレードオフも実行しながら確認できます。

なお、そもそも関係データベース以外を選ぶ判断もあります。その比較は SQLとNoSQLの違い にまとめました。

設計の進め方（実際の手順）

紙の上でやることは、ほぼこの順番です。

  1. 要件を文章で書く。 「会員が商品を注文する」。この文の名詞がテーブル候補、動詞が関係になります。
  2. モノごとに表を分ける。 会員・商品・注文・注文明細。
  3. 各表の主キーを決める。 1行を一意に特定できるか声に出して確認する。
  4. 関係を線で結ぶ。 1対多か多対多か。多対多なら中間テーブルを足す。
  5. 列を配る。 「この列は本当にこの表の事実か？」と1列ずつ問う（＝第3正規形の確認）。
  6. 制約を書く。 NOT NULL・UNIQUE・外部キー・CHECK。
  7. CREATE TABLE に落とす。 ここで初めてSQLを書く。

この流れを図にしたものがER図です。書き方と読み方は データモデリングと ER 図 ― 設計の地図を描く、要件から実装まで通しでやる回は 設計から実装まで ― 総合演習 にあります。

つまずきやすいところ

次に読む・次に動かす

← ブログ一覧に戻る