長谷川 裕行 （はせがわ ひろゆき）
有限会社 手國堂 代表取締役
http://www.hirop.com/
テクニカルライターとして活躍。プログラミングに関する著書多数、DB Magazineなどにも多くの記事を提供している。 |
リレーショナルデータベースは、実際のデータを管理する上で非常に強力な仕掛けを持っています。しかしそれをうまく使って行くためには、ちょっと難しいデータ管理の仕組みを理解しなければなりません。
できるだけイメージを把握しやすいよう、実例を用いてリレーションの意味とそこで発生する矛盾、その解決策について説明しましょう。
前回、複数のテーブルまたはクエリーを結び付けるリレーションには、1対多の関係になるものと1対1の関係になるものがあると説明しました。この他に、多対多という形もありますが、あまり一般的ではありません。
1対多と1対1のリレーションについて、実例を使ってもう少し説明しておきましょう。
表1のようなフィールド構成のテーブル「T_商品」があり、そこに、表2のようにレコードが記録されているとします。
表1:テーブル「T_商品」のフィールド構成
| フィールド名 |
データ型 |
| 商品ID |
Long(長整数) |
| 品名 |
Text(文字列) |
| 単価 |
Currency(通貨) |
表2:テーブル「T_商品」に記録されているレコード
| 商品ID |
品名 |
単価 |
| 0001 |
パソコンＡ |
98,000 |
| 0002 |
パソコンＢ |
198,000 |
| 0003 |
パソコンＣ |
248,000 |
商品と販売記録の関係が、典型的な1対多のリレーションとなります。
- 販売状況を記録するテーブル -
上記の商品が、販売状況が表3のようなフィールド構成のテーブルに記録されていくとします。
表3:商品の販売状況を記録するテーブル「T_販売状況」
| フィールド名 |
データ型 |
| 販売ID |
Long(長整数) |
| 日付 |
Date(日付) |
| 販売先 |
Text(文字列) |
| 商品ID |
Long(長整数) |
| 数量 |
Integer(整数) |
このテーブルは以下のような形で先の「T_商品」とリレーションされており、表4のような形でレコードが記録されていきます。
表4:テーブル「T_販売状況」に記録されているレコード
| 販売ID |
日付 |
販売先 |
商品ID |
数量 |
| 00001 |
99/10/01 |
xx商会 |
0002 |
3 |
| 00002 |
99/10/01 |
xx小学校 |
0001 |
5 |
| 00003 |
99/10/01 |
xx不動産 |
0002 |
1 |
| 00004 |
99/10/02 |
xx設計 |
0003 |
2 |
| 00005 |
99/10/02 |
xx商店 |
0002 |
5 |
| 00006 |
99/10/01 |
xx中学校 |
0003 |
3 |
- 販売状況を提示する選択クエリー -
この2つのテーブルを元に、具体的な販売状況を表示するレコードセットを生成するには、表5のような形のクエリーを作ります。
このクエリーを開くと、表6のような形で販売状況が表示されます。
表5:具体的な販売状況を表示する選択クエリー「Q_販売状況」
| テーブル： |
T_販売状況 |
| |
T_販売状況 |
| |
T_販売状況 |
| |
T_販売状況 |
| |
T_商品 |
| |
T_商品 T_販売状況 (式) |
| フィールド： |
販売ID |
| |
日付 |
| |
販売先 |
| |
商品ID |
| |
品名 |
| |
単価 |
| |
数量 |
| |
単価*数量 |
表6:選択クエリー「Q_販売状況」によって生成されたレコードセット
- 「商品ID」フィールドに注目 -
双方のテーブルに「商品ID」フィールドが存在しています。
これが主キーとなり、リレーションによって結び付けられることで、「T_販売状況」の「商品ID」フィールドに記録された値から「T_商品」の中の唯一のレコードが参照され、その中の「品名」と「単価」フィールドの値が引き出されてきます。これを「値の導出」と呼びます。
テーブル「T_販売状況」のレコードの中には「T_商品」のレコードを指し示す「商品ID」フィールドの値が何度も登場します。しかし、「T_商品」の中では1つの「商品ID」を持つレコードはただ1件だけしか存在しません。
- 参照と被参照 -
2つのテーブルには
テーブル「T_販売状況」はテーブル「T_商品」を
参照する
という関係が成り立っているため、
T_販売状況:参照側
T_商品:被参照側
となります。
参照側である「T_販売状況」には同じ「商品ID」が複数記録される可能性がありますが、被参照側の「T_商品」には1レコードしか登場しません。従って、
参照側:多
被参照側:1
という1対多の関係が成り立つのです。
- その他の1対多の関係 -
商品と販売記録の他に、表6に示すようなテーブルの関係で1対多のリレーションが成り立ちます。
表7：1対多の関係が成り立つテーブル
| 1側(被参照側) |
多側(参照側) |
| 商品 |
入出庫記録 |
| 社員 |
勤怠記録 |
| 仕入先 |
商品 |
| トッピング |
ピザ |
| オプション部品 |
自動車 |
1対1のリレーションでは、商品の在庫管理が典型例となります。
- 在庫数を記録するテーブル -
先に示したテーブル「T_商品」を元に、在庫管理を行うとします。「T_商品」に記録された商品に対応する在庫状況のテーブルが必要です。これには、表7のようなフィールド構成が考えられます。テーブル名は「T_在庫」としておきましょう。
テーブル「T_在庫」には、表8のような形でレコードが記録されていきます。
表8:商品の在庫状況を記録するテーブル「T_在庫」のフィールド構成
| フィールド名 |
データ型 |
| 商品ID |
Long(長整数) |
| 在庫数 |
Long(長整数) |
表9:テーブル「T_在庫」に記録されているレコード
| 商品ID |
在庫数 |
| 0001 |
16 |
| 0002 |
8 |
| 0003 |
31 |
- 在庫数を提示する選択クエリー -
テーブル「T_商品」と「T_在庫」は、以下のような形で「商品ID」フィールドによって関連付けられています。
この2つのテーブルを元に、具体的な在庫状況を表示するレコードセットを生成するには、表9のようなクエリーを作ります。
このクエリーを開くと、表10のような形で販売状況が表示されます。
表9:具体的な在庫状況を表示する選択クエリー「Q_在庫」
| テーブル： |
T_在庫 |
| |
T_商品 |
| |
T_商品 T_在庫 |
| フィールド： |
商品ID |
| |
品名 |
| |
単価 |
| |
在庫数 |
表10:選択クエリー「Q_在庫」によって生成されたレコードセット
| 商品ID |
品名 |
単価 |
在庫数 |
| 0001 |
パソコンＡ |
98,000 |
16 |
| 0002 |
パソコンＢ |
198,000 |
8 |
| 0003 |
パソコンＣ |
248,000 |
31 |
- 「商品ID」フィールドに注目 -
双方のテーブルに「商品ID」フィールドが存在しています。これらがリレーションによって結びつけられることで、「T_在庫」の「商品ID」フィールドに記録された値から「T_商品」の中の唯一のレコードが参照され、その中の「品名」と「単価」フィールドの値が導出されます。
このとき、先ほどの「T_販売状況」とは違って、テーブル「T_在庫」のレコードにも「1つの商品に対して1件のレコードだけ」が存在しています。従って、「T_在庫」と「T_商品」のレコードは「1対1」で対応していることになります。
- 1つのテーブルを分割している -
この場合、
T_在庫:参照側
T_商品:被参照側
という関係となり、「T_商品」が被参照側であることは変りません。
参照側である「T_在庫」と被参照側である「T_商品」のレコードは、共に「同じ商品に対する別の種類の情報」を仲良く分担して保存していることになります。
図1:1つの情報を複数のテーブルに分割している
- その他の1対1の関係 -
商品と在庫数の他に、表11に示すようなテーブルの関係で1対1のリレーションが成り立ちます。
表11：1対1の関係が成り立つテーブル
| 被参照側 |
参照側 |
| 生徒 |
試験成績 |
| 社員 |
給与 |
| 選手 |
順位 |
|
|
このように1対1のリレーションシップは、基本的に1つのテーブルでも表現可能なデータを、複数のテーブルに分割する際に用いられます。特殊な関係のようにもみえますが、実際には非常に重要な関連付けの形態です。
- 1対1は使われない?! -
VisualStudioのヘルプには「1対1のリレーションシップはあまり用いられない」と書かれていますが、それは
基本的に1つのテーブルとして定義できる情報を
わざわざ複数のテーブルに分割することはない
という意味でしょう。
実際には、このような形で複数のテーブルを用意して1対1のリレーションシップを設定する場面は、案外存在します。決して「あまり用いられないから使わない方がよい」ということではありません。
- 1つのテーブルに押し込めてはいけない -
例えば「社員の情報」ということで
氏名、生年月日、住所、家族構成、基本給
身長・体重などの健康診断データ
健康保険章番号、社内試験成績、取得資格
…などなど、ありとあらゆる情報を1つのテーブルに押し込んでしまって、問題はないでしょうか?
これらを1つのテーブルに記録すると、体重が増えただけで、試験成績や基本給のデータまでアクセスされることになってしまい、非常に効率が悪くなります。情報は必要なものだけに絞ってアクセスする方が効率的で、万一データ障害が発生した場合でも、被害を最小限に食い止められます。
- 種類別にテーブルを分ける -
これら各種情報は、
氏名、生年月日などの基本情報
身長・体重などの健康診断データ
基本給、健康保険番号などの管理用データ
社内試験成績、取得資格
などの個人成績 など、いくつかに分類できます。従って
社員基本テーブル、健康診断結果テーブル
管理データテーブル
…などと種類別にテーブルを作り、唯一のレコードを示す社員IDで1対1のリレーションを設定するのが正解です。
- 用途と更新頻度で分類する -
例に示した商品と在庫数もリレーションも、商品の基本データとその在庫数とは「異なる種類の情報」と捉えられるので、切り離した方が安全です。実際の業務処理では、商品の基本データには
商品の仕様、生産開始年月日、梱包単位
発注から納品までの日数、仕入先
など細かな情報が含まれています。これらと現在庫数とは明らかに種類の異なる情報です。
在庫数は頻繁に更新されますが、その度にあまり更新されることのない他のデータまでアクセスされるのは非効率的です。
基本情報:商品名、単位、販売単価
仕入・販売情報:仕入単価、仕切率、梱包単位
仕入先、納入日数
現在庫情報:現在高
在庫管理情報:前月越高、前月入庫数
前月出庫数
などと分けて管理した方がよいでしょう。
リレーションを設定するということは、異なるテーブル同士でフィールドの参照が行われるということです。すると、テーブルに記録されているレコードの内容によっては、矛盾の発生する可能性が出てきます。
- テーブル間の矛盾 -
例えば、以下のような場合を考えてみましょう。
商品の基本情報を記録したテーブル「T_商品」
と
商品の販売状況を記録した
テーブル「T_販売記録」
が
「商品ID」フィールドで関連付けられている
さらに「T_商品」は
商品の在庫状況を記録したテーブル「T_在庫」
とも
「商品ID」フィールドで関連付けられている
とします。先に1対多および1対1のリレーションのところで例に示した形と同じです。
- 存在しない商品が販売される?! -
ある日、商品ID=0001の「パソコンＡ」が生産中止になったとします。そこで、「T_商品」テーブルから該当するレコードが削除されました。
すると
「T_販売記録」の中の「商品ID=0001を販売した記録」のレコードがリレーションをたどり「T_商品」を参照して商品ID=0001の「パソコンＡ」を見つけようにも当該レコードが存在しないということになってしまいます。また、「T_在庫」の中の「商品ID=0001の在庫数」も同じ状況となって商品ID=0001の「パソコンＡ」が見つからない
ことになります。
- 販売額が勝手に変わる?! -
また、商品ID=0002の「パソコンＢ」の単価が178,000円に値下がりしたとしましょう。すると
「T_商品」と「T_販売記録」から生成されているクエリー「Q_販売状況」の中の「商品ID=0002を販売した記録」にある「単価*数量」フィールドの値(金額)の計算結果が変ってしまう
ことになります。
このように、基本情報を変更すると
販売したはずの商品が存在しなくなったり
過去の販売結果が変ってしまったり
することになるのです。
- テーブル間の矛盾を防ぐ手段 -
このような矛盾を避けるために、参照整合性という考え方が適用されます。参照整合性とは、上述したような参照側と被参照側との間で発生する矛盾を回避するため
リレーションシップが設定されているテーブル間で被参照側のレコードが書き換えられたり削除されないように
する機能です。
また、参照整合性の発展機能として、
被参照側のレコードを削除すると、そのレコードを参照している参照側のレコードも連動して削除される(レコードの連鎖削除)
ようにしたり、
被参照側のレコードを更新すると、そのレコードを参照している参照側のレコードも連動して更新される(レコードの連鎖更新)
ようにもできます。
図2:テーブル間の矛盾と参照整合性
リレーションシップは流動的な情報を効率的に管理するための手段であり、永久に存続し続けるものではありません。いつかは固定的に記録されることになります。
- データは最終的に固定される -
商品の価格は変動するものですし、未来永劫にわたって1つの商品が存在し続けるとは限りません。 参照整合性の問題を考えれば、以下のようなことが言えます。
複数のテーブルをリレーションさせると
いつかは必ず矛盾が発生する
このことから、
リレーションシップによる複数テーブルの関連付けは、
一時的な処理の中でのみ有効である
ということがお分かりいただけるはずです。
つまり、
ある処理の中でリレーションシップによって関連付
けられていた情報も最終的にはリレーションを持
たない単一のテーブルとして固定的に記録されな
ければならない
のです。
- 一定期間だけリレーションされている -
例えば、1日の処理の中では
商品と販売記録
はリレーションを保っていますが、1日の仕事が終わって日締め処理を行えば、リレーションされていたテーブルのレーコードは
1日分の記録として別のテーブルに記録される
ようになります。このときにはテーブルは1つに
まとめられるので、リレーションシップは存在し
ません。
同じように
商品と在庫数
の情報も、一定の期間(通常は1箇月)が経てば
当月分の「最終在庫数」=翌月分の処理の「前月越高」
として別テーブルに固定的に記録されます。
- レコードの定着処理 -
こういった
リレーションを解き、複数のテーブルに分かれて
いたフィールドを1つのレコードにまとめて固定する
ことを「テーブルの定着」と呼びます。
同じことを「データの確定」と呼ぶ人もいるようですが、一般に「データの確定」とは
入力されたデータを一時的な領域から実際の
作業領域へまたはメモリからディスクへと送り、
取消や変更できないようにする
ことを示すため、混同を避ける意味で「定着」と表現しています。
- 定着のタイミングは業務により様々 -
レコードの定着作業は、例えば先に示した選択クエリー「Q_販売記録」のレコードセット(以下の表)を、「T_販売記録FIX」などといった名前の単一のテーブルに記録する作業となります。
リレーションを解体してレコードを定着するタイミングは、1日の場合もあれば1箇月の場合もあり、あるいは1時間単位といったこともあります。これは、被参照側である基本テーブルに記録されたデータが更新される頻度や、参照側のテーブルにレコードが追加される頻度などによって変わってきます。
通常は1日または半日単位とし、基本テーブルの情報を更新する前に必ず定着処理を行なって、取り引きテーブルのレコードをすべて削除する必要があります。
表12:定着された販売記録
今回も、かなり理屈っぽいお話になってしまいました。専門書などを読むと、さらに難しい専門用語と概念で、頭がくらくらすること請け合いです。
リレーションシップと参照整合性はリレーショナルデータベースを扱う上で避けて通れない問題です。リレーショナルデータベースの仕組みとその応用は、実際のデータとその用途に深く関連しているため、実際の開発経験がないとちょっと分かりにくいかもしれません。
リレーションシップと参照整合性、レコードの定着処理などを理解するには、あなたの会社の業務などを、実際にデータベース化してみることをお奨めします。簡単な構成で商品の販売管理処理などを作ってみれば、案外簡単にリレーションやレコードの矛盾の意味が理解できるでしょう。
|
