データベース設計！テーブルの基礎知識&正規化とはなんぞや？

論理設計と正規化

概要

この記事は、達人に学ぶ DB 設計徹底指南書を読み学習した内容を個人学習用にまとめ直したものです。

この記事では、データベース設計の論理設計、テーブルの構成要素、キー、制約、正規化について記述しています。

テーブルとは何か？

データベースにおけるテーブルの定義は以下の通り。

• テーブルとは共通点を持ったレコードの集合
• 二次元表と似ているが、各レコードは上記の条件を満たす必要がある(形だけ同じではダメ)
• テーブルは英語ならば複数形/複数名詞で書ける(そうでなければどこかに間違いがある)

Users テーブルの例:

id	name	email	created_at
1	Saito	[e-mail]	2025-12-01 09:12:30
2	Tanaka	[e-mail]	2025-12-03 14:05:10
3	Suzuki	[e-mail]	2025-12-07 21:44:02

テーブルの構成要素

• 行と列
• キー
• 制約

行と列

• 行(レコード)
  • テーブルの横方向に並ぶデータの組
• 列(カラム)
  • テーブルの縦方向に並ぶデータの組

キー

あるレコード(一行とは限らない)を特定するための列の組み合わせ。

以下の二つの種類がある。

• 主キー
• 外部キー

主キー(プライマリキー)

• その値を特定すれば、必ず一行のレコードを特定できる列の組み合わせ
• テーブルに置いて必ず一つ存在しなければならず、かつ一つしか存在しない
• テーブルのサンプルを表すときは、わかりやすく下線がつけられることが多い
• 複数列を組み合わせなければならない場合もあり、その場合は複合キーと呼ばれる

上記の特徴から、理論的にはテーブルには重複行は存在できない。(そのようなテーブルも作れるが設計上、許されない)

id 列が主キーである例:

id	name	email	created_at
1	Saito	[e-mail]	2025-12-01 09:12:30
2	Tanaka	[e-mail]	2025-12-03 14:05:10
3	Suzuki	[e-mail]	2025-12-07 21:44:02

外部キー

• 二つのテーブルの列同士で設定する
• 親テーブル(外部キーで参照されるテーブル)に存在しないデータは子テーブル(外部キーを持つテーブル)に登録できない制約を課す(参照整合性制約)
• 親テーブルには自由にデータを登録できる
• 親テーブルのレコードが削除された場合に対応する外部キーを持つ子テーブルのレコードも一緒に削除する(カスケード)か、そのような削除 sql をエラーにするかは選択可能
• 一番望ましいのは、外部キーが設定されている場合は、データの削除は子から順に操作すること

user_id 列が外部キーである例:

Users テーブル

id	name
1	Saito
2	Tanaka
3	Suzuki

Posts テーブル

id	user_id	title
1	1	Hello World
2	1	My First Post
3	2	Sample Post

user_id 列に追加できるのは、Users テーブルに存在する id のみ(参照整合性制約)。

キーとなる列にはコードや ID などの表記体系が定まった固定長文字列を用いる

「名前」のように可変長文字列として定義された列をキーとして使用すると、同じ名前であっても微妙に異なる表記となることがあるため、ヒットするはずの文字列でキーがヒットしなかったり、キーが重複して見えてしまったりすることがある。

例:

以下は同じ人物名で空白を含むか否かの表記の違いのみだが、データベース上は違う文字列として扱われるので、外部キーとしては不一致となり結びつかない

「山田太郎」と「山田 太郎」

こうした理由から、キーに使用する列はコードや ID などの表記体系が定まった固定長文字列のデータ型に格納して使うのが RDB のテーブル設計の鉄則となる。

制約

テーブルにおける制約は前項の「参照整合性制約」に加えて以下の三つが代表的である。

• NOT NULL 制約
• 一意性制約
• CHECK 制約

NOT NULL 制約

• データカラムに NULL を含めることを禁止する制約
• 列単位で設定可能
• テーブル定義においては、列には可能な限り NOT NULL 制約を付与する
  • 可能な限りデータは NULL にしないというデータベース設計の原則に従う
• 主キーとなる列には DBMS 側で暗黙に NOT NULL 制約が付与される

一意性制約

• ある列の組について一意性を求める制約
• 主キーが一テーブルに一つしか設定できないのに対し、一意制約は何個でも設定可能

CHECK 制約

• ある列のとりうる値の範囲を制限する制約
• 一つのテーブルにつき、何個でも設定可能
• 複数列にまたがった制約は設定できない(例: A 列 > B 列 のような条件)

テーブルの列と名前のルール

• 名前に使える文字集合
  • 半角アルファベット
  • 半角数字
  • アンダーバー(_)
• 最初はアルファベット
  • 例: 2025_usersや_usersのように、名前の先頭をアルファベット以外で初めてはならない
• 名前は重複してはならない
  • DBMS が設定する範囲内(ドメイン/スキーマ)においては同じ名前のテーブルは作成できない
  • 同じテーブル内では同じ名前の列は作成できない

正規化とは何か？

データベース設計の知識がなく、行き当たりばったりでテーブルを作成し、データを格納していくと以下のような不都合が生じてくる。

• 一つの情報が複数のテーブルに存在して無駄なデータ領域と面倒な更新処理を発生させてしまう(冗長性)
• 冗長なデータ保持によって、更新処理のタイムラグによってデータの不整合が発生したり、そもそもデータを登録することができないようなテーブルを作ってしまう(非一貫性)

上記のような問題点を解決するために考案された方法論が正規化である。

正規形の定義

正規形とは、正規化によって作られるデータ形式。

すなわち、データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式。

正規形のレベルは第 5 まであるが、通常は第 3 正規形まで達成すればほとんどの場合は問題ないことが多い。

第 1 正規形

第一正規形の定義は「一つのセルの中には一つの値しか含まない」というもの。

一つのセルに一つだけの値が含まれているとき、この値のことをスカラ値と呼ぶ。

第 1 正規形の作りかた

まず、以下のように一つのセルの中に複数の値が含まれるテーブルを考える。

第一正規化する前のテーブル:

employee_id	employee_name	dependents
1001	Saito	Hanako,Taro
1002	Tanaka
1003	Suzuki	Yuko

1. 複数の値が含まれるセルを分割する

セルの中に一つの値が含まれるようにレコードを増やしたテーブル:

employee_id	employee_name	dependent_name
1001	Saito	Hanako
1001	Saito	Taro
1002	Tanaka
1003	Suzuki	Yuko

しかし、単純に行を増やしただけのこの状態では以下のような問題が発生する。

• 主キーを決められない(一行を特定するのに最低でも三つの列の値が必要)
  • 上記の理由から三つのカラム全てが主キー扱いとなるが、主キーには一部であっても NULL を含めてはならない(dependent_name には NULL が含まれる場合がある)
  • 解決方法として、dependent_name を持たない場合は「no dependent」など、一律で特定の値を入れることで解決する方法もあるが、以下の二つ目の問題を解決できない
• employeeとdependentという二つのエンティティの情報を含んでしまっており、テーブルの意味やレコードの単位をすぐに理解できない

2. テーブルを分割する

前項の問題を解決するためには、employeeとdependentという二つのエンティティでテーブルを分割する。

dependentにはdependent_nameを持っているemployeeレコードの情報のみを保持することで主キーが NULL になることがなくなる。

完全な第一正規化を行ったテーブル:

employees テーブル

employee_id	employee_name
1001	Saito
1002	Tanaka
1003	Suzuki

dependents テーブル

dependent_id	employee_id	dependent_name
1	1001	Hanako
2	1001	Taro
3	1003	Yuko

第一正規形式で分割したテーブルへの SQL

employee_idをキーにして結合すれば、employee_nameとdependent_nameの情報を結びつけられる。

1. 内部結合（INNER JOIN）

扶養者が「存在する社員だけ」を取得する SQL

SELECT
  e.employee_id,
  e.employee_name,
  d.dependent_name
FROM employees e
JOIN dependents d
ON e.employee_id = d.employee_id;

employee_id	employee_name	dependent_name
1001	Saito	Hanako
1001	Saito	Taro
1003	Suzuki	Yuko

• 扶養者が 1 人以上いる社員のみ
• 扶養者を持たない社員は 結果に出ない

2. 外部結合（LEFT JOIN）

扶養者が「いない社員も含めて」取得する SQL

SELECT
  e.employee_id,
  e.employee_name,
  d.dependent_name
FROM employees e
LEFT OUTER JOIN dependents d
ON e.employee_id = d.employee_id;

employee_id	employee_name	dependent_name
1001	Saito	Hanako
1001	Saito	Taro
1002	Tanaka	NULL
1003	Suzuki	Yuko

• すべての社員が必ず 1 行以上出る
• 扶養者がいない社員は dependent_name が NULL

関数従属性

リレーショナルデータベースにおいて、関数従属性とは、「X 列の値を決めれば、Y 列の値が一意に決まる」という関係性。(なお、X および Y は複数の列の組み合わせも可能)

{employee_id} -> {employee_name}

第一正規形に反した、複数の値が含まれるセルが含まれるテーブルが許されないのは、上記の関数従属性が成り立たないため。

第 2 正規形(部分関数従属)

第一正規形を満たすが、第二正規形は満たしていないテーブルの例:

employee_projects テーブル:

employee_id	project_id	employee_name	assigned_at
1001	10	Saito	2025-12-01
1001	11	Saito	2025-12-05
1002	10	Tanaka	2025-12-02

上記のテーブルは複数の値を含むセルがないため第一正規形を満たすが、主キーは(employee_id, project_id)の複合キーであるのに対し、employee_name列は主キーの一部であるemployee_idにしか依存していない。

{employee_name} -> {employee_id}のみに依存

このように複合主キーの一部の列に対して従属する列がある場合、この関係を部分関数従属と呼ぶ。(対して、主キーを構成するすべての列に従属性がある場合を完全関数従属と呼ぶ)

第 2 正規形は、部分関数従属を解消することで得られる。

第 2 正規形の作りかた

第 2 正規形にするには、部分関数従属の関係にあるキー列と従属列だけ独立のテーブルにすれば良い。

分割した employees テーブル:

employee_id	employee_name
1001	Saito
1002	Tanaka

第二正規形にした employee_projects テーブル:

employee_id	project_id	assigned_at
1001	10	2025-12-01
1001	11	2025-12-05
1002	10	2025-12-02

第 2 正規形を行わない場合のデメリット

第 2 正規形を行わない場合、例えば第 2 正規化前のemployee_projectsテーブルの場合、以下のようなデメリットが存在する。

• プロジェクトにアサインされていない社員は、主キーの一部である project_id が NULL になってしまうため、テーブルに登録することができない。
• 社員 ID と社員名の対応関係が複数のレコードに重複して保持されるため、冗長性が生まれる。これにより、社員名を変更する際に更新漏れが起きると、データの不整合が発生する可能性がある。
  • 例: { empoyee_id: 1001, employee_name: Saito } と { empoyee_id: 1001, employee_name: Saito Satoru } のように内容が一致しない間違ったデータが登録されうる。
    第 2 正規化によって、「社員」と「プロジェクト」という異なるレベルのエンティティをそれぞれ独立したテーブルとしても分割することで、上記のデメリットを解消することができる。

無損失分解と情報の保存

情報を完全に保存したままテーブルを分割する操作のことを、無損失分解と呼ぶ。

そして、第 2 正規化は正規形にした後も元の非正規形に戻すことができる無損失分解であり、可逆的な操作である。

さらに正規化を元に戻すための逆操作はテーブル同士の結合である。

第 3 正規形(推移関数従属)

第 2 正規形を満たすが、第 3 正規形は満たしていないテーブルの例:

employee_id	employee_name	department_id	department_name
1001	Saito	10	Sales
1002	Tanaka	20	Engineering
1003	Suzuki	10	Sales

上記のテーブルは、主キーが一つのみであるため第 2 正規形を満たすが、以下のような問題がある。

• 社員が一人もいない部署(department_id)を登録することができない

これは、department_nameが主キーではないdepartment_idに依存しているため。つまり、主キーへの依存を考えると以下のような従属関係が発生している。

{employee_id} -> {department_id} -> {department_name}

上記のようなテーブル内部に存在する段階的な従属関係を推移関数従属と呼ぶ。

第 3 正規形は、推移関数従属を解消することで得られる。

第 3 正規形の作りかた

第 3 正規形にするには、推移関数従属の関係にあるキー列と従属列だけ独立のテーブルにすれば良い。
これにより以下の状態となる。

• 非キー列はキー列に対してのみ従属するようになる
• 社員(employees)と部署(departments)という二つのエンティティをそれぞれ独立したテーブルとしても可逆的に分割できる

employees テーブル:

employee_id	employee_name	department_id
1001	Saito	10
1002	Tanaka	20
1003	Suzuki	10

departments テーブル:

department_id	department_name
10	Sales
20	Engineering

正規化のまとめ

• 1. 正規化を行う目的は更新時の不都合/不整合を排除するため
  • データの冗長性を排除して、人間のオペレーションミスによるデータ不整合を防ぐ目的もある
• 2. 正規化は従属性を見抜くことで可能になる
  • テーブル内部に部分関数従属や推移関数従属が存在していれば正規化の対象となる
  • 従属関係は業務ロジックで決まるため、テーブル形式だけを見ても判断できない(業務分析の必要性)
• 3. 正規形はいつでも非正規形に戻せる
  • 正規化は無損失分解なので、非正規化テーブルにいつでも復元できる
  • 上記の理由から、高次の正規形は低次の正規形を含む(第 3 正規形は第 2 正規形を含む)

正規化は常にするべきか

• 第 3 正規形までは、原則として行う
• 関連エンティティが存在する場合は関連とエンティティが 1 対 1 に対応するよう注意する

正規化のメリット・デメリット

• メリット
  • データの冗長性が排除され、更新時の不整合を防止できる
  • テーブルの持つ意味が明確になり、開発者が理解しやすい
• デメリット
  • テーブルが増えるため、SQL 文で結合を多用することになり、パフォーマンスが悪化する

参考文献

この記事は以下の情報を参考にして執筆しました。

• 達人に学ぶ DB 設計徹底指南書