正規化という手法を理解するには準備としていくつかの知識を整理しておく必要があり、また、若干抽象的な内容になりますので、その利点を含めて理解するまでにはそれなりに時間がかかります。
そこで、詳しい定義よりも先に、ある程度は理屈や利点を示しつつ正規化の考え方に沿って実際のテーブルを変更していく例を見ていきたいと思います。
また、正規化に関連する概念と、正規化に対する疑問点のいくつかについて触れていきます。
販売記録の正規化
実例として、販売記録のデータを正規化していく例を見ていきます。商品販売は多数の「商品」や「取引先」という実体があるものどうしを「販売」という無形の行為で結びつけることであり、一定の複雑さがあることから、Accessのようなデータベースソフトの利点を生かしやすい分野とされています。いろいろなデータベースの参考書を見ても、販売管理というのが定番の題材となっていることがわかります。
正規化以前
以下に示すテーブルの例では、1件の販売ごとに1つの行（レコード）を追加しています。「販売ID」列を主キーとして定め、1件ごとに番号を付与し販売を識別しています。なお、このテーブルは（横着なことに）商品一覧や取引先の一覧を兼ねており、つまり販売業務に関するすべての情報をこのテーブル1つに記録し運用しているものと考えてください。また、単純化のため、商品の名称や単価の変更、取引先の名称や所在地の変更はない（考慮しない）ものと考えてください。
| 販売ID | 取引先コード | 取引先名 | 取引先所在地 | 商品コード | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 山田商事 | ○○区△△ | 1,2 | 牛乳,味噌 | 120,200 | 5000,8000 |
| 2 | 2 | 斉藤商店 | ◇◇町☆☆ | 2,3 | 味噌,バター | 200,190 | 16000,9000 |
| 3 | 1 | 山田商事 | ○○区△△ | 4 | りんごジュース | 80 | 2000 |
| 4 | 3 | 竹田ストア | ＊＊市□□ | 1,4 | 牛乳,りんごジュース | 120,80 | 1900,7000 |
さて、確かに1つの行に1件の販売に関する情報すべてが記録されていて、シンプルにまとめられた記録にはなっています。ただし、1件の販売に複数の商品が含まれる場合でも、カンマ区切りによって詰め込まれているのがわかります。Excelを使ったことがあればわかると思いますが、このようなテーブルでは入力規則を定めて値が正しく入力されているかどうかをチェックすることが難しく、販売額を計算したり商品別の販売数量を計算することも容易でないため、活用が難しい状態にあることがわかります。もちろんこうした問題はAccessを使ったところで全く同じであり、好ましくない状態であることに変わりありません。
| 販売ID | 取引先コード | 取引先名 | 取引先所在地 | 商品コード1 | 商品名1 | 単価1 | 数量1 | 商品コード2 | 商品名2 | 単価2 | 数量2 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 山田商事 | ○○区△△ | 1 | 牛乳 | 120 | 5000 | 2 | 味噌 | 200 | 8000 |
| 2 | 2 | 斉藤商店 | ◇◇町☆☆ | 2 | 味噌 | 200 | 16000 | 3 | バター | 190 | 9000 |
| 3 | 1 | 山田商事 | ○○区△△ | 4 | りんごジュース | 80 | 2000 | ||||
| 4 | 3 | 竹田ストア | ＊＊市□□ | 1 | 牛乳 | 120 | 1900 | 4 | りんごジュース | 80 | 7000 |
こちらの例では、販売の内訳である商品データにカンマ区切りを行わず、1種類の商品ごとに列を設け、すべて単一の値で記録するようにしています。列数は増え、見た目は決してスマートではありませんが、先ほどの例と比べると扱いやすくなっており、商品コードの入力チェックや販売額の計算に関する困難は軽減されています。しかしこのようなテーブルでも、以下のような問題が残ります。
- 一度に販売する商品の種類が多くなると（具体的には3種以上の商品が含まれると）、列を追加しなければならない。
この問題については、列を増やして対応するとしても商品の種類と同じ数だけ列を増やさなければ列が不足する可能性が残りますし、それだけの列を用意しても、商品の種類そのものが増えるかもしれません。また、売上金額や数量を算出する際に多数の列を参照する必要があり、計算の手間が増えます。
1件の販売なのですから1つの行に収めたいところではありますが、そこにこだわる限り上記の問題は解決しません。結局のところ、いくつもある商品情報を無理やり1つの行に詰め込んでしまおうとすることには、そもそも無理があるのです。
第一段階（第1正規形）
そこで、1つの行に収められていた情報を複数の行に分けることとします。具体的には以下のようになります。
| 販売ID | 取引先コード | 取引先名 | 取引先所在地 | 商品コード | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 山田商事 | ○○区△△ | 1 | 牛乳 | 120 | 5000 |
| 1 | 1 | 山田商事 | ○○区△△ | 2 | 味噌 | 200 | 8000 |
| 2 | 2 | 斉藤商店 | ◇◇町☆☆ | 2 | 味噌 | 200 | 16000 |
| 2 | 2 | 斉藤商店 | ◇◇町☆☆ | 3 | バター | 190 | 9000 |
| 3 | 1 | 山田商事 | ○○区△△ | 4 | りんごジュース | 80 | 2000 |
| 4 | 3 | 竹田ストア | ＊＊市□□ | 1 | 牛乳 | 120 | 1900 |
| 4 | 3 | 竹田ストア | ＊＊市□□ | 4 | りんごジュース | 80 | 7000 |
1件の販売について複数の行にまたがって記録するようにしました。販売IDによって、どこからどこまでが1件の販売なのかを把握できるようになっています（販売IDの値が同じであれば、同じ1件の販売に関する行であるということです）。1件の販売で3種類の商品を販売したとすれば3行を、10種類の商品を販売したとすれば10行を使って記録すればよく、1件の販売にどれほど多くの種類の商品が含まれるとしても、列の追加というテーブルの設定変更を行う必要はありません。
なお、複数の行にわたって同じ販売IDの値が現れますので、販売IDだけでは1つの行を特定することはできなくなっていますが、このテーブルは1件の販売に含まれる商品の種類が1つ増えるごとに行も1つ増えるつくりになっていることから、販売IDと商品コードがわかれば行をただ1つに特定できることがわかります。
さて、しかしこのテーブルにも問題がないというわけではありません。まず、取引先の情報に着目すると、
- 販売があるたびに、しかも販売する商品の種類の数だけ販売先コードのほか取引先名と取引先所在地を繰り返し記録しなければならず、入力ミスの原因となりやすい。
ということが挙げられます。1回の販売で1回ならともかく、商品の種類の数だけ取引先情報を記録していかなければならないというのは確かに面倒なことであります。かといって元のテーブルに戻るのでは意味がありません。
さて、この問題を解決するにはどうしたらよいのでしょうか。
第二段階（第2正規形）
上記の問題を解決する方法、それはテーブルを複数に分けることです。そしてその結果は次のようになります。
| 販売ID | 取引先コード | 取引先名 | 取引先所在地 |
|---|---|---|---|
| 1 | 1 | 山田商事 | ○○区△△ |
| 2 | 2 | 斉藤商店 | ◇◇町☆☆ |
| 3 | 1 | 山田商事 | ○○区△△ |
| 4 | 3 | 竹田ストア | ＊＊市□□ |
| 販売ID | 商品コード | 商品名 | 単価 | 数量 |
|---|---|---|---|---|
| 1 | 1 | 牛乳 | 120 | 5000 |
| 1 | 2 | 味噌 | 200 | 8000 |
| 2 | 2 | 味噌 | 200 | 16000 |
| 2 | 3 | バター | 190 | 9000 |
| 3 | 4 | りんごジュース | 80 | 2000 |
| 4 | 1 | 牛乳 | 120 | 1900 |
| 4 | 4 | りんごジュース | 80 | 7000 |
販売テーブルにおいては、1件の販売を1つの行として記録します。1件の販売において取引先は1つしかありませんので、取引先に関する情報もこちらに記録するのが自然です。これにより上記（b.販売があるたびに、しかも販売する商品の種類の数だけ販売先コードと取引先名と取引先所在地を繰り返し記録しなければならない）の問題がひとまず緩和されます。また、販売明細テーブルにおいては、（分割前のテーブルと同じように）1件の販売ごと、商品の種類ごとに1行を使って記録します。販売テーブルの主キーは販売ID、販売明細テーブルの主キーは販売IDと商品コードの複合キーとなります。
この2つのテーブルの内容をつき合わせることにより分割前のテーブルの情報が得られること、つまり分割前の情報が保たれていることを確認してください。
さて、取引先の情報の記録に関する問題は改善しましたが、一方で、商品の情報に着目すると、次のような問題があることがわかります。
- 同じ商品を販売するたびに商品コードのほか商品名、単価を繰り返し記録しなければならず、入力ミスの原因となりやすい。
- 新たな販売がない限り新しい行を増やせないため、販売実績がない商品についてはその名称や単価といった情報を記録することができない。
- 販売のキャンセルなどにより、ある商品の販売に関する行がすべて削除されると、その商品の情報（商品名、単価）が一緒に失われてしまう。
これらの問題の原因は、商品の情報の記録が販売の実績に依存していること、そして、その販売のたびに商品情報を繰り返し記録しなければならないところにあります。
しかし、上記のような問題も、テーブルをさらに分割することにより解決していくことができます。具体的には次のようになります。
| 販売ID | 取引先コード | 取引先名 | 取引先所在地 |
|---|---|---|---|
| 1 | 1 | 山田商事 | ○○区△△ |
| 2 | 2 | 斉藤商店 | ◇◇町☆☆ |
| 3 | 1 | 山田商事 | ○○区△△ |
| 4 | 3 | 竹田ストア | ＊＊市□□ |
| 販売ID | 商品コード | 数量 |
|---|---|---|
| 1 | 1 | 5000 |
| 1 | 2 | 8000 |
| 2 | 2 | 16000 |
| 2 | 3 | 9000 |
| 3 | 4 | 2000 |
| 4 | 1 | 1900 |
| 4 | 4 | 7000 |
| 商品コード | 商品名 | 単価 |
|---|---|---|
| 1 | 牛乳 | 120 |
| 2 | 味噌 | 200 |
| 3 | バター | 190 |
| 4 | りんごジュース | 80 |
販売テーブルについては変更はありませんが、販売明細テーブルをさらに分割する形で、新たに商品テーブルを設けました。商品の種類ごとに記録すべき情報、つまり商品名と単価はこのテーブルに記録することとなります。なお、数量は商品自体の特性ではなく、毎回の販売の内訳ごとに異なりますので、販売明細テーブルに置いたままとするのが自然です。
これにより、商品に関する情報を販売の都度記録する必要がなくなり、上記のb〜dの問題がすべて解決します。販売に関する行がすべて削除されても、商品テーブルに記録されている商品の情報が消えることはありませんし、まだ販売実績がない商品でも事前に情報を記録しておくことができます。
さて、商品の情報についての問題を解決しましたが、販売テーブルに記録されている取引先の情報に再度着目すると、上記c〜eと同じように、やはり以下のような問題が残っていることがわかります。
- 販売のたびに販売先コードのほか取引先名と取引先を記録しなければならず、入力ミスの原因となりやすい。
- 新たな販売がない限り新しい行を増やせないため、見込み客（取引が発生しそうな相手）が表れても、その情報を記録することができない。
- 販売のキャンセルなどにより、ある取引先に関する行がすべて削除されると、その取引先の情報（取引先名、所在地）が一緒に失われてしまう。
さて、これらの問題についてはどう解決すべきでしょうか。
第三段階（第3正規形）
上記の問題も、やはりテーブルを分割することで解決することができます。結果は次のようになります。
| 販売ID | 取引先コード |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 3 |
| 取引先コード | 取引先名 | 取引先所在地 |
|---|---|---|
| 1 | 山田商事 | ○○区△△ |
| 2 | 斉藤商店 | ◇◇町☆☆ |
| 3 | 竹田ストア | ＊＊市□□ |
| 販売ID | 商品コード | 数量 |
|---|---|---|
| 1 | 1 | 5000 |
| 1 | 2 | 8000 |
| 2 | 2 | 16000 |
| 2 | 3 | 9000 |
| 3 | 4 | 2000 |
| 4 | 1 | 1900 |
| 4 | 4 | 7000 |
| 商品コード | 商品名 | 単価 |
|---|---|---|
| 1 | 牛乳 | 120 |
| 2 | 味噌 | 200 |
| 3 | バター | 190 |
| 4 | りんごジュース | 80 |
販売テーブルに記録されていた取引先の情報を、取引先テーブルとして独立させました。これによって、取引先に関する情報を販売のたびに記録する必要がなくなり、上記のf〜hの問題がすべて解決します。販売に関するレコードが削除されても、取引先テーブルに記録されている取引先の情報は消えることはありませんし、販売実績がない取引先でも事前に情報を記録しておくことができます。
正規化の実例についてはひとまずここまでとなります。
一事実一箇所
販売記録を例として正規化の実例をみてきましたが、その結果として、ある事実（たとえばある取引先の名称が「山田商事」であること）ことは1箇所にしか記録されていない状態となっていることがわかります。正規化以前の段階では、同じ商品の名称が何度も繰り返し記録されていましたし、同じ取引先の名称も繰り返し記録されていました。一方、正規化後には、ある商品の名称は商品テーブル上の1箇所にしか記録されていませんし、ある取引先の名称も取引先テーブル上の1箇所にしか記録されていません。この状態は、「一事実一箇所」と表現されます。
データベースにおいてデータの正確さを保つためには、ある事実を繰り返し何度も記録し、矛盾があったらその多数の記録を分析して（例えば多数決で）判断するのが正しいのでしょうか。それとも重複を排除し、ただ1箇所に記録しておくのが正しいのでしょうか。正規化の考え方に沿う限り、その答えは後者であり、原則としてある事実は1箇所にのみ記録し管理していくべきであることが示されているのです。
実体と関連付け
正規化以前のテーブルが雑多な情報をごちゃまぜにしたものだったのに対し、正規化されたテーブルでは取引先を記録する取引先テーブル、商品を記録する商品テーブル、といったようにテーブルが独立していきます。これによりそれぞれのテーブルの位置づけがハッキリしたものとなり、データの利用性が高まっていることがわかるものと思います。
もう少し詳しく見ると、取引先テーブルと商品テーブルはそれぞれ取引先と商品という、実体のあるものを記録しており、それ自体で完結したテーブルとなっています。一方で、販売テーブル及び販売明細テーブルは全体として取引先と商品の関連づけを記録しており、取引先テーブルや商品テーブルの存在があってはじめて成り立つテーブルであることがわかります。
このように、正規化によって実体を表すテーブル、関連付けを表すテーブルというようなテーブルの性格付けが明確になっていきます。これは後にリレーションシップの設定を理解するにあたって重要な考え方となります。
実例の再検討
上記の正規化の実例は典型的なものですが、その過程も結果も決して単純なものとはいえません。正規化後においてもデータ記録上の問題をいくつか解決したに過ぎず、特に正規化の考え方や実例に初めて触れる方にとっては疑問点も多く感じられることと思います。
そのいくつかを整理するとともに、基本的な考え方を示します。一部やや込み入った説明になりますが、ピンと来ない場合でも余り深入りせず先に進んでください。
実用性がどう確保されるのか
正規化を進めると必然的にテーブルの数は増え、人間の目でデータの内容を直感的に把握するのが難しくなります。上記の正規化以前の状態ですと1つのテーブルの1つの行を見れば販売の内容がつかめましたが、正規化後になると4つのテーブルを参照し、それらを総合しないと内容が把握できません。
人間の目でみてどうかというだけでなく、これらのテーブルをもとに請求書を作成したり、商品別あるいは取引先別の売り上げ集計を行ったり、という作業を行うのも大変そうです。実際にそれらをどう進めればいいのでしょうか。
一つの答えとして、これらのテーブルに基づき、用途に応じてクエリという新たな表を導き出して利用することができます。もし必要であれば次のようなクエリ（販売額という列が加わっているだけで最初のテーブルとほぼ同じ内容です）を導いて、この新たな表を差し込み印刷の要領で利用すれば（実際にはレポート機能を使うこととなります）、例えば請求書を作成することもできます。
| 販売ID | 取引先コード | 取引先名 | 取引先所在地 | 商品コード | 商品名 | 単価 | 数量 | 販売額 |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 山田商事 | ○○区△△ | 1 | 牛乳 | 120 | 5000 | 600000 |
| 1 | 1 | 山田商事 | ○○区△△ | 2 | 味噌 | 200 | 8000 | 1600000 |
| 2 | 2 | 斉藤商店 | ◇◇町☆☆ | 2 | 味噌 | 200 | 16000 | 3200000 |
| 2 | 2 | 斉藤商店 | ◇◇町☆☆ | 3 | バター | 190 | 9000 | 1710000 |
| 3 | 1 | 山田商事 | ○○区△△ | 4 | りんごジュース | 80 | 2000 | 160000 |
| 4 | 3 | 竹田ストア | ＊＊市□□ | 1 | 牛乳 | 120 | 1900 | 228000 |
| 4 | 3 | 竹田ストア | ＊＊市□□ | 4 | りんごジュース | 80 | 7000 | 560000 |
いざデータを利用するとなったときにこういう形の表を導き出すというのでは、何か手戻りをしているように見えるかもしれませんが、クエリはあくまでテーブルのデータから導き出されるだけのものであり、正規化していないテーブルを運用しているときに生じる上記a〜hのような問題は生じません。運用の安全性が全く異なることに注意してください。また、一般にテーブルを正規化していれば、必要な情報を含む様々なクエリをより容易に得ることができます。
そして注意すべきなのは、いくら正規化をきちんと理解し実践できても、そこから用途に適したクエリを導き出せなければ実用にならず、意味がないということです。正規化によるテーブルの構築と、クエリによるデータの加工は並行して学んでいく必要があります。
データの改定（訂正ではない）にどう対応するのか
正規化を進めていくと、データの冗長さが排除されていきますが、それにより不都合が生じる場合があります。
上記の例では単純化のため商品の価格改定はないものとしていましたが、実際に120円の商品を150円に値上げした場合にはどうなるでしょうか。第一段階（第一正規形）までの状態であれば、販売のたびに商品単価を入力するので、新たにその商品を販売する際にその商品の単価を150と入力すればよいだけです。過去に120円で販売していた記録があってもそれは当然失われず、売り上げ集計上の問題もありません。
しかし、第二段階（第二正規形）以降においては商品テーブルの単価を120から150に変更することとなります。このとき、過去に120円で販売していたという事実はどこにも残りませんので、過去に販売した記録と商品テーブルの単価を照らし合わせて売り上げを計算すると、（単価120円であったはずの）過去の売り上げ額が150円の単価で計算されることとなってしまいます。
これを解決する方法しては、販売明細テーブルに販売単価の列を設け、販売のたび商品ごとに単価を記録することが挙げられます。具体的には次のようになります。
（販売テーブル、取引先テーブルについては上記の第三正規形から変更なしのため省略）
| 販売ID | 商品コード | 販売単価 | 数量 |
|---|---|---|---|
| 1 | 1 | 120 | 5000 |
| 1 | 2 | 200 | 8000 |
| 2 | 2 | 200 | 16000 |
| 2 | 3 | 190 | 9000 |
| 3 | 4 | 80 | 2000 |
| 4 | 1 | 150 | 1900 |
| 4 | 4 | 80 | 7000 |
| 商品コード | 商品名 | 単価 |
|---|---|---|
| 1 | 牛乳 | 150 |
| 2 | 味噌 | 200 |
| 3 | バター | 190 |
| 4 | りんごジュース | 80 |
商品テーブルにおける牛乳の単価が150となっており（値上げが反映されている）、販売明細テーブルにおける牛乳の販売単価が1行目では120、6行目では150であること（値上げ前後の単価が記録されている）に注意してください。
この変更により売り上げ集計上の問題は解決します。しかし、販売のたびに単価を記録するということは、正規化を（一部とはいえ）あきらめて退行してしまったということなのでしょうか。
そのように解釈する見方もありますが、この変更後のテーブルもやはり第三正規形であると判断することができます。なぜかというと、商品テーブルに記録されるのは現在の定価つまり原則としての単価ですが、販売明細テーブルに追加した販売単価というのは実際に販売した際に適用された単価を指しています。これは常に一定であるとの保証はなく（現実には価格交渉により毎回変わることも考えられます）、つまり販売のたびに記録すべきものであり、販売明細テーブルに販売単価という列を置くことは冗長ではないと考えられるからです。
さて、さらに考えてみると、このようなテーブルでは商品価格の改定について正確に記録されているとはいえない、つまりいつからいつまでその価格で販売していたのかがわからない（売り上げがあればその時点の価格は記録されますが、そもそも売り上げがなければその価格であったという事実さえ残りません）ということに気づくと思います。もしそこまで記録していく必要があるのであれば、価格の改定を順次記録する価格履歴テーブルというものを設けることとなります。これ以上は複雑になりますので割愛しますが、重要なのはいずれも正規化という観点では問題ないものであり、実際にどこまでのデータを記録し管理していくべきなのかは業務上の必要性と手間のバランスから判断しなければならないということです。
主キーと外部キーの整合をどう保つのか
正規化以前の状態ではテーブルが1つしかありませんでしたので、ある主キーの値は1箇所にしか現れませんでした。
しかし、正規化後になると表が分割されます。1つの行（レコード）として離れ難くつながっていたデータが各テーブルにバラバラになってしまいます。それでも元のデータとしてのつながりを保つために、主キーの値が外部キーとしていくつものテーブルに現れます。主キーに変更がなければそれでも問題ないのですが、何かの理由で主キーが変更されると、外部キーの値もすべて変更しなければ全体としてつじつまが合わなくなってしまいます。また、外部キーの値を変更するときも、主キーとして存在しない値に変更してしまうと、両テーブルの間で矛盾が生じてしまいます。
このように主キーと外部キーの整合について常に気にしなければならない、というのは確かに正規化の副作用といえるところです。
しかし、リレーションシップの設定を行うことにより、主キーとして存在しない値を外部キーの値として記録できないよう設定できないようにすることができます（参照整合性）。また、主キーの値を変更すると同時に外部キーの値を一斉に変更することができます（連鎖更新）。つまりAccessの機能によりこの問題は解決されます。
