長谷川 裕行 （はせがわ ひろゆき）
有限会社 手國堂 代表取締役
http://www.hirop.com/
テクニカルライターとして活躍。プログラミングに関する著書多数、DB Magazineなどにも多くの記事を提供している。 |
テーブルやクエリーについて基礎的なことを理解し、それを操作するための方法を知っても、いざ現実の業務に応用するとなると難しいものです。今回は、現実の業務をデータベース化するために必要な、リレーションとクエリーの機能について説明します。
データベースソフトの入門書では、住所録やコレクション管理などのシンプルなデータベースが例に使われます。これらシンプルなデータベースの構造を、もう一度検討してみましょう。
- シングルテーブルのデータベース -
住所録やコレクション管理などの処理は、1つのテーブルだけで完結できるシングルテーブルのデータベースです。シングルテーブルのデータベースでは、処理に必要なすべての情報が、1つのテーブルを構成するフィールドとして定義されます。
例えば住所録なら
氏名 読み 電話番号 郵便番号 住所
といったフィールド構成になるでしょう。これが蔵書管理なら
書名 著者 出版社 判型 価格
といったフィールド構成となるはずです。
このようにシングルテーブルのデータベースでは、1人分の住所を記録するテーブルや1冊の書籍を記録するテーブル
だけで処理が完結するようになっています。
- データベースの理解が先決 -
ここで最も重要なことは、対象となる業務で処理されるデータを、どのような形でデータベースに置き換えるか――ということです。たくさんの情報を種類別に分類し、それらを目的に沿って組み合わせていかなければなりません。
従って、データベースに対する理解が最重要課題だと言えます。データベースの扱いさえ理解できれば、それを扱うプログラミングの作法は自ずと身に付いていきます。しかし、逆にVBのプログラミング作法だけを知っていたところで、データベース・アプリケーションは作れません。
- 業務は1つのテーブルだけで成り立たない -
入門段階でこういったシンプルな構成のデータベースが例に用いられるのは、当然のことながら「分かりやすい」からです。しかし現実の業務は、個人が住所を管理したり蔵書を管理したりするような、単純なものではありません。
従って、1つの業務あるいは1つの処理が1つのテーブルだけで成り立つということは、よほど単純で簡単な仕事でない限り、まずあり得ないことになります。
では、複雑なデータベースとはどんなデータベースなのでしょう? 当然、複数のテーブルで構成されたデータベースということになります。が、ただ漠然と複数のテーブルが1つにまとめられているわけではありません。目的とする業務に関連する複数のテーブルが、一定の意味を成すように組み合わさっているのです。
複数のテーブルを一定の意味を成すように組み合わせる、とはどういうことでしょう？ 先に例として挙げた住所録と蔵書管理を比べてみると、そのヒントが見えてきます。
- 住所録と蔵書管理の決定的な違い -
住所録で用いるテーブルと蔵書管理で用いるテーブルには、決定的な違いがあります。もう一度、両者のテーブルの構造――フィールド構成を見てください。
フィールド構成だけでは分かりにくいかもしれません。実際のデータを示してみましょう。
表 1：住所録のデータ
表 2：蔵書管理のデータ
- 住所録～データの重複がない -
住所録のデータでは、1件1件のレコードが完全に独立していることが分かります。名前や読みはもちろんのこと、電話番号も住所も、まったく同じ値を持つレコードが複数存在していることはありません。
郵便番号は、同じ地域に住んでいれば同じになる場合もあります。学校や町内会の名簿なら、全レコードが同じ値となることもあるでしょう。
電話番号は、同じ家に住んでいない限り同じとはなりません。今や1人に1本電話がある時代ですから、たとえ同じ家に住んでいても、電話番号が同じとは限りませんね。市外局番や市内局番は同じになる場合がありますが、「電話番号」という1つのフィールドがまるごと同じになる可能性は滅多にありません。
同姓同名の人もいるでしょうから、氏名や読みがなでもまれに「まったく同じ値」となることはあります。が、名前が同じでも同一人物ではないので、それらは異なるレコードとなって当然です。
- 蔵書管理～随所にデータの重複がある -
蔵書管理のテーブルはどうでしょう?
こちらは、同じ値が随所に見受けられます。同じ値が登場するフィールドは、
著者、出版社、判型
の3つです。
書名や価格にも同じ値が登場する場合はありますが、書名に関しては住所録の氏名と同じで、たまたま同じタイトルの本があっただけのことであり、価格は数値データなので、重複の可能性はあって当然です。
では、著者・出版社・判型の値も、「たまたま同姓同名の著者」「たまたま同じ名前の会社」なのでしょうか? これは明らかに違います。1人の著者が多数の本を書き、1つの出版社が多数の本を出版しているのですから、あちこちのレコードで重複している著者や出版社の値は、「同一人物・同一の会社」です。
このように蔵書管理のテーブルでは、他のレコードにも同じデータが重複して存在する場合が多々あります。これが住所録との決定的な違いです。
蔵書管理と住所録は一見よく似た構造ですが、現実のデータを当てはめてみると、両者には決定的な違いがあることが分かりました。データの重複が存在する蔵書管理のテーブル構造を、もう少し突き詰めて捕えてみましょう。
- 著者と出版社の情報は切り離せる -
先に示した蔵書管理テーブルの、「著者」フィールドに注目してみましょう。著者は1人の人間であり、世の中にはたくさんの著者が存在しています。従って、著者に関するデータだけでも、1つのテーブルが作れることになります。
但し、著者の情報は個人の使用する一般的な住所録ではないので、あまり詳細なデータは把握できません。以下のようなフィールド構成が考えられます。
著者 読み 代表作 受賞歴
このテーブルが、基本的に住所録の構造と同じものだということは、理解いただけると思います。1件のレコードを構成するフィールドが、同じテーブルの他のレコードと重複する可能性が極めて少ないのです。
同じように、今度は出版社に注目してみましょう。これも、住所録と同じような形でテーブル化できます。
出版社 郵便番号 住所 電話番号
この構造は住所録そのものです。
- 分けたテーブルを1つにまとめる -
これで、蔵書管理に対して「蔵書」「著者」「出版社」3つのテーブルが定義できました。これら3つのテーブルは、図1のような形で互いに関連しています。
図1:3つのテーブルが相互に関連している
作業の中心となるのは、図1の中央にある「蔵書」のテーブルです。「蔵書」のテーブルを構成する要素の中の「著者」フィールドは「著者」のテーブルから、「出版社」フィールドは「出版社」テーブルからそれぞれ値を引き出してくるという形です。
- IDフィールドでレコードを特定する -
上記の構造を、もっとデータベースらしくしてみましょう。データベースでテーブル中の1件のレコードを特定するには、「書名」「著者」といった名前ではなく、IDと呼ばれる番号を用います。
先ほど住所録で同姓同名について触れましたが、IDを使ってレコードを特定すれば、仮に「氏名」フィールドが同じ値(同姓同名)でも、それらが異なる人物に関するレコードであることが明確になります。
「著者」と「出版社」の2つのテーブルにIDフィールドを設けると、フィールド構成は以下のようになります。
・著者テーブル
著者ID 著者 読み 代表作 受賞歴
・出版社テーブル
出版社ID 出版社 郵便番号 住所 電話番号
- テーブルとリレーション -
「蔵書」テーブルでは、この2つのテーブルから「著者」と「出版社」を特定するレコードを指定し、値を引き出す必要があります。そのため「蔵書」テーブルの「著者」と「出版社」のフィールドには、具体的な著者名や出版社名は不要です。
IDを指定すれば、それぞれのテーブルから著者名や出版社名だけではなく、著者の代表作や出版社の住所まで明らかになるからです。
従って蔵書テーブルは、以下のようなフィールド構成となります。
・蔵書テーブル
蔵書ID 書名 著者ID 出版社ID 判型 価格
著者IDで「著者」テーブルの中の1件のレコードを特定するIDを指定すれば、そこから著者・読み・代表作
・受賞歴…といった情報が引き出せます。出版社についても同じです。
例えば、蔵書テーブルに以下のようなレコードがあったとします。
| フィールド |
蔵書
ID |
書名 |
著者
ID |
出版社
ID |
判型 |
価格 |
| データ |
2001 |
パン屋
再就職 |
8005 |
6101 |
文庫判 |
500 |
このレコードだけでは、著者名や出版社名は分かりません。が、先に示した関連づけの構造をたどって
著者を著者テーブルの著者IDに示されたレコード 出版社を出版社テーブルの出版社IDに示されたレコード から引き出してくると、図2のように著者と出版社を特定できます。
図2:IDを頼りにレコードを参照する
このように、IDを頼りに別のテーブルからレコードを見つけだせるようにした構造が「リレーション」(relation)です。1つの情報を複数のテーブルに分け、互いに関連づけて一定の意味を成す仕組みです。
蔵書管理テーブルを解体し、著者と出版社の情報を別のテーブルとしてみました。しかし上記のままでは扱いにくいので、データベースらしい仕掛けを施しておきましょう。
- IDフィールドを設ける -
こうして見つけ出した著者と出版社の情報は、先に示した「蔵書」テーブルの構造では表示できません。「蔵書」テーブルには著者IDと出版社ID、つまりIDの値しか保存されていないからです。
そこで、「蔵書」テーブルを元にリレーションをたどって見つけ出した著者と出版社の情報を提示するための仕掛けが必要になります。そのためのフィールド構成は
蔵書ID 書名 著者ID 著者 出版社ID 出版社 判型 価格
といった形になるでしょう。これに基づいてレコードを表示させれば |
|
蔵書
ID |
書名 |
著者
ID |
著者 |
出版社
ID |
出版社 |
判型 |
価格 |
| 2001 |
パン屋
再就職 |
8005 |
村山歩樹 |
6101 |
文芸
秋秋社 |
文庫判 |
500 |
といった形になります。
また
蔵書ID 書名 著者ID 著者 代表作
出版社ID 出版社 電話番号 判型 価格
といった構成として、さらに著者や出版社に付帯する別の情報(例では著者の代表作と出版社の電話番号)を提示することも可能です。
- ビューとクエリーの働き -
このように、リレーションに基づいて処理の中心となるテーブルのフィールド構成と、それが参照している別のテーブルのフィールドとをまとめ、あたかもそれが1つのテーブルであるかのように提示する機能を「ビュー」(view)と言います。ビューはクエリー(query)によって定義されます。
選択クエリーで、元のテーブルのフィールド構成を操作し、フィールドの並び順を入れ替えたり、不要なフィールドを隠すことができるのをご存じでしょう。これと同じ操作が、複数のテーブルを関連づけた場合にもできるのです。
こうして提示された仮のテーブルを、ダイナセットと呼びます。ダイナセットの構造は、それがディスクに保存されたデータではなく一時的なメモリ上のデータであることを除いて、テーブルとまったく同じです。
テーブルとクエリーによるダイナセットとをあわせて、レコードセットと呼びます。レコードの集合という意味です。クエリーはテーブルに対して操作を行うものと捉えている人もいるかもしれませんが、実際にはクエリーはレコードセットに対して機能します。従って、クエリーで生成したダイナセットに対して、さらにクエリーを用いて加工することも可能です。
蔵書管理を例に、リレーションとクエリーの働きを紹介してきました。これがリレーショナルデータベースの最も基礎的で、かつ重要な機能です。
さらに現実の業務に近い例を考えてみましょう。
- 商品管理のリレーション構造 -
以下のような2つのテーブルがあるとします。
・商品テーブル
商品ID 品名 仕様 単位 仕入単価 販売単価 仕入先ID
・仕入先テーブル
仕入先ID 名称 郵便番号 住所 電話 FAX 担当者
この2つのテーブルから、商品テーブルの仕入先IDを頼りに仕入先テーブルのレコードを特定する
という構造が作れます。さらに
・商品台帳テーブル
台帳ID 商品ID 入庫数 出庫数 残数
というテーブルを用意すれば、図3のようなリレーションの構造が出来上がります。
図 3:商品と仕入先の情報を元に商品台帳の情報を構成する
- 見積業務のリレーション構造 -
また、
・得意先テーブル
得意先ID 名称 郵便番号 住所 電話 FAX 担当者
というテーブルを作り、さらに
・見積書テーブル
見積ID 伝票番号 日付 得意先ID 商品ID 数量 消費税 見積額
といったテーブルを用意すれば、図4のような見積書作成のためのリレーションが構成できます。
図4:さらに得意先テーブルを加えて見積書の情報を構成する
- 静的な情報を記録する～基本テーブル -
仕入先テーブルと得意先テーブルが、基本的に住所録の構造と同じであることに注目してください。リレーションを用いて複雑なデータ構造を分割したテーブルに収める場合、最終的なテーブルの構造は住所録と同じものになります。
このことは、商品テーブルの構造にも当てはまります。商品の場合は住所や電話番号はありませんが、
1種類の商品を表す情報が1件のレコードに収まっているという点で
1人の人物を表す情報が1件のレコードに収まっている住所録と同じ構造です。
このような、処理の最も基本となるテーブルを基本テーブルまたはマスターテーブルと呼びます。住所録は、リレーショナルデータベースの基本テーブルの構造をそのまま利用しているので、データベースの基礎を学ぶ素材として頻繁に用いられるのです。
- 動的な情報を記録する～取引テーブル -
さて、先に示した例の中で、1つだけ傾向の異なるテーブルがありました。見積書テーブルです。他のテーブルと何が違うのでしょう?
他のテーブルは、商品、仕入先、得意先…といった具合に、処理の基本情報としてその値があまり変化しないという性質を持っています。
販売する商品の価格が毎日変わったり、得意先や仕入先がころころと転居したりすることはありません。基本テーブルは、極めて静的な情報を保持するテーブルです。
一方見積の情報は、取り引きが発生するたびに1件ずつレコードが増えて行きます。また、見積内容を修正する場合もあるでしょう。これは見積だけではなく納品書や請求書のデータでもまったく同じです。
こういった、業務を進めるたびにレコードが増えたり、あるいは既存のレコードの内容が書き換えられたりするテーブルを、取引テーブルと呼んでいます。取引テーブルは、極めて動的な性質を持っています。
一見一つの業務記録としてまとめられる情報も、実は基本的ないく種類かの情報の集合であることがお分かりいただけたと思います。データベースのデザインでは、ある情報を複数の基本的な情報に分割し、それを関連づける作業が重要になります。
- 社員情報と健康診断情報の関係 -
同じようにして、今度は社員の情報と健康診断の情報をテーブルにしてみましょう。
・社員テーブル
社員ID 氏名 読み 性別 生年月日 採用年月日…
この社員テーブルは基本テーブルです。これを使って健康診断のデータを記録するテーブルを作るとします。
・健康診断テーブル
社員ID 身長 体重 右視力 左視力…
この健康診断テーブルは基本/取引どちらのテーブルでしょう?
一見すると取引テーブルのように見えますが、基本テーブルです。1件のレコードが1人の社員の健康診断記録を保持しているため、健康診断テーブルの1件のレコードと社員テーブルの1件のレコードとが「1対1で対応」することになります。
つまり健康診断テーブルは、社員テーブルの延長線上にある性質のテーブルだと言えます。
- 社員情報と勤怠情報の関係 -
では、同じ社員テーブルを元にして勤怠状況を記録するテーブルを作ってみましょう。
・勤怠テーブル
勤怠ID 日付 社員ID 出社時刻 退社時刻 休日…
このテーブルも、1件のレコードに1人の社員の勤怠状況が記録されます。が、2番目のフィールドに日付が記録されていることから、
1日のデータに1人の社員が重複することはないが、
別の日のデータには同じ社員のデータが重複して
現れる ことになります。つまり勤怠テーブルのレコードと社員テーブルのレコードは「1対多で対応」しているのです。
図5:異なるテーブルのレコードが1対1で対応している
図6:異なるテーブルのレコードが1対多で対応している
勤怠情報のような構造の場合、毎日社員が出社し、あるいは退社するたびにレコードが増えていきます。従って勤怠テーブルは取引テーブルとなります。
- 1対1の関係と1対多の関係 -
実際には、あるテーブルが基本テーブルか取引テーブルかということは、データベース設計においてさして重要ではありません。ただ、異なるテーブルのリレーション構造が
1対1で対応するか
1対多で対応するか
を意識しておいてください。
但し、
1対1だから基本テーブル
1対多だから取引テーブル という訳ではありません。
ピザの商品名とトッピング → 1対多
→ ピザもトッピングも基本テーブル
自動車とオプション部品 → 1対多
→ 自動車もオプション部品も基本テーブル
社内ボウリング大会の成績 → 1対1
→ 社員は基本テーブル、成績は取引テーブル
など、様々な関係が存在します。
- 手作業で言えば台帳と伝票の関係 -
ここで、手作業で処理を行なう場合を考えてみましょう。例えば商品管理なら、以下のような帳簿類を用いるはずです。
商品の基本情報:商品台帳
商品の入出庫状況:入出庫台帳
入庫や出庫の記録:入庫伝票と出庫伝票
～台帳と付く帳簿は基本テーブルです。～伝票と付く帳票類は取引テーブルとなります。これは社員管理なら
社員の基本情報:社員名簿
健康状態:健康診断記録台帳
勤怠状況:出勤簿・勤怠記録表
となるでしょう。この場合～名簿は基本テーブル、～簿、 ～表などは取引テーブルと言えます。
これはすべての場合に当てはまるわけではありませんが、多くの場合、台帳や名簿類は基本テーブルであり、伝票や追記式の一覧表は取引テーブルとなります。
そして、取引テーブルは常に基本テーブルのレコードを参照し、一定の意味を成す情報を構成します。そのための関連付けの仕組みがリレーションであり、リレーションされたテーブル群から最終的な情報を提示する仕組みがクエリーです。
今月は、かなり理屈っぽいお話になってしまいました。VBプログラミングから離れ、現実の業務をデータベース化する際の考え方の領域に入ったので、サンプルプログラムもありません。図を見ながら頭の中で考え、リレーションやテーブルの関係などを把握しておいてください。
あなたが実際に仕事で伝票や帳簿を使っているのなら、そこに記録されるデータをテーブル化し、リレーションの構造を考えてみるとよいでしょう。常に現実の情報のあり方を意識し、それをデータベース化することを考える訓練をしてください。
|
