今回はテーブル構成の検討に入るに際し、正規化・リレーションの考え方を紹介します。
データ入力の課題点
本題に入る前に、 今回作成する購買伝票画面イメージを改めて示します。
この画面を基に、入力データのサンプルを下に示す表にまとめてみました。
表1 入力データサンプル
| 管理番号 | 購買担当者 | 発注先名称 | 発注日 | 購買品目 | 購買数量 | 単価 | 小計 |
| 1 | 鈴木 太郎 | A社 | 2019/10/29 | 鉛筆（黒） | 100 | 50 | 5,000 |
| 1 | 鈴木 太郎 | A社 | 2019/10/29 | 消しゴム | 80 | 70 | 5,600 |
| 2 | 佐藤 花子 | A社 | 2019/10/30 | 消しゴム | 100 | 70 | 7,000 |
| 2 | 佐藤 花子 | A社 | 2019/10/30 | 鉛筆（赤） | 110 | 60 | 6,600 |
| 2 | 佐藤 花子 | A社 | 2019/10/30 | ボールペン | 100 | 80 | 8,000 |
| 3 | 鈴木 太郎 | B社 | 2019/10/30 | シャープペン | 50 | 100 | 5,000 |
| 3 | 鈴木 太郎 | B社 | 2019/10/30 | ノート | 200 | 120 | 24,000 |
| 4 | 山田 一郎 | A社 | 2019/10/31 | ボールペン | 50 | 80 | 4,000 |
| 5 | 佐藤 花子 | A社 | 2019/10/31 | 鉛筆（黒） | 200 | 50 | 10,000 |
| 5 | 佐藤 花子 | A社 | 2019/10/31 | 消しゴム | 100 | 70 | 7,000 |
| 5 | 佐藤 花子 | A社 | 2019/10/31 | 鉛筆（赤） | 100 | 60 | 6,000 |
| 5 | 佐藤 花子 | A社 | 2019/10/31 | ボールペン | 90 | 80 | 7,200 |
いかがでしょうか？この表を見て、Excelを利用されている方は、こんなものかな、と思われるかもしれません。
ここで、読者の皆さんに次の質問をしてみましょう。
① 「管理番号」が同じであれば「購買担当者」、「発注先名称」、「発注日」が 一緒のため、何度も同じデータ入力をすることは煩雑ではありませんか？
② 「購買数量」と「単価」が決まれば、「小計」は自ずと決まるため、 データを入力することは煩雑ではありませんか？
③ 万一、佐藤花子さんが発注した［鉛筆（赤）］の「単価」が60円ではなく 70円だったら全てのレコードを修正することは大変ではありませんか？
修正しなくてはいけないレコード数が1000個の場合、100% 修正漏れがないと言い切れますか？言い切れませんよね？
これらの質問に一つでも「はい」と答えた方は、ぜひAccessを使うことを検討してほしいのです。
Accessは正規化に基づき、リレーションを組んでデータの整合性を担保することができます。
また、テーブルの構成を適切に設計することで、不要なデータの入力を削減することができ、業務効率の改善を図ることもできるのです。
データベースの基礎知識を細かく学びたいという方はAmazon Kindleを活用して様々な情報を収集することが大切です。
スポンサーリンク
次からはサンプルデータを交えながら概要を紹介していきます。
データの正規化
先ほど、繰り返しのデータ入力は煩雑ではないかとの質問をしましたが、
データの正規化とは、データの重複をなくしデータの整合性を担保できるようにテーブルの設計を行うことです。
例えば、表1で見てみると管理番号が「5」のレコードで、実は、発注日が「2019/10/29」であった場合、重複するレコードをすべて修正する必要があります。
レコード数が多くなればなるほど、修正に時間を要することになります。
したがって、これら重複が発生し得るデータのテーブルは正規化により分割をします。
前回のテーブル作成において5つのテーブルを作成したのは、このためだったのです。
今回の例では、データ項目が少ないので、イメージしにくいかもしれませんが、担当者の詳細情報（例えば、従業員番号、性別 等）をデータベースに保持したい場合、繰り返し発生するデータは分割して別のテーブルで管理すれば、データの修正は1回で済みます。
同じことは、商品（例えば、品名、型式、単価 等）にも、取引先（例えば、社名、電話番号、FAX番号、住所 等）の情報を取り扱う際にも考えられることです。
このため、今回のテーブル設計では、
① T_購買伝票
② T_購買伝票内訳
③ T_商品マスタ
④ T_取引先マスタ
⑤ T_担当者マスタ
を作成しました。
これまでの内容でデータの正規化について、その必要性を理解いただけたのではないでしょうか？
次に表1のデータについて、正規化を考慮した各テーブルに保存するための方法を順を追ってみていきましょう。
図2赤枠の各フィールドには、1つの購買伝票につきそれぞれ1つのデータを入力します。一方で、緑枠の各フィールドは１度に購入する品目の数（無限数）だけデータを入力します。
表1のうち、管理番号が「1」となるレコードを例にしてみると、次のようになります。
T_購買伝票： 図2の赤枠に相当
| 管理番号 | 購買担当者 | 発注先名称 | 発注日 | 合計 |
| 1 | 鈴木 太郎 | A社 | 2019/10/29 | 10,600 |
T_購買伝票内訳： 図2の緑枠に相当
| 購買品目 | 購買数量 | 単価 | 小計 |
| 鉛筆（黒） | 100 | 50 | 5,000 |
| 消しゴム | 80 | 70 | 5,600 |
正規化は繰り返しのデータが発生しないようにすることを目的としており、この例では、T_購買伝票テーブルに該当するフィールドが「管理番号」、「購買担当者」、「発注先名称」、「発注日」、「合計」に、T_購買伝票内訳テーブルに該当するフィールドは「購買品目」、「購買数量」、「単価」、「小計」になります。
ここで、T_購買伝票内訳テーブルのレコード内容をみると、今後も同様のレコードが登録される可能性があります。つまり、別の発注時にも同様に鉛筆（黒）を100本購入する可能性があるということです。そうすると、各レコードの特定が難しくなるため、この問題を解消するためにレコードを一義的に定めることができるように新たなフィールドとして「購買内訳ID」を追加します。
すると、T_購買伝票内訳テーブルは次のようになります。この際、購買内訳IDは同じ値が入らないようにするため、オートナンバー型にする必要があり、このフィールドが主キー設定を持つことになります。
「購買内訳ID」フィールドを追加したT_購買伝票内訳テーブル
| 購買内訳ID | 購買品目 | 購買数量 | 単価 | 小計 |
| 1 | 鉛筆（黒） | 100 | 50 | 5,000 |
| 2 | 消しゴム | 80 | 70 | 5,600 |
これで、正規化としての第1段階をクリアすることができました。
テーブルのリレーション
これまで、正規化の手順を見てきましたが、表1の各フィールドを分割してテーブルを作成したことで、T_購買伝票テーブルとT_購買伝票内訳テーブルの各レコードの関係性がわからなくなってしまいます。つまり、T_購買伝票内訳テーブルの購買内訳ID=1,2のレコードがT_購買伝票テーブルの管理番号=1と関係を持っていることを示す必要があります。これが、テーブルのリレーション設定となります。
T_購買伝票テーブルでは、「管理番号」フィールドが主キー設定をされており、つまり、重複するデータは存在しないことになります。そこで、T_購買伝票内訳テーブルから見たときに、 各レコードが 「管理番号」と紐づけば、T_購買伝票テーブルの各レコードと関係性を持つことができるようになります。したがって、T_購買伝票内訳テーブルに「管理番号」フィールドを追加します。
「管理番号」フィールドを追加したT_購買伝票内訳テーブル
| 購買内訳ID | 管理番号 | 購買品目 | 購買数量 | 単価 | 小計 |
| 1 | 1 | 鉛筆（黒） | 100 | 50 | 5,000 |
| 2 | 1 | 消しゴム | 80 | 70 | 5,600 |
上のように、購買内訳ID=1, 2の各レコードはT_購買伝票テーブルの管理番号=1のレコードと紐づいているため、T_購買伝票内訳テーブルの管理番号にも「1」を登録します。
この設定により、「管理番号」フィールドでT_購買伝票テーブルとT_購買伝票内訳テーブルを紐づけることができました。
T_購買伝票テーブルとT_購買伝票内訳テーブルは「1対無限」のリレーションが成り立ちます。
リレーションの考え方については理解いただけたかと思います。
それではこの考え方を基に、他のフィールドについても細かく考えていきましょう。
まず、T_購買伝票テーブルの「購買担当者」フィールドについて考えましょう。
改めて表1を見てみると、「購買担当者」フィールドには「鈴木 太郎」と「佐藤 花子」が繰り返しでてきます。
実務では、当然同じ担当者がデータを登録するために繰り返しデータが出てくるわけですが、例えば結婚等で姓が変わった場合に、全てのデータを変更しないといけない場合が出てきます。
そこで、T_購買伝票テーブルの内、「購買担当者」フィールドは別のテーブルで管理する方が好ましいことになるわけです。それが、T_担当者マスタテーブルになります。
T_担当者マスタ
| 担当者ID | 担当者氏名 |
| 1 | 鈴木 太郎 |
| 2 | 佐藤 花子 |
| 3 | 山田 一郎 |
ここでは、「担当者氏名」フィールドしか作成していませんが、例えば「従業員番号」や「所属部署」等のフィールドを追加することで、より詳細な情報管理をすることも可能になります。
上に示したように、T_担当者マスタテーブルでは「担当者ID」フィールドを作成していますが、「氏名」だけにすると、同姓同名の人がいた場合にレコードを特定することができません。
また、先に説明をしたリレーション設定で、T_購買伝票テーブルと紐づけをするフィールドも必要となります。
したがって、「担当者ID」フィールドを主キー設定とすることで、レコードを一義的に決めることができ、このフィールドを用いてリレーション設定を行うことができるようになります。
リレーションには「担当者ID」に登録される整数値を用いるため、T_購買伝票テーブルの「購買担当者」フィールドにも整数を入力することになります。このため、Accessで作る購買伝票について その３ テーブル作成 で作成したT_購買伝票テーブルの「購買担当者」フィールドのデータ型は「数値型」としたわけです。
この一連のリレーションについても下に図解していますので確認してみてください。
この説明で、「それならT_購買伝票の発注先名称フィールドも同じことができるんじゃないか？」と思われた方もいるかもしれません。
そうなんです！！
T_購買伝票テーブルの「発注先名称」フィールドも購買担当者と同様の扱いができます。
そのため、T_取引先マスタテーブルを作成していたわけです。
リレーションの設定は、購買担当者と全く同様の方法となりますので、図に示して説明としたいと思います。
それでは、次にT_購買伝票内訳テーブルについても詳しく見ていきましょう。
改めて、現在までのT_購買伝票内訳テーブルを見てみましょう。
| 購買内訳ID | 管理番号 | 購買品目 | 購買数量 | 単価 | 小計 |
| 1 | 1 | 鉛筆（黒） | 100 | 50 | 5,000 |
| 2 | 1 | 消しゴム | 80 | 70 | 5,600 |
ここで着目していただきたい点は、「購買品目」フィールドと「単価」フィールドです。
先ほどと同じ考え方をすると、購買品目の値は繰り返し出てきます。
また、購買品目が決まれば一義的に単価は決まりますので、これらフィールドは別のテーブルとして管理するべきで、それがT_商品マスタテーブルになります。
T_商品マスタテーブルでは主キー設定をした「商品ID」フィールドがありますから、これをリレーション設定に用いることになります。
そして、もう一つ重要なこととして「小計」フィールド をデータベースに保持する必要があるか、という点です。「小計」は「購買数量」と「単価」が決まれば自ずと決まる値で、わざわざデータベースに保存する必要はありません。
実は、Accessではテーブルの値をフォームやレポートに表示する際、各フィールドの値を用いて計算した結果も表示することができます。
このため、T_購買伝票内訳テーブルに「小計」フィールドを作る必要はなく、今回の場合は削除します。
この考え方は、T_購買伝票テーブルの「合計」フィールドにも同様に適用できるものです。
したがって、T_購買伝票テーブルの「合計」フィールドは削除します。
以上を踏まえて、T_購買伝票内訳テーブルを再設計したものが次のとおりです。
T_購買伝票内訳
| 購買内訳ID | 管理番号 | 購買品目 | 購買数量 |
| 1 | 1 | 1 | 100 |
| 2 | 1 | 2 | 80 |
T_商品マスタ
| 商品ID | 品目 | 単価 |
| 1 | 鉛筆（黒） | 50 |
| 2 | 消しゴム | 70 |
これで、全てのテーブル設計を完了することができました。最後に、これらテーブルがどのようなリレーション関係になっているかを示します。
まとめ
今回は、データ入力作業の効率化・データ整合性の担保を目的として、「正規化」と「リレーション」について紹介しました。正規化したテーブルに対してリレーションを組むことで最終的な運用の際にはデータの整合性が常に担保された状態を維持することができるようになります。また、繰り返しのデータ入力も省くことができ結果として、業務効率を飛躍的に向上することができるようになります。
次回は、Accessでリレーションシップを確立する方法について紹介したいと思います。
スポンサーリンク
コメント