テーブル設計・正規化・制約の基本

SQLを書けるようになっても、テーブル設計が崩れているとクエリは複雑になります。同じデータが複数箇所に保存されていたり、1つの列に複数の意味が詰め込まれていたりすると、更新漏れや集計ミスが起きやすくなります。

この章では、テーブル設計、正規化、制約の基本を整理します。

学習者

正規化って試験用語っぽく聞こえるけど、実務でも本当に必要なの？

テーブル設計で最初に考えること

テーブル設計では、まず「何をエンティティとして扱うか」を考えます。エンティティとは、システムで管理したい対象です。

たとえばECサイトなら、次のようなエンティティがあります。

ユーザー
商品
注文
注文明細
支払い
配送先

それぞれをテーブルとして表すと、データの責務が分かれます。

テーブル設計では、まず「何を別の概念として管理するか」を決めます。

主キーを決める

主キーは、テーブル内の1行を一意に識別する列です。

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL
);

sql

主キーには次の性質が必要です。

一意である
NULLにならない
後から変わりにくい

メールアドレスや電話番号を主キーにしたくなることがありますが、実務では変更される可能性があります。そのため、多くのシステムでは id のようなサロゲートキーを使います。

外部キーで関係を表す

外部キーは、別テーブルの主キーを参照する列です。

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  ordered_at TIMESTAMP NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

sql

この例では、orders.user_id が users.id を参照しています。つまり「この注文はどのユーザーのものか」を表しています。

外部キー制約を設定すると、存在しないユーザーIDを持つ注文を防げます。

-- users.id = 999 が存在しないならエラーになる
INSERT INTO orders (id, user_id, ordered_at)
VALUES (1, 999, CURRENT_TIMESTAMP);

sql

先生

正規化は試験用語じゃなくて実務で毎日使う考え方。「同じ情報を2箇所に持たない」を意識するだけで、更新漏れやバグがぐっと減るよ。

制約でデータを守る

制約は、テーブルに入るデータのルールです。

制約	役割
`PRIMARY KEY`	行を一意に識別する
`FOREIGN KEY`	別テーブルとの参照関係を守る
`UNIQUE`	重複を禁止する
`NOT NULL`	NULLを禁止する
`CHECK`	値の条件を制限する

たとえば、メールアドレスの重複を禁止するなら UNIQUE を使います。

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL
);

sql

ステータスの値を制限したい場合は CHECK が使えます。

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  status VARCHAR(20) NOT NULL,
  CHECK (status IN ('pending', 'paid', 'cancelled'))
);

sql

DBMSによって CHECK の対応や実運用での使い方は異なりますが、「不正な値をDBに入れない」という考え方は重要です。

制約は、アプリケーションのバグや運用ミスからデータを守る最後の防衛線です。

1対多と多対多

テーブルの関係には、よく出るパターンがあります。

1対多

1人のユーザーが複数の注文を持つ場合、orders に user_id を置きます。

users
  id
 
orders
  id
  user_id

sql

「多」側に外部キーを置くのが基本です。

多対多

商品とタグのように、双方が複数を持てる場合は中間テーブルを作ります。

products
  id
 
tags
  id
 
product_tags
  product_id
  tag_id

sql

中間テーブルには、両方の外部キーを置きます。

CREATE TABLE product_tags (
  product_id BIGINT NOT NULL,
  tag_id BIGINT NOT NULL,
  PRIMARY KEY (product_id, tag_id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (tag_id) REFERENCES tags(id)
);

sql

学習者

制約をたくさん付けると、開発中にINSERTが面倒になりません？外しておいた方が楽では？

先生

面倒に感じるのは正しいデータを入れようとしている証拠。制約を外すと本番で不正データが入ったときに気づけなくなるから、開発中もなるべく付けておこう。

テーブル設計と制約を丁寧に決めることが、長期的に保守しやすいシステムの土台になります

正規化とは

正規化は、データの重複や更新不整合を減らすために、テーブルを適切に分ける考え方です。

たとえば、注文テーブルに商品名や商品価格をそのまま何度も持つと、商品名変更時に過去の注文データまでどう扱うかが難しくなります。商品情報は products、注文時点の明細は order_items のように役割を分けます。

正規化は「同じ事実を複数箇所に持たない」ための設計手法です。

第1正規形

第1正規形は、1つの列に複数の値を詰め込まない状態です。

-- 避けたい: tags に複数値を詰め込んでいる
products
  id
  name
  tags  -- "sale,new,recommend"

sql

この設計だと、タグで検索したり、タグごとに集計したりするのが難しくなります。別テーブルに分けます。

products
  id
  name
 
tags
  id
  name
 
product_tags
  product_id
  tag_id

sql

第2正規形・第3正規形

第2正規形や第3正規形は、ざっくり言うと「主キーに対して正しく依存していない列を分ける」考え方です。

たとえば注文明細に product_name と product_category_name を毎回持つと、商品カテゴリ名が変わったときに多くの行を更新する必要があります。

-- 避けたい例
order_items
  order_id
  product_id
  product_name
  product_category_name
  quantity

sql

商品名やカテゴリ名は商品側の情報として分けます。

products
  id
  name
  category_id
 
categories
  id
  name
 
order_items
  order_id
  product_id
  quantity

sql

ただし、注文時点の商品名や価格を履歴として残したい場合は、あえて order_items に持つ設計もあります。これは非正規化というより、履歴として別の事実を保存していると考えます。

非正規化する判断

正規化は基本ですが、常に細かく分ければよいわけではありません。読み取り性能や履歴保持のために、あえて重複を許すことがあります。

非正規化を検討する例です。

集計結果を毎回計算すると重すぎる
注文時点の商品名や価格を履歴として残したい
検索画面で頻繁に使う表示用データをまとめたい
大量データでJOINがボトルネックになっている

よくあるハマりどころ

CSV文字列を1列に入れる

-- 避けたい
users
  id
  favorite_category_ids -- "1,3,8"

sql

この設計は検索、JOIN、制約、集計が難しくなります。中間テーブルに分けましょう。

user_favorite_categories
  user_id
  category_id

sql

テーブルは管理したい概念ごとに分ける
主キーは行を一意に識別する
外部キーはテーブル間の関係を守る
UNIQUE、NOT NULL、CHECK で不正な値を防ぐ
正規化は重複と更新不整合を減らすための考え方
非正規化する場合は、理由と更新ルールを残す

次の章では、インデックス、トランザクション、SQLインジェクション対策など、実務で必要になる運用寄りの知識を扱います。

SQLクイズに挑戦するこの章で学んだSQLの知識を、4択クイズでアウトプットして定着させよう