データベース千夜一夜第10回

テーブルの関連付けとサブクエリによる条件の抽出 長谷川裕行
有限会社 手國堂

新しいデータベースとリレーションの設定

前回紹介したように、今回からサンプル・データベースの構造が新しくなっています。新しいデータベースの入手方法と、旧データベースとの入れ替え(新データベースの登録)方法については、前回の本コラムをお読みください。

まず、新しいデータベースのすべてのテーブルとその構造を紹介しておきましょう。


- 新しいデータベースの構造 -

新しいサンプル・データベース内のテーブルとそれぞれの役割は、表1のようになります。

テーブルの数が一気に増えました。各テーブルの役割を見れば、「商品」の基礎情報が「商品_mr」と「在庫_mr」に分割され、さらに商品の仕入先情報を記録する「仕入先_mr」など新しいテーブルが追加されていることが分かります。

他にも、商品を仕入れた結果や販売した結果を記録するためのテーブルが追加され、それぞれ「ヘッダ」と「明細」の2つのテーブルに分かれています(こちらについては、今回は使用しません)。

これらは、(それでもかなり簡略化していますが)実際の販売業務に使用するデータベースの構造に近いもので、仕入~販売~在庫数の把握といった各業務に対応しています(但し、商品の在庫については数量を把握できるだけで、在庫管理業務には対応していません)。

これらたくさんのテーブルは、例えば商品の情報なら「商品_mr」を中心にして「仕入先_mr」と「在庫_mr」の各テーブルが関連付けられるようになっています。つまり、テーブルの関連付け――リレーションを設定することが前提の構造です。

表1:サンプルデータベースのテーブル構成
テーブル名 役割
基本情報 システムを使用しているユーザー企業の名称、住所、消費税額など
商品_mr 商品の基本情報を記録
在庫_mr 商品の在庫数を記録
仕入先_mr 商品仕入先の基本情報を記録
得意先_mr 得意先(顧客)の基本情報を記録
仕入ヘッダ 1件の仕入に対する日付や仕入先などの基礎情報を記録※
仕入明細 1件の仕入に対する商品と数量などを記録※
売上ヘッダ 1件の売上に対する日付や得意先などの基礎情報を記録※
売上明細 1件の売上に対する商品と数量などを記録※
累計仕入_fx リレーションを断ち切って内容を定着させた仕入情報※
累計売上_fx リレーションを断ち切って内容を定着させた売上情報※
※ 今回は使用しません。詳しくは回を追って説明します

各テーブルの構造を、Enterprise Managerのデザイン画面で確認しておきましょう。画面1~画面11を参照してください。













- 3つのテーブルの関連付け -

先述したように、これらのテーブルはリレーションを設定することで機能するようになっています。しかし、まだ一部のリレーションだけしか設定されていません。今回は、商品の基本情報だけを扱うので、それに関連するリレーションだけを予め設定してあります。

SQL Serverでは、予め設定するリレーションをダイヤグラム(Diagram)オブジェクトに記録します。画面12が「商品_mr」「仕入先_mr」「在庫_mr」の3つのテーブルを関連付けたダイヤグラム“DIAGRAM1”です。

これを見れば、「商品_mr」の「仕入先ID」フィールドと「仕入先_mr」の「仕入先ID」フィールド(主キーフィールド)、「在庫_mr」の「商品ID」フィールドと「商品_mr」の「商品ID」フィールド(主キーフィールド)とが、それぞれ関連付けられていることが分かります。



- 「1対多」と「1対1」 -

ダイヤグラム“DIAGRAM1”で設定されているリレーションは、2通りあります。

「商品_mr」と「仕入先_mr」のリレーション(「仕入先ID」フィールド)
「仕入先_mr」テーブルには、商品の仕入先(の情報)が記録されています。「商品_mr」の「仕入先ID」フィールドの値に基づき、それと同じ値を持つ「仕入先_mr」のレコード(仕入先の情報)を参照します。「商品_mr」が参照側で「仕入先_mr」が被参照側テーブルとなり、「仕入先_mr」から唯一のレコードを参照する形となるため、「仕入先_mr」が「1」、「商品_mr」が「多」という形の1対多のリレーションとなります。


「在庫_mr」と「商品_mr」のリレーション(「商品ID」フィールド)
「在庫_mr」テーブルには商品の現在庫数が記録されています。「商品ID」フィールドの値から、対応する「商品_mr」テーブルの1件のレコード(「商品ID」フィールドの値が一致するレコード)を特定できます。「商品ID」フィールドの値によって、「商品_mr」の1件のレコードと「在庫_mr」の1件のレコードがつながっています。「在庫_mr」が「商品_mr」を参照するのではなく、双方の唯一のレコード同士が「商品ID」で直結されている形なので、1対1のリレーションとなります。


- 共通のリレーションだけを設定する -

テーブルのリレーション自体は、このようにデータベースで予め設定できるため、アプリケーションのソースコード内でSQLを記述して個別にリレーションを設定する必要はありません。

但し、アプリケーションに関連するリレーションを何でもかんでもデータベース側で設定していては、データベースはダイヤグラムだらけになってしまいます。オブジェクトが増えればそれだけリソースを消費することになり、データベース自体も大きく重くなります。

データベースは1つのアプリケーションからだけ使用される訳ではありません。複数のアプリケーションがデータベースを共用するのが普通なので、ダイヤグラムでは「各種アプリケーションに共通する基本的なリレーションだけ」を設定し、あとは各アプリケーション内でSQLを使って個別にテーブルを連携させることになります。

今回は「商品_mr」と「仕入先_mr」のリレーションを用いますが、その他には「売上明細」と「商品_mr」「得意先_mr」のリレーションなどが汎用的でしょう。これらについては、回を追って紹介していきます。



トップページ
新しいデータベースとリレーションの設定
新しいデータベースの構造
3つのテーブルの関連付け
「1対多」と「1対1」
共通のリレーションだけを設定する
マスターテーブルから在庫数を切り分ける理由
サブクエリによる条件の抽出
あとがき
Copyright © GrapeCity inc. All rights reserved.