データベース正規化とは？初心者でもわかるデータ重複を防ぐSQL設計の基本
生徒
「データベースの正規化って何ですか?よく聞くんですが難しそうで…」
先生
「正規化は、データベースのテーブルを整理整頓して、同じ情報が何度も出てこないようにする作業のことです。例えば、紙の名簿で同じ住所を何度も書くのは面倒ですよね?それを防ぐ方法だと考えてください。」
生徒
「なるほど!でも、なぜそんなことをする必要があるんですか?」
先生
「同じデータが何度も登場すると、更新するときに全部直さないといけなくなって大変です。それに、データの矛盾も起きやすくなります。正規化すれば、効率よくデータを管理できるんですよ。」
1. データベース正規化とは何か?
データベースの正規化とは、テーブルの設計を見直して、データの重複や矛盾を防ぐための設計手法です。データベースは大量の情報を保存する場所ですが、設計が悪いと同じ情報が何度も保存されてしまい、管理が大変になります。
正規化を行うことで、データを効率的に保存し、更新や削除の際にエラーが起きにくくなります。これは、紙の書類を整理整頓するのと同じように、デジタルデータを整理する作業だと考えてください。
正規化は、データの重複を減らし、データベースの保守性を高めるための重要な設計テクニックです。
2. 正規化をしないとどんな問題が起きるか?
まず、正規化をしていないテーブルの例を見てみましょう。以下は、ある会社の受注管理テーブルです。
order_id | customer_name | customer_address | product_name | price
---------+---------------+-----------------------+--------------+-------
1 | 山田商店 | 東京都渋谷区1-2-3 | ノートPC | 80000
2 | 山田商店 | 東京都渋谷区1-2-3 | マウス | 2000
3 | 佐藤電機 | 大阪府大阪市4-5-6 | キーボード | 5000
4 | 山田商店 | 東京都渋谷区1-2-3 | モニター | 30000
5 | 鈴木工業 | 愛知県名古屋市7-8-9 | ノートPC | 80000
このテーブルには、いくつかの問題があります。
- データの重複: 山田商店の名前と住所が3回も登場しています。
- 更新の問題: もし山田商店が引っ越したら、3箇所すべてを修正しなければなりません。1箇所でも修正し忘れると、データに矛盾が生じます。
- 削除の問題: 佐藤電機の注文をすべて削除すると、佐藤電機の住所情報も消えてしまいます。
- 挿入の問題: 新しい顧客情報だけを登録したくても、注文がないと登録できません。
これらの問題を「更新異常」「削除異常」「挿入異常」と呼びます。正規化は、これらの異常を防ぐための手法なのです。
3. 第一正規化 - 繰り返し項目をなくす
正規化には段階があり、最初のステップが「第一正規化」です。第一正規化では、一つのセルに複数の値が入っている状態を解消します。
例えば、以下のような設計は第一正規化されていません。
customer_id | name | hobbies
------------+----------+------------------
1 | 田中太郎 | 読書, 映画, 旅行
2 | 鈴木花子 | スポーツ, 音楽
このテーブルでは、hobbies列に複数の趣味がカンマ区切りで入っています。これでは、特定の趣味を持つ人を検索するのが困難です。第一正規化では、一つのセルに一つの値だけを入れるようにします。
customer_id | name | hobby
------------+----------+--------
1 | 田中太郎 | 読書
1 | 田中太郎 | 映画
1 | 田中太郎 | 旅行
2 | 鈴木花子 | スポーツ
2 | 鈴木花子 | 音楽
これで、各セルに一つの値だけが入るようになりました。ただし、まだ名前が重複しているという問題は残っています。
4. 第二正規化 - 部分関数従属をなくす
第二正規化では、テーブルのキー(主キー)の一部にだけ依存している列を別のテーブルに分離します。少し難しく感じるかもしれませんが、具体例を見れば理解できます。
以下のような注文明細テーブルがあるとします。
order_id | product_id | product_name | quantity | unit_price
---------+------------+--------------+----------+-----------
1 | 101 | ノートPC | 2 | 80000
1 | 102 | マウス | 5 | 2000
2 | 101 | ノートPC | 1 | 80000
3 | 103 | キーボード | 3 | 5000
このテーブルでは、order_idとproduct_idの組み合わせが主キーです。しかし、product_nameやunit_priceは、product_idだけで決まります。つまり、主キーの一部(product_id)にだけ依存しているのです。
第二正規化では、これを2つのテーブルに分けます。
注文明細テーブル
order_id | product_id | quantity
---------+------------+----------
1 | 101 | 2
1 | 102 | 5
2 | 101 | 1
3 | 103 | 3
商品テーブル
product_id | product_name | unit_price
-----------+--------------+-----------
101 | ノートPC | 80000
102 | マウス | 2000
103 | キーボード | 5000
これで、商品名や単価の情報は商品テーブルに一度だけ保存されるようになりました。
5. 第三正規化 - 推移的関数従属をなくす
第三正規化では、主キー以外の列に依存している列を別のテーブルに分離します。これも具体例で見てみましょう。
以下のような従業員テーブルがあるとします。
employee_id | name | department_id | department_name
------------+----------+---------------+-----------------
1 | 山田太郎 | 10 | 営業部
2 | 佐藤花子 | 20 | 経理部
3 | 鈴木一郎 | 10 | 営業部
4 | 田中次郎 | 30 | 開発部
5 | 高橋美咲 | 20 | 経理部
このテーブルでは、department_nameはemployee_idに依存していますが、実際にはdepartment_idに依存しています。つまり、主キーを経由して間接的に依存しているのです。これを「推移的関数従属」と呼びます。
第三正規化では、これを2つのテーブルに分けます。
従業員テーブル
employee_id | name | department_id
------------+----------+---------------
1 | 山田太郎 | 10
2 | 佐藤花子 | 20
3 | 鈴木一郎 | 10
4 | 田中次郎 | 30
5 | 高橋美咲 | 20
部署テーブル
department_id | department_name
--------------+-----------------
10 | 営業部
20 | 経理部
30 | 開発部
これで、部署名は部署テーブルに一度だけ保存されるようになりました。もし部署名を変更する必要があっても、部署テーブルの1箇所を修正するだけで済みます。
6. 正規化されたテーブルをSQLで結合する
正規化すると、データは複数のテーブルに分かれます。では、分かれたデータをどうやって取得するのでしょうか?答えは「結合(JOIN)」です。
例えば、先ほどの従業員テーブルと部署テーブルから、従業員名と部署名を一緒に取得するには、以下のSQLを使います。
SELECT
e.name AS 従業員名,
d.department_name AS 部署名
FROM
employees e
INNER JOIN
departments d ON e.department_id = d.department_id;
このSQLでは、employeesテーブルとdepartmentsテーブルを、department_idというキーを使って結合しています。INNERJOINは、両方のテーブルに存在するデータだけを取得する命令です。
実行結果
従業員名 | 部署名
-----------+---------
山田太郎 | 営業部
佐藤花子 | 経理部
鈴木一郎 | 営業部
田中次郎 | 開発部
高橋美咲 | 経理部
正規化されていても、必要なデータは簡単に取得できることがわかります。
7. 正規化のメリットとデメリット
正規化には多くのメリットがありますが、デメリットもあります。両方を理解して、適切に設計することが大切です。
メリット
- データの重複がなくなる: 同じ情報を何度も保存する必要がありません。
- データの一貫性が保たれる: 更新時に矛盾が起きにくくなります。
- ディスク容量の節約: 重複データがないので、保存容量が少なくて済みます。
- 更新が簡単: 一箇所を修正すれば、すべてに反映されます。
デメリット
- テーブル数が増える: 管理するテーブルが多くなり、設計が複雑になります。
- 結合が必要: データを取得するときに、複数のテーブルを結合する必要があります。
- 検索速度の低下: 結合処理が増えると、クエリの実行速度が遅くなることがあります。
実際のシステム開発では、完全に正規化するのではなく、パフォーマンスを考慮して意図的に正規化を緩める「非正規化」を行うこともあります。
8. 実践例 - 正規化前と正規化後の比較
最後に、実際の例で正規化の効果を確認してみましょう。冒頭で紹介した受注管理テーブルを正規化します。
正規化前(問題のあるテーブル)
order_id | customer_name | customer_address | product_name | price
---------+---------------+-----------------------+--------------+-------
1 | 山田商店 | 東京都渋谷区1-2-3 | ノートPC | 80000
2 | 山田商店 | 東京都渋谷区1-2-3 | マウス | 2000
3 | 佐藤電機 | 大阪府大阪市4-5-6 | キーボード | 5000
4 | 山田商店 | 東京都渋谷区1-2-3 | モニター | 30000
正規化後
これを3つのテーブルに分割します。
顧客テーブル
customer_id | customer_name | customer_address
------------+---------------+----------------------
1 | 山田商店 | 東京都渋谷区1-2-3
2 | 佐藤電機 | 大阪府大阪市4-5-6
商品テーブル
product_id | product_name | price
-----------+--------------+-------
101 | ノートPC | 80000
102 | マウス | 2000
103 | キーボード | 5000
104 | モニター | 30000
注文テーブル
order_id | customer_id | product_id
---------+-------------+------------
1 | 1 | 101
2 | 1 | 102
3 | 2 | 103
4 | 1 | 104
正規化後は、顧客情報も商品情報も一度だけ保存されています。山田商店の住所を変更したいときは、顧客テーブルの1行を修正するだけで済みます。
データを取得するときは、以下のようにSQLで結合します。
SELECT
o.order_id,
c.customer_name,
c.customer_address,
p.product_name,
p.price
FROM
orders o
INNER JOIN
customers c ON o.customer_id = c.customer_id
INNER JOIN
products p ON o.product_id = p.product_id;
実行結果
order_id | customer_name | customer_address | product_name | price
---------+---------------+-----------------------+--------------+-------
1 | 山田商店 | 東京都渋谷区1-2-3 | ノートPC | 80000
2 | 山田商店 | 東京都渋谷区1-2-3 | マウス | 2000
3 | 佐藤電機 | 大阪府大阪市4-5-6 | キーボード | 5000
4 | 山田商店 | 東京都渋谷区1-2-3 | モニター | 30000
結合することで、正規化前と同じ形式でデータを取得できます。しかし、裏側では効率的にデータが保存されているのです。
9. 初心者が正規化で気をつけるポイント
正規化を学び始めた初心者の方に、覚えておいてほしいポイントをまとめます。
必ずしも完全な正規化が正解ではない
教科書では第三正規化まで進めることが推奨されますが、実務では状況に応じて判断します。検索速度が重要な場合は、あえて正規化を緩めることもあります。
最初から完璧を目指さない
最初は正規化の理論を理解し、基本的な設計ができれば十分です。経験を積むことで、より適切な設計ができるようになります。
主キーを必ず設定する
正規化を進める上で、各テーブルに主キーを設定することは非常に重要です。主キーがないと、正規化の判断ができません。
実際に手を動かして学ぶ
理論だけでなく、実際にテーブルを設計して、SQLを書いてみることが大切です。失敗を恐れずに試行錯誤しましょう。
