正規化よりも実装のしやすさを優先させる

はじめに

本記事では見出しにある通り、データベースの正規化について述べたいと思います。

みなさんは正規化をすることができるのに、あえて正規化をしていないケースを見かけたことがあるでしょうか。

DB設計をする時は必ず行わなければならないと思われがちな正規化ですが、現在私が携わっている案件には正規化がされていないテーブルがあります。「なぜ正規化がされていないのか」を DB設計をされた方に質問させていただいたところ、改めて正規化のメリットとデメリットを学ぶ大変よい機会になったので、正規化について振り返りながら、「正規化をあえてしないこととは、どういうことなのか」を最終的にお伝えできればと思います。

目次

• 正規化とは何か
• 正規化の手順
• 正規化のメリットとデメリット
• あえて正規化を行わない具体例
• まとめ

正規化とは何か

正規化とは「データの整合性をとるためにテーブルを複数に分割していく作業」です。 言葉による定義だけではわかりづらい部分があると思うので、正規化の手順を紹介します。

正規化の手順

正規化には細かく分けると第1正規化から第5正規化まで存在します。 また、それぞれの段階で正規化されたデータ構造のことを「正規形」と呼びます。 ここでは正規化の中でも重要とされる第1正規形から第3正規形までについて具体的な正規形の例を交えながら紹介していきます。

第1正規形

第1正規形とは「1つのフィールドには1つの値しか入らない」というルールが守られた形のことを指します。 以下は正規化を行う前のテーブル例です。「所属サークル」のフィールドに2つ以上の値が入っているのでこちらのテーブルは正規化する必要があります。

社員テーブル

社員ID	社員名	所属サークル
1	山田太郎	サッカー、軽音
2	菊池太郎	野球
3	川上太郎	バレーボール

フィールド内の値を1つにするために考えられる方法として、行に分ける方法と列に分ける方法がありますが、どちらも問題があります。 最初に行で分ける方法では「社員ID」と「所属サークル」のフィールドがセットで主キーになります。 しかし、主キーにNULLは禁止なので「所属サークル」がないデータが生まれてしまう可能性があるこの方法には「主キーが定められない」という問題があります。

行で分ける方法

社員ID	社員名	所属サークル
1	山田太郎	サッカー
1	山田太郎	軽音
2	菊池太郎	野球
3	川上太郎	バレーボール

次に列で分ける方法では「社員ID」が主キーになるので一見すると問題なさそうに見えますが、ここにも問題があります。 それは「『所属サークル』の列を幾つ用意すればいいのか？」という点と大量にNULLの値を生んでしまうという点です。 どのサークルにも所属していない社員は「所属サークル」の列の分だけNULLの値が入ることになります。

列で分ける方法

社員ID	社員名	所属サークル1	所属サークル2
1	山田太郎	サッカー	軽音
2	菊池太郎	野球
3	川上太郎	バレーボール

ではどうすればいいのかというと、以下のようにテーブルを社員と所属するサークルの2つに分けることで上で述べた問題を回避しつつ、第1正規形にすることができます。

社員テーブル

社員ID	社員名
1	山田太郎
2	菊池太郎
3	川上太郎

サークルテーブル

サークルID	サークル名
1	サッカー
2	野球
3	バレーボール
4	軽音

第2正規形

第2正規形とは部分関数従属が解消されていて、完全関数従属のみのテーブルになっている形のことを指します。

部分的関数従属とは「主キー列の一部によって値が決定する関係」のことであり、完全関数従属とは「全ての主キー列を使って値が決定する関係」のことです。

例えば以下のような「社員ID」と「所属サークルID」が主キーのテーブルでは、「社員名」は「社員ID」によって決定しますが、「所属サークル名」は「所属サークルID」によって決定します。 この関係を部分的関数従属と言います。

社員と所属サークルテーブル

社員ID	社員名	所属サークルID	所属サークル名	カテゴリID	カテゴリ名
1	山田太郎	1	サッカー	1	スポーツ
2	菊池太郎	2	野球	1	スポーツ
3	川上太郎	3	バレーボール	1	スポーツ
4	田中太郎	4	軽音	2	文化

部分的関数従属を解消させるには以下のようにテーブルを3つに分けます。

社員テーブル

社員ID	社員名
1	山田太郎
2	菊池太郎
3	川上太郎
4	田中太郎

サークルテーブル

サークルID	サークル名	カテゴリID	カテゴリ名
1	サッカー	1	スポーツ
2	野球	1	スポーツ
3	バレーボール	1	スポーツ
4	軽音	2	文化

社員の所属サークルテーブル

社員ID	所属サークルID
1	1
2	2
3	3

部分的関数従属を解消することで、「社員ID」が「4」、「社員名」が「田中太郎」のような「所属サークル」に入っていない社員をテーブルに反映させることができるようになりました。

第3正規形

第3正規形とは推移的関数従属が解消されている形のことを指します。推移的関数従属とは「Aが決まればBが決まり、Bが決まればCが決まる関係」のことです。

例えば先ほど正規化したサークルテーブルでは「サークルID」で「カテゴリID」の値が決まり、「カテゴリID」が決まると「カテゴリ名」も決まります。 この関係性を推移的関数従属と言います。

サークルテーブル

サークルID	サークル名	カテゴリID	カテゴリ名
1	サッカー	1	スポーツ
2	野球	1	スポーツ
3	バレーボール	1	スポーツ
4	軽音	2	文化

なので、この推移的関数従属を解消すると、テーブルは以下のようになります。

サークルテーブル

サークルID	サークル名
1	サッカー
2	野球
3	バレーボール
4	軽音

カテゴリテーブル

カテゴリID	カテゴリ名
1	スポーツ
2	文化

第2正規形と第3正規形は似ていますが、第2正規形が「主キー」に着目して従属性を解消するのに対し、第3正規形は「主キー以外の列」に着目して従属性を解消するという違いがあります。 では、正規化をすることでどのようなメリットとデメリットがあるのでしょうか。

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

正規化のメリット

まず、1つ目はデータの整合性をとることができる点です。データの整合性をとるとは、データに矛盾がないようにすることです。例えば、

サークルテーブル

サークルID	サークル名	カテゴリ名
1	サッカー	スポーツ

のようなデータがあるとします。この時に「軽音を登録するはずだったのに、サッカーを登録してしまった」という間違いがわかった場合、どのようなクエリを記述すればよいでしょうか。 もし

UPDATE サークルテーブル SET サークル名 = '軽音' WHERE サークルID = 1;

としてしまった場合は、間違いです。なぜなら、

サークルテーブル

サークルID	サークル名	カテゴリ名
1	軽音	スポーツ

確かに「サークル名」の更新は行われますが、「カテゴリ名」の更新が行われておりません。そのため、「軽音サークルのカテゴリはスポーツ」と矛盾した状態になっていることがわかります。 ならば、「カテゴリ名」も「サークル名」と一緒に更新すればよいと思われるかもしれません。確かに

UPDATE サークルテーブル SET サークル名 = '軽音',カテゴリ名 = '文化' WHERE サークルID = 1;

というクエリで更新すれば、この場合データの整合性が失われることはないです。 しかし、この例で用いたような単純なテーブルではなく、カラムがもっと多いテーブルである場合を考えてみてください。あるカラムの値に依存している他のカラム (軽音ならカテゴリが文化と決まる)が多い場合、そのカラムを全て漏れなく更新しなければなりません。

このようなテーブルの場合は人間誰しもがミスをする可能性があることを考えると、矛盾したデータを生じさせてしまう可能性があるため、 データの管理に適切なテーブルではありません。そのためさまざまなデータを1つのテーブルに集約せず、データの整合性を維持するために分割することでデータの管理がしやすいようにしなければなりません。

2つ目はデータの変更が柔軟にできる点です。例えば、

サークル入会履歴テーブル

社員ID	サークル名	入会日時
1	サッカー	2021-09-30
2	野球	2021-09-30
3	軽音	2021-10-01

このようにサークル名が直接登録されてしまっているテーブルを用いている時に、「『サッカー』を来月から『アップデートサッカー』に改名したい」となった場合はどうなるのでしょうか。

UPDATE サークル入会履歴テーブル SET サークル名 = 'アップデートサッカー' WHERE サークル名 = 'サッカー';

で更新できますが、これはデータの件数が多ければ多いほどすべて更新するのに時間がかかり、データの管理がしづらく大変です。そこで、

サークルテーブル

サークルID	サークル名
1	サッカー
2	野球
3	軽音

サークル入会履歴テーブル

社員ID	サークルID	入会日時
1	1	2021-09-30
2	2	2021-09-30
3	3	2021-10-01

と正規化をすれば、サークル名の改名があったとしても

UPDATE サークルテーブル SET サークル名 = 'アップデートサッカー' WHERE サークルID = 1;

というクエリ1行だけ更新すればよいので、テーブルを分けなかった場合と分けた場合のどちらがデータの管理がしやすいか一目瞭然です。 このように正規化はデータの変更に柔軟に対応できるわけです。

3つ目は「どのようなデータがあるか管理することができる」点です。

正規化前の状態では、サークル入会履歴テーブルにどんなサークルが登録される可能性があるのか、実際のレコードからだけでは把握する術はありません。

しかし、正規化後のテーブルの場合、サークル入会履歴テーブルに登録されうるサークルは、サークルテーブルに登録されているサークルであることが、容易く理解できます。

また、正規化することで想定しない値が入ることを防ぐことができます。なぜならサークルテーブルにないサークルをサークル入会履歴テーブルで使うことはできないからです。 以上の3つが正規化のメリットです。

正規化のデメリット

正規化のデメリットは正規化前と正規化後のテーブルを比較すればわかります。

そして、この正規化のデメリットを理解することこそが正規化をあえてしないという選択につながります。

サークル入会履歴テーブルの正規化前と正規化後を見ていただければわかる通り、正規化後にサークル入会履歴テーブルの「入会日時」に加えて、サークルテーブルの「サークル名」も取得したい場合、

SELECT サークル入会履歴テーブル.入会日時,サークルテーブル.サークル名
FROM サークル履歴テーブル 
inner join サークルテーブル サークル入会履歴テーブル.サークルID = サークルテーブル.サークルID;

とJOINで結合してデータを取得しなければなりません。また、

カテゴリテーブル

カテゴリID	カテゴリ名
1	スポーツ
2	文化

サークルテーブル

サークルID	サークル名	カテゴリID
1	サッカー	1
2	野球	1
3	軽音	2

のように、サークル入会履歴テーブルが参照しているサークルテーブルがさらにカテゴリテーブルを参照しているという場合も考えられます。

このようにリレーションが複雑になってくると、JOINするテーブルが増えたり、クエリが増えたりするので、 どうしてもパフォーマンスが落ちてしまう可能性があります。

まさにこれが正規化のデメリットであり、テーブルの数が必然的に多くなることでパフォーマンスが低下する可能性を生むということです。 総じて正規化をするということは、「パフォーマンスが落ちてしまうことよりも、データの整合性の維持やデータの変更に柔軟に対応できるようにすることを優先させる作業」と言えます。

あえて正規化を行わない具体例

では、あえて正規化をしないということはどういうことなのでしょうか。 ただ、プログラムの修正よりもデータ構造の修正は容易にできないため、特別な理由がない限りは正規化は行った方が良いと言えます。

その前提であえて正規化を行わない例を紹介したいと思います。 例えば外部に送るデータを保存しておくためだけの以下のようなテーブルがあるとします。

社員テーブル

社員ID	社員名	所属サークル
1	山田太郎	サッカー,軽音
2	菊池太郎
3	川上太郎	野球

上記の社員テーブルでは「所属サークル」の値がカンマ区切りで入っており、「1つのフィールドには1つの値しか入らない」というルールを破っているので、本来であれば正規化の手順で紹介した第1正規形に正規化するところです。 しかし、

• 外部にしか送らない
• データの更新等も行われない
• リレーション先のテーブルも存在しない

などの理由から正規化が行われないテーブルもあります。 まさにこのようなテーブルが現在私が携わっている案件にあります。正規化をするメリットが薄く、正規化を行わないことで素早くデータを取得できる(パフォーマンスの向上)ため、このような設計になっているのです。

実際に社内の勉強会のために正規化前と後でどれくらいパフォーマンスが違うのかを検証してみました。

場面

「備品がモニターである貸出日時を取得したい」

条件

1. 貸出履歴テーブルの正規化前と正規化後のデータ数はともに3000件
2. 貸出履歴テーブルの正規化前と正規化後のそれぞれの備品データの数は同じである

正規化前

貸出履歴(lending_histories)

No	フィールド名	概要
1	lending_history_id	貸出履歴ID(主キー)
2	created_at	登録日時
3	employee_name	社員名
4	item_name	備品名(モニター,マウス,キーボードが1つ以上カンマ区切りで入る)

正規化後

貸出履歴(lending_histories)

No	フィールド名	概要
1	lending_history_id	貸出履歴ID(主キー)
2	created_at	登録日時
3	employee_id	社員ID(社員テーブルの外部キー)

社員(employees)

No	フィールド名	概要
1	employee_id	社員ID(主キー)
2	name 名前

貸出内訳(uchiwakes)

No	フィールド名	概要
1	lending_history_id	貸出履歴テーブルの外部キー
2	item_id	備品テーブルの外部キー

備品(items)

No	フィールド名	概要
1	item_id	備品ID
2	label	名前

正規化前のクエリ:

select created_at from lending_histories where item_name like '%mouse%';

正規化後のクエリ:

select lending_histories.created_at from lending_histories
join uchiwakes on lending_histories.lending_history_id = uchiwakes.lending_history_id
where uchiwakes.item_id = 2;

検証結果は、正規化前より正規化後はすべてのデータを取得するのに約3倍(正規化前: 2.329s,正規化後: 7.310s)かかってしまいました。3000件の検証でこれほど異なるのであれば、実際の運用ではデータの件数を考えた時、さらにその差が大きくなることが予想できると言えるでしょう。

まとめ

いかがだったでしょうか。DBの正規化は非常に重要であることがわかる一方で、あえて正規化をしない選択肢がなぜあるのかがお分かりいただけたと思います。

正規化を優先すれば、データの整合性を取ることができるといったメリットがある一方で、パフォーマンスが落ちてしまう可能性がある。 正規化をしなければ、パフォーマンスが向上するが、データの整合性がとれないという正規化とパフォーマンスはまさにトレードオフの関係にあるのです。

そして、DB設計において大事なことは「正規化をすることではなく、対象のテーブルをどのように用いるか」ということです。なぜならそれがはっきりすれば、正規化するべきか否かがわかるからです。 設計者の立場になった時は、今回の経験を生かし、DB設計をする上で何が大切なのかを理解した上で柔軟に設計したいと思います。