最初は、テーブル設計の話から、行きたいんだけれども、５年くらい前から比べると、最近はデータベース設計の本も選ぶのに苦労するくらい多いですよね。
手法もデータモデリング中心になってきていて、解りやすいようでいて、いざ実際にに開発する段階では、データモデリングからはいるというのは、逆に戸惑ってしまうことが多いんじゃないかと思います。
最初は、帳票を元手にした地道な方法でゆきましょうか・・・・前の繰り返しになりますけどね。
表題の『正規化』って言うのは、簡単に言えば、今、使ってる帳票から、まじりっけなしの純粋な要素にテーブルを分解して、実務に耐えうる「清く・正しく・美しいテーブル」を作る定石のこと。
もっと大胆に言っとけば、「正規化とは、帳票の骨格テーブルと、繰り返し使いまわすマスターテーブルに切り離してしまう作業のこと。」呪文みたいに、暗唱して覚えてください。
帳票部分は、日常的にじゃんじゃんデータを加えたり、削除したりするトランザクション・テーブルになります。切り出した部分は、必要なときだけ更新し、いつもは、帳票入力のために参照し、その目印の主キーだけをもらってくるマスターテーブルになります。もし、こういうきれいな構造に正規化しなければ、不安定で実用に耐えないテーブルになってしまいます。
初めて勉強する人には、堅苦しいこと言ってもはじまらないので、思いっきり単純にまとめましょう。理屈をぐだぐだ言っててもしょうがないので、まづは、市販の納品書からやっつけてみましょう。
こいつは、どこでも見かけるものですよね。
最初に、この帳票のすべての項目を 表にしてみましょう。
とまあ、こんなふうなんだけど、なんか変だよね。でも、表計算で育った人なんかは、案外、変だと思わないかもしれない。でも、このままテーブルを作ってしまうと、のちのち問題がたくさん出てきます。
問題点
１ すべての伝票につき、商品・数量・単価・金額・適用のセットが８回できるので、１伝票あたりの商品が少ない場合、いかにも無駄である
２ 商品が８個までしか登録できないので、それ以上の件数を登録する必要が発生した場合、大幅な改変が必要になる。
３ 商品ごとの合計数や件数を集計したい場合、商品１～商品８までの項目を集計し、それをまた集計しなおさなければなければならない。
４ 納品先や商品名など直に書いているので、変更があった場合、全レコードの該当項目を修正しなければならない。修正が不十分なら、間違ったデータが放置されることになる。
５ 納品先などすべてレコードにいちいち文字データを入れていたのでは、いかにも無駄である。住所などあれば尚更。
上のテーブルじゃ集計できないからって、左や下みたいなテーブルを作ってデータ入れちゃうとだめ。
こういう風にすると、商品ごとの集計はとりあえず出来るけど、[ No ]、[ 日付 ]、[ 納品先 ]、[税率]、[税額]、[税込合計金額]の6項目は、明らかにダブってて、いかにも無駄だし、[税込み合計金額]は、合計したら違う値が出てくるし、おまけに直打ちしてるから、間違えずに入力するのも大変だよね。
そこで、最初にやることは、項目のうち、繰り返し現れるものを、別のテーブルにしてしまうということ。それを『第１正規化』と言います。これは、帳票の骨格部分の整備作業です。
二つに分けると、５４項目から１３項目に減ってすっきりするでしょ？
でも、このままじゃ納品書テーブルと納品書明細テーブルは何の関係もないので、結びつきません。そこで、関連付けのために納品書明細テーブルに、納品書テーブルの何らかの項目と同じ値の項目を置かなければなりません。その納品書の項目は、同じ値が繰り返し出てくるようなものだと、関連付けられないので、ひとつのレコードに対し全レコードを通じて絶対重ならない値を与える必要がある。こうして人為的に作られた項目を、『主キー』（Primary Key,PK）と言います。まぁ、スポーツ選手の背番号やなんかではないことは確か。だって、チームの枠を取っ払うと、同じ番号がいくらであるでしょ？社会保険番号やクレジットカードの番号みたいなもんかな。
この例の場合、[No]をそれに当てることができます。ただ、それのためには、絶対に重複値を与えない方法が、必要だけどね。Ａｃｃｅｓｓの『オートナンバーフィールド』は、そのためにある特殊なフィールドなの。全体を通じて『一意性』を保証しているだけだから、更新中にキャンセルしたら欠番が出るけど、気にしていない。連続値を保証してるんじゃなくて、一意性を保証しているだけだから、ぜんぜん問題ないわけ。なんか初学者では気になる人が多いみたいだけど。納品書に『主キー』を設定できたら、その主キー項目と同じデータ型のフィールドを納品書明細テーブルに設けます。この関連付けのために作った項目を『外部キー』(Foreign Key,FK)言います。これで、伝票の大枠が出来上がりです。
今回の場合、納品書明細テーブルの主キーは、[商品No]＋外部キーの[納品書No]とします。こういう二つの項目でキーを形成する場合、そのキーは『複合キー』と言います。背番号の例でいえば、チーム名＋背番号ならユニークになるので複合キーに出来ます。でも、初代背番号３番とか世代管理もしなきゃいけないですね。（ほんとはこのキー嫌なんですけど、例題ですから…。）
Accessでテーブル作って保存するときに必ず、主キーが設定されていないと「主キーが設定されていません。」って、注意してくるでしょ？主キーはすべてのテーブルに設けるべきものだと考えたほうが良いでしょう。元のテーブルに主キーがないとリレーションも張れないよね。
手順をまとめると
帳票名をとりあえずテーブル名にする。
帳票のすべての項目（フィールド）を書き出す。
キー項目を決める。または作る。
繰り返しの部分を見極める。
繰り返し部分を分離して、子テーブルを作る。
子テーブルに適切な名前をつける。
子テーブルの主キーを作る。
親テーブルの主キーをそのまま子テーブルにもらって来て、外部キーを作る。
まぁ、伝票の表組みになってる部分を別テーブルににして、各テーブルに主キーを設ける。切り出したテーブルには、元の親テーブルの主キーを関連付けのためにもらってくる。と考えても良いでしょう。これで先にあげた問題点の１～３が、一応クリア出来ました。これで、帳票の骨格の完成です。これから贅肉を落としてゆきます。
One fact in one plaseと言う格言があります。「ひとつの事実は、１ヶ所に記録する。」と言うことです。データを一ヶ所にしか書かなければ、修正は１ヶ所ですみます。間違ったデータも発生しにくくなります。また、そのデータを使いまわせば、データの無駄もなくなります。
では、４～５の問題を解決してみましょう。
＜第２正規化＞
第２正規化では、主キーとの関係を考えます。第２正規化では純粋に主キーだけとの関係するものだけを残し、それ以外のものは、別テーブルに排除します。
この例の場合は、第１正規化納品書明細テーブルが対象にします。
ここで検証するのは、各項目が主キーにだけ、従属しているかどうかです。この例の場合、[納品書No]と[商品No]の複合キーが主キーですから、その２項目に従属するかどうかを検証します。個別に見てみましょう。
[品名]はどうでしょうか。品名は商品Noが決まれば決まります。000001 = MSOffice 2000 という具合です。でも、納品書Noが決まっても決まりませんね。納品書Noの何番とも結びつく可能性があります。
[数量]はどうでしょう。これは、商品Noが決まっても、いくつかまでは決まりません。今回の受注と商品に対して決まるのですから、納品書Noと商品Noの組み合わせに従属します。
[単価]はどうでしょうか。単価はいつ注文を受けても商品について決まっているものですから、商品Noに従属します。
[金額]は今回の注文に対して、決まるものですから、主キーに従属します。適用も同じです。
そうすると、下の図のような関係になります。
このことから、主キーである[納品書No]、[商品No]の組み合わせに従属するのは、[数量]、[金額]、[適用]となり、[商品No]にだけ従属する[品名]、[単価]と別のグループを形成します。主キーに従属しない[商品No]のグループを別テーブルに分離してしまいます。この過程を第２正規化といいます。
手順をまとめましょう。
複合キーを持つテーブルを選ぶ。
キー以外の項目がキー全体に従属しているのか、キーの一部に従属しているのか見極める。
キーの一部にだけ従属している項目をグループ化する。
従属しているキーの一部とその項目グループを別テーブルに分離する。
元のテーブルに残したキーは関連付けのための外部キーになる。
切り出したキーの一部を分離テーブルの主キーにする。
このことによって、どういうメリットがあるのでしょう。
それぞれのテーブルを見てみると、ちょっと性格が違いますね。解かりますか？
納品書明細テーブルは、毎日、何十件、何百件と新しく入力されてゆきます。削除もされるかもしれません。変更もありえます。
商品テーブルはどうでしょう。これは普通１回登録されれば普通モデルチェンジとか値段の改定、廃止がない限りはそのままです。データの倉庫みたいなものです。これは明細テーブルから参照されて利用されるだけのテーブルです。
もし、納品書明細に繰り返し繰り返し使われる商品の情報を直に書いていて、もし、価格の改定があったらどうなるでしょう。その商品が記入された全レコードを訂正しなければなりません。書き換えるときにミスが起きるかもしれません。商品テーブルを参照しているだけならば、該当商品の単価を１ケ所書き換えるだけですみます。このように、マスターを分離することによって、間違いの発生しにくいデータ構造が出来上がります。
おまけに、納品書明細に品名単価を書く必要がなくなるのでデータ量も激減します。
[金額]項目がなくなっているのに、お気づきでしょうか？[金額]は、[数量]*[単価]という操作で計算できます。このような値を『導出データ』といいテーブルからは省きます。これも実際の項目を持っていたりすると、[単価]や[数量]を訂正したときは、必ず、その都度[金額]も訂正しなければ間違ったデータが発生します。計算項目で持っていれば訂正の必要はありません。
でも、これで完成というわけじゃありません。
＜第３正規化＞
第２正規化では、キー項目に注目していましたが、第３正規化では、キー以外にも同じような従属－非従属関係がないかどうかを調べます。
最初の例ではあげていませんでしたけど、普通、納品書にはもう少し納品先の住所とか電話とかの情報がありますね。そういう主キー関連以外で従属関係のある項目をグループ化し別のテーブルにしてしまいます。メリットは第２正規化と同じです。
ということで、これで一応の完成です。
手順をまとめます。
キー以外の項目間の従属関係を検証する。
従属関係にある項目をグループ化する。
従属関係にあるグループのキーを見つける。なければ作る。
キーだけをを元のテーブルに残し、他の項目グループを別テーブルに分離する。元のテーブルに残したキーは関連付けのための外部キーになる。
この納品書の場合も本来は[納品先No]や[商品No]というキー項目は本来ありませんでした。このような場合は、主キーを作らなければなりません。わたしの場合は、主キーにはあまり有意な値は設けたくないと思ってます。これは好き好きですけどね。
以下に全体のリレーションを示しておきましょう。
こうして全体を見てもらえば冒頭に言った「正規化とは、帳票の骨格テーブルと、繰り返し使いまわすマスターテーブルに切り離してしまう作業のこと。」ということが理解していただけるのではないでしょうか。納品書テーブルとその明細テーブルが骨格です。じゃんじゃん使うのはこの部分です。あとの切り離した要素は、一般によくマスターと呼ばれるデータの倉庫みたいなものですね。
正規化は、データモデリングのうちの一部分の手法です。これですべてが処理できる訳ではありません。なぜ、最初にご紹介したかというと、過去のわたしの経験によるものです。
それまでDos版の『桐 Ver４～５』を使っていて、社内のシステムのＷｉｎｄｏｗｓ化とともにＰａｒａｄｏｘやＡｃｃｅｓｓに移行したとき、全く、手も足も出ませんでした。そうして途方にくれているとき、データベースは理論があるんだと知り、正規化手法を学びました。目から鱗が落ちるとは、将にこのこと、一挙に理解が深まりました。そういう経験があるから、まず最初にご紹介したわけです。今でも処理に迷ったときは、とりあえずセオリーどおりにしています。
正規化されたテーブルを作らなければならないのは、何故でしょうか？
正規化はリレーショナルデータモデルの誕生と別ち難く結びついています。リレーショナルデータベースはよく言われるようにＳＥＴ指向で非手続的にデータを処理します。そのためにはどうしても正規化されたテーブルでないと思うように扱えないのです。ただ、正規化したテーブルを使えば複雑なコーディングをせずにすみ、一挙に解かりやすく、単純なデータモデルで処理が出来るようになるのです。
ＳＥＴ指向なんてよく言いますが、何のことなんでしょう？みんな解かってるのかしら？(ｰｰ;)
少なくとも私は解かってなかった。(^_^;)
ということで、もう少し理論的なことを勉強しましょう。
＜補足＞
実際は、上記のテーブルでは、価格改定をした場合など、過去のデータまで全て変わってしまったり、その場限りの値引きなんかは出来ない、お役所みたいなシステムになってしまいますので、正規化さえすれば全てＯ．Ｋ．という訳ではありません。項目が、いったい何に従属しているのかを見極めるのは、簡単そうですけど、実は、大変難しい問題を含んでいます。そのことは追って詳しく述べたいと思います。
