DB設計 正規化とは?


はじめに

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

当方、実務に入って5ヶ月目のエンジニアです。
業務の中で、DBやテーブル構成についての理解を深める必要があると感じたため、上記書籍を読んで得た知見をまとめています。また、図などは前述の書籍から引用させていただきました。

今回は、その中でもっとも重要であると感じた「正規化」について記述していきます。

正規化とは

「正規化」という言葉自体は、リレーショナルデータベース以外の分野でも使われている。
一般的な意味合いとしては、「あるルールに従った形」に整理することをいう。
ざっくりいうと、正規化とは実際にあるデータを「リレーショナルデータベースに適した状態」に整形していくこと。

正規化によって作られたデータの形を「正規形」という。
正規形とは、保持データの冗長性をなくし、一貫性と効率性を保持するためのデータ形式。

正規形は第1〜5まで段階があるが、通常の業務レベルでは第3正規形までを行えればOK。
今回の記事でも、第3正規形までをまとめています。

第1正規形

リレーショナルデータベースのテーブルは、いずれも既に第1正規形を満たす形となっている。

  • 第1正規形の定義 スカラ値の原則

    「一つのセルの中には一つの値しか含まない」
    スカラ(scalar)は、「単一の」という意味

なぜリレーショナルデータベースではスカラ値のテーブルしか認められないのか?

セルに複数の値を許せば、主キーが各列の値を一意に決定できないから。
リレーショナルデータベースでは、主キーが各列の値を一意に決定できなければいけないというルールがある。

たとえば、以下のようなテーブルの場合

table名「扶養者」

社員ID 社員名
000A 加藤 達夫
信二
000B 藤本
001F 三島
陽子
清美

「子」列のセル内に複数のデータが入ってしまっているため、第一正規形の条件を満たしていないということになる。
この状態を解決する方法として、以下の2通りのパターンが挙げられます。

① 列を増やす(列持ち・横持ち)パターン

社員ID 社員名 子1 子2 子3
000A 加藤 達夫 信二
000B 藤本    
001F 三島 陽子 清美

② 1行ずつ持つ(行持ち・縦持ち)パターン

社員ID 社員名
000A 加藤 達夫
000A 加藤  信二 
000B 藤本
001F 三島
001F 三島 陽子
001F 三島 清美

様々な理由から、この2つのパターンは後者の方が良いとされている。
しかし後者を選択しても、この状態では主キーを決められないという問題が発生する。

原因
・1レコードの列をすべて特定しようとすると、{社員ID, 社員名, 子} とすべてを指定する必要がある。
・主キーは一部であってもNULLを含んではならないが、子のいないレコードが存在するため矛盾が生じる。

そのため、上記の②のパターンは更にもうひと手間かける必要があります。

table名「社員」

社員ID 社員名
000A 加藤
000B 藤本
001F 三島

table名「扶養者」

社員ID 社員名
000A 達夫
000B 信二
001F
000B 陽子
001F 清美

以上のようにテーブルを分けることにより、先程の問題は解決し、なおかつテーブルの意味やレコードの単位がすぐ理解できるようになりました。

関数従属性

リレーショナルデータベースではスカラ値のテーブルしか認められない。
それは前述した「セルに複数の値を許せば、主キーが各列の値を一意に決定できない」という理由からである。
スカラ値だけのテーブルであれば、主キーは各列を一意に決めることができるようになる。

上記の内容を理解する鍵となる概念として、「関数従属性」というものがあります。

Y = f(X)
こちらの関数は「入力(X)に対して出力(Y)を一つに決めるための箱」である。

リレーショナルデータベースでは、このXとYの関係を  {X} → {Y} というように表現する。
これは「X列の値を決めれば、Y列の値が一つに決まる」ということを示している。
つまり、先程の関数と同じことを示しているということでもある。

正規化とは、テーブルすべての列が関数従属性を満たすように整理していくこと。

第2正規形

table名「社員」

会社コード 会社名 社員ID 社員名 年齢 部署コード 部署名
C0001 A商事 000A 加藤 40 D01 開発
C0001 A商事 000B 藤本 32 D02 人事
C0001 A商事 001F 三島 50 D03 営業
C0002 B科学 000A 斎藤 47 D03 営業
C0002 B科学 009F 田島 25 D01 開発
C0002 B科学 010A 渋谷 33 D04 総務

上記のような構成のテーブルが存在しているとする。
このテーブルはそれぞれのセルがスカラ値によって構成されているため、第一正規形であるといえますが、まだまだ問題があります。

このテーブルの主キーは{ 会社コード, 社員ID }です。そのため、他のすべての列はこのキーに従属しています。
しかし「会社名」列だけは主キーの一部である「会社コード」に従属している。
つまり、以下のような関数従属性があるといえます。

{ 会社コード } → { 会社名 }

このように、「主キーの一部の列」に対して「従属する列」がある場合、この関係を部分関数従属という。
また、これに対して「主キーを構成するすべての列」に従属性がある場合を完全関数従属と呼びます。

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

以下は、第2正規形にした後の「社員」テーブルと「会社」テーブルです。
これによって、いずれのテーブルも主キーに完全関数従属することになりました。

table名「社員」

会社コード 社員ID 社員名 年齢 部署コード 部署名
C0001 000A 加藤 40 D01 開発
C0001 000B 藤本 32 D02 人事
C0001 001F 三島 50 D03 営業
C0002 000A 斎藤 47 D03 営業
C0002 009F 田島 25 D01 開発
C0002 010A 渋谷 33 D04 総務

table名「会社」

会社コード 会社名
C0001 A商事
C0002 B科学

第2正規形でないと何が悪いのか

  • 第2正規形にする前のテーブルでは、社員の情報が不明の会社があった場合、その会社をテーブルに登録することができない
  • 運用を誤ると会社コードと会社名の対応がレコードによってマチマチになってしまう危険がある

    (例){ C0001, A商事 } というレコードの他に、{ C0001, A商社 }というデータが登録されてしまう可能性がある。

第2正規形とは、異なるレベルの実体(エンティティ)を分割する作業

このような観点から見ると、第二正規化というのは「会社」と「社員」というそれぞれに異なるレベルの実体(エンティティ)を、きちんとテーブルとしても分離してやる作業。

現実の世界では会社があって、その下に社員がある
このように階層構造となっているものや概念を分離し、リレーショナルデータベースの世界で表現するといったイメージです。

無損失分解

第二正規化は可逆的な操作である。
テーブルを分割することによって、元のテーブルに戻せなくなってしまうのでは?
という疑問はもっともですが、「第2正規化は必ず正規化する前の状態にテーブルを戻すことができる」ようになっています。

このような「情報を完全に保持したまま」テーブルを分割する操作のことを、無損失分解といいます。

正規化されたテーブルから非正規化された状態のテーブルに戻すには?
結合(JOIN)を行う

第3正規形

先ほど第2正規化したテーブルをもう一度見ていると、ある問題が浮上します。

table名「社員」

会社コード 社員ID 社員名 年齢 部署コード 部署名
C0001 000A 加藤 40 D01 開発
C0001 000B 藤本 32 D02 人事
C0001 001F 三島 50 D03 営業
C0002 000A 斎藤 47 D03 営業
C0002 009F 田島 25 D01 開発
C0002 010A 渋谷 33 D04 総務

table名「会社」

会社コード 会社名
C0001 A商事
C0002 B科学

「部署コード」と「部署名」について注目してみると、「社員」テーブルからは開発、人事、営業という3つの部署が存在している事がわかる。
しかし、A商事の部署がこの3つだけである保証はない。

  • 「広報」という部署は存在しているが、たまたま社員が一人もいないだけかもしれない
  • 新たな部署が追加される場合、社員がいなければ登録ができない

このような問題が発生するのは、「社員」テーブルの中にまだ隠れた関数従属性が残っているため。
現在でいうと

{ 部署コード } → { 部署名 }

という関数従属性が存在していることが見えてくる。

一方で、

{ 会社コード, 社員ID } → { 部署コード }

という関数従属も存在している。全体として考えると

{ 会社コード, 社員ID } → { 部署コード } → { 部署名 }

という2段階の関数従属が存在している。
このように、テーブル内部に存在する段階的な従属関係のことを、推移的関数従属と呼びます。

推移的関数従属の解消

これを解消するためには、第2正規化のときと同じようにテーブル分割を行い、それぞれの関数従属関係を独立させる。

table名「社員」

会社コード 社員ID 社員名 年齢 部署コード
C0001 000A 加藤 40 D01
C0001 000B 藤本 32 D02
C0001 001F 三島 50 D03
C0002 000A 斎藤 47 D03
C0002 009F 田島 25 D01
C0002 010A 渋谷 33 D04

table名「会社」

会社コード 会社名
C0001 A商事
C0002 B科学

table名「部署」

部署コード 部署名
D01 開発
D02 人事
D03 営業

このように、新しく「部署」テーブルを作成することで、非キー列はキー列にのみ従属するようになり、推移的関数従属も解消された。
また、一人も存在しない部署を追加できない、という問題も「部署」テーブルが独立したことによって解消されている。
そして、このテーブル分割も第2正規化のときと同じ「無損失分解」なので、SQLでJOIN句を用いることによって分割前の状態に戻すことも可能です。

まとめ

以降、

  • ボイスーコッド正規形
  • 第4正規形
  • 第5正規形

と続いていきますが、実際の業務ではほぼこの第3正規形でテーブル設計を行えればひとまずは問題ないとされています。
テーブル設計を学ぶことで、設計者がどのような意図を持ってこの構成にしたのか、といったことが以前よりはっきりとわかるようになりました。
冒頭にも紹介しましたが、達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへは非常に読みやすく、わかりやすく解説されており非常に参考になりました。
また学習が深まって来たら、正規化による功罪(パフォーマンス)等の問題についてもまとめていければと思います。