データベースソフトを使ってシステムを組むためのノウハウをまとめました。
ご自分でシステムを組まれる際の要件定義にお役立てください。
| DB便利帳 | |||
|---|---|---|---|
| 基本用語 | グループ化 | リレーション | |
| データ形式 | データの削除 | ||
| データの鮮度管理(1) | データの鮮度管理(2) | データの鮮度管理(3) | 使用頻度の管理 |
基本用語 |
データベースでデータを管理するためにはテーブルの構造を理解する必要があります。データベースのテーブル構造は横一列に一カタマリの情報となるようにデータを並べていきます。これをレコードと呼びます。
レコードはデータを項目順に並べたものです。一行のレコードの中にあるデータの内容を決めるのがフィールドです。テーブル内では同じ種類のデータが一列に並ぶように表形式で管理するようになります。横の列がレコードという一カタマリの情報の集合体で縦の列が項目ごとのデータが入っているフィールドとなります。
クエリはテーブルの中から、欲しいデータを選択するものです。データベースソフトが他のソフトと違うのはSQLという形式の条件式を使えるところにあります。データベースではテーブルにデータを格納しています。テーブルはレコードを一カタマリの情報として管理しますので、レコード内のフィールドにある情報を手がかりにして、レコードを探し出すことができます。
クエリとは一般的にSQLのSelect文といわれる条件式のことを指しますが、MS-Accessなどでは条件式で抽出されたデータをテーブルのように整理したものをクエリと呼んでいます。これはMS-Accessがクエリに関して強力なGUIを持っているので、テーブルと同じような管理ができるようになっているからです。
クエリの正体はSQLの代表的な条件文であるSelect文というものになります。この文は特徴として以下のような書き方をします。
| ○○テーブルの□□フィールドにあるデータを、 |
| ○○テーブルから、 |
| △△フィールドのデータが××という条件に合うものを選択する。 |
具体的に書くと次のようになります。
| Select ○○テーブル.□□フィールド | (○○テーブルの□□フィールドにあるデータを) |
| From ○○テーブル | (○○テーブルから) |
| Where △△フィールド=×× | (△△フィールドのデータが××という条件) |
データベースソフトにはフォームといわれる入力画面などを作ることがきる機能をサポートしています。フォームはデータ入力はもちろん、出力する帳票をつくることができます。
フォームはテーブルにあるレコードの中に並べられたデータを整理して表示するシートです。ですからフォームには表示する元のデータがテーブルの中に存在します。これをフォームのデータソースといいます。フォーム全体に対してはフォームソース、後述するコントロールに対してはコントロールソースともいいます。
フォームを作る作業を簡単に言うとデータソースを表示する場所をフォーム上に並べていくということになります。具体的には表示枠やそのラベルを作って並べていくようになります。このときに使う表示枠やラベルのことを総称してコントロールといいます。実際には、文字ラベル1つにもデータを書き換えたりするためのプログラムを記述して、その文字ラベルに触ったりしたときにデータを書き換えるという役割を持たせることができるので、フォーム上に並べることができるもの全てがコントロールと呼ばれます。コントロールにはデータソースを表示させるボックス、プログラムを作動させるためにクリックさせるボタンなどがあります。
テーブルに入力するデータには何度も繰り返して使うことが多いものがあります。使用頻度の高いデータはマスターデータとしてマスターテーブルに管理しておきます。このようにしておくと、データを扱うときにマスターデータのリストから選択して使えるようになり、便利です。マスターテーブルに保存するデータは、
| ①WHO | 人 |
| ②WHAT | モノ |
| ③WHERE | 場所 |
| ④HOW | 手段 |
に関するデータをそれぞれ別々のテーブルに管理して保存するようにします。
過去に起こったこと、また、未来に起こることを表現するには文が必要です。データベースではマスターテーブルにあるデータを組み合わて定型文を作り事象を表現します。ワークテーブルは定型文に必要な要素を1つのレコードとして扱うというデータ管理のためのテーブルです。
| ①WHO | 人 | ←マスターテーブルから選択 |
| ②WHAT | モノ | ←マスターテーブルから選択 |
| ③WHERE | 場所 | ←マスターテーブルから選択 |
| ④HOW | 手段 | ←マスターテーブルから選択 |
| ⑤WHEN | 時間 | ←ワークテーブルで入力 |
マスターテーブルにある①～④までのデータを並べて⑤の時間の要素を加えると４W１Hの文を完成させることができます。このデータの組み合わせをレコードとして格納するのがワークテーブルということになります。
グループ化 |
多数のデータレコードを扱う場合には、情報をグループ化して情報を絞り込みやすいように管理します。データベース上でこれを行うには、グループ用のフィールドをテーブルに作成します。そしてそのフィールドにグループ名を入力すればOKです。
例えばここにKYOJYOU1とうテーブルがあります。これを東というグループと西というグループにわけるには以下のようにします。
| フィールド1 | フィールド2 |
|---|---|
| 石田 | 佐和山 |
| 加藤 | 熊本 |
| 最上 | 山形 |
| 上杉 | 若松 |
| 伊達 | 岩出山 |
先ずグループ名というフィールドを追加します。グループ名フィールドにそれぞれ分けたいグループ名を入力します。
| フィールド1 | フィールド2 | グループ名 |
|---|---|---|
| 石田 | 佐和山 | 西 |
| 加藤 | 熊本 | 東 |
| 最上 | 山形 | 東 |
| 上杉 | 若松 | 西 |
| 伊達 | 岩出山 | 東 |
このようにしておくとテーブル：KYOJYO1-1から東グループだけを取り出すことができます。
このテーブルから東グループだけを抽出するには
| [KYOJYO1-1]テーブルの[フィールド1]と[フィールド2]にあるデータを |
| [KYOJYO1-1]テーブルから、 |
| [グループ名]フィールドのデータが[東]という条件に合うものを選択する。 |
というSelect文を使います。このSelect文の書き方は以下のようになります。
| Select [KYOJYO1-1].[フィールド１],[KYOJYO1-1].[フィールド2] |
| From [KYOJYO1-1] |
| Where [グループ名]=[東] |
このSelect文を使ったクエリによって表示される結果は次のようになります。
| フィールド1 | フィールド2 | グループ名 |
|---|---|---|
| 加藤 | 熊本 | 東 |
| 最上 | 山形 | 東 |
| 伊達 | 岩出山 | 東 |
グループ名は、別のテーブルを使って管理するのが一般的ですが、ルックアップ機能があるDBソフトの場合は既にグループ名フィールドにあるグループ名をルックアップ機能を使って拾うこともできます。ただし、この方法ではグループ名を変更したりしたいときなど、グループ名そのものの使い勝手がよくありません。グループ名は別のテーブルに作成するようにしましょう。
使用頻度順に並べる
最近使った順に並べる
データの鮮度管理(1)タイムスタンプ |
どのようなデータも古いものは使わなくなるものです。システムの使い勝手が悪くなる原因のひとつとして、古いデータの存在があります。使わなくなったデータは必要なデータを探し出す邪魔になるのでより分けられなければなりません。そこでデータをより分ける基準として有効なのはデータの作成日です。これを実現するのに必要なのはデータレコードにタイムスタンプを打つということです。
データベース上でタイムスタンプを打つには入力日のフィールドを作成してレコードを生成する際にPCやモバイル端末が持っている時計機能からデータを取得して入力されるようにします。
トランザクションを管理することができる高機能なデータベースソフトを使う場合でもこの方法のを使います。タイムスタンプを他のレコードと同列に扱えるようにしておくことが重要です。なぜならばタイムスタンプをパラメーターとしたクエリを使ってデータを整理することができるからです。
データの鮮度管理(2)データの期限設定 |
データに期限を設定するにはレコード上に期限の日付、時間を入力するフィールドを設定して管理することが必要です。レコード上には発効日と失効日という2つのフィールドを設定する必要があるということです。有効期間○○日として設定する場合でも入力フォームのプログラムで発効日と失効日が同時にレコードに入力されるようにします。そして、もう1つ重要なことは、期限を設定しているレコードを呼び出して使う場合に失効日に関する条件を必ず入れるということです。この条件の書き方の例が以下のようになります。
もし、○○テーブルの[失効日](フィールド)にあるデータが、現在より過去のものである場合は、
メッセージを表示。「××のデータは使えません。」よろしいですか？
答えが「Yes」の場合は○○テーブルのデータを編集する処理へ。
答えが「No」の場合はこの処理は中止。
それ以外の場合は
次の処理へ。
データの期限設定をする場合は、失効したデータレコードの代替となるデータレコードが必ず存在しなければなりません。なぜならば、期限が切れて使えない情報の代わりに使える情報がないと、システムを使っているユーザーの業務が滞るからです。
その上いくつものレコードからデータを拾って計算をするようなプログラムを組んだ場合、期限の切れたデータに代わるデータが取得できずに計算が止まってしまい、システム全体がダウンすることもあります。
データの鮮度管理は時としてシステム管理者の仕事の範疇を超える場合が発生します。システムに入っているデータについてはユーザー側も知識をもつ必要があります。
データの鮮度管理(3)データの時限管理 |
レコード内にある特定のフィールドに対して時限管理をするには、テーブルのリレーションを使って行います。
例えば、ガソリンの価格などは週ごとに変化するので、週ごとに単価を改定していく必要があります。
これを実現するためにはガソリンの基本情報が入っているテーブルから、単価のフィールドを切り離して別のテーブルを作ります。そして基本情報の入っているテーブルのレコードと別にした単価情報テーブルのレコードに関連性を持たせて管理します。
| 製品名 | 単価 | 入力日 | 発効日 | 失効日 |
|---|---|---|---|---|
| レギュラー | 111 | 2008/11/14 | 2008/12/1 | 2008/12/7 |
| ハイオク | 121 | 2008/11/14 | 2008/12/1 | 2008/12/7 |
| 軽油 | 95 | 2008/11/14 | 2008/12/1 | 2008/12/7 |
基本情報テーブルの同一レコードで発効日失効日を管理すると、クエリなどで失効日を過ぎたレコードを除外して使うことができません。このような場合は、常に失効日がくる前に失効日を書き換えるという作業をしないと、基本情報のレコード自体が存在しないことにになります。
| No. | 製品名 | 入力日 |
|---|---|---|
| 1 | レギュラー | 2008/11/14 |
| 2 | ハイオク | 2008/11/14 |
| 3 | 軽油 | 2008/11/14 |
| レコードNo. | 基本情報No. | 単価 | 入力日 | 発効日 | 失効日 |
|---|---|---|---|---|---|
| 1 | 1 | 140 | 2008/11/28 | 2008/12/1 | 2008/12/7 |
| 2 | 2 | 150 | 2008/11/28 | 2008/12/1 | 2008/12/7 |
| 3 | 3 | 135 | 2008/11/28 | 2008/12/1 | 2008/12/7 |
| 4 | 1 | 121 | 2008/12/4 | 2008/12/8 | 2008/12/14 |
| 5 | 2 | 110 | 2008/12/4 | 2008/12/8 | 2008/12/14 |
| 6 | 3 | 103 | 2008/12/4 | 2008/12/8 | 2008/12/14 |
基本情報から単価情報を分離して別のテーブルにしておくと、失効日を過ぎても基本情報レコードは存在するので、単価情報以外の情報は使用できます。
また、このように管理しておけば1つの基本情報のレコードに対して複数の単価情報レコードを持つことが可能です。失効日を過ぎたら自動的に継続する期間のレコードを書き加えるプログラムを書いて運用すれば、事前のデータを失うことなく、新しいデータを更新の有無にかかわらず、使用することも可能になります。
実際のところ時限管理は、システムユーザーにとって非常に煩わしいものとなります。そこで期限の自動延長や、ログイン直後にデータ管理画面を開いて、ユーザーにデータ更新を強制するなどの方法があります。
データの使用頻度 |
データが使用されているかどうかをチェックするのは、使いやすいシステムを作るために必要なステップです。データベースの中には、よく使われるデータと、ほとんど使われることがないデータが同列に存在するようになっています。
そこで、データが使われた回数を数えて、データを使用するときに表示される順番に反映させる仕組みを入れておくと使用者がよく使うデータほど早く探すことができるようになり、システムの使い勝手は向上します。
これを実装するためには、レコードに使用回数をデータとして保存するフィールドを作り、そのデータが使われる度にこのフィールドの数値がカウントアップされるようにします。
このフィールドの数値が高ければ高いほど使用頻度が高いデータと言えるので、データ検索をする際、このフィールドを降順に検索して表示するようにすれば、よく使うデータほど上位に表示されるようになるのでシステムの使い勝手がよくなります。
この他にも、このフィールドのデフォルトの数値を０にしておき、検索のSQLで０より大きいものだけを抽出するようにすれば、一度も使われていないデータが除外することができます。
データの使い勝手は並び順に意外と左右されます。データの内容をよく見極めて使いやすい表示をするようにしましょう。
データ形式 |
データベースで扱うデータの形式はは一般的に以下の表のように分類されます。
| No. | データ形式 | 概要 |
|---|---|---|
| 1 | テキスト型 | 文字列といわれますが、ようは文章や単語を書き表すことのできるデータの形式です。 |
| 2 | メモ型 | テキスト型とほぼ同じですが、長い文章を入力することができるようにデータベース内で格納領域を多く裂いておく形式です。 |
| 3 | 数値型 | 数を扱うデータ形式です。データベースソフトによって使用できる範囲が異なります。数値型には大きく分けて整数を扱うInteger（インテグラル）と小数点以下の単位を保持するDouble（ダブル＝倍精度浮動小数点）があります。データベースによってはIntegerより扱える桁数が多いLong（長整数）をサポートしています。その場合には長整数を使用します。 |
| 4 | 日付/時刻型 | 日付と時間を扱うデータ形式です。 |
| 5 | YES/NO型 | 二者択一式のデータ形式になります。ブーリアン型ともいわれます。 |
ここに挙げたデータ形式以外にもバイト型やOLEオブジェクト型などのデータ形式がありますが、これらはデータベースソフトから他のソフトウェアに渡すために使用するものになります。従って個人レベルでシステムを組む場合には先ず、上記の五つのデータ形式の特徴と扱い方を把握する必要があります。
リレーション |
データベースの使い方としてリレーションをつくるということがあります。
リレーションとは二つのテーブルにあるレコードを関連付けることです。
データベースでのリレーションは基本的に親子関係になります。つまり、親となるレコードが格納される親テーブルと子となるレ コードが格納される子テーブルの関係になります。この場合、親テーブルのレコードに対応する子テーブルのレコードは一対一または一対多数となります。
具体的には、親テーブルのレコードを特定できるフィールドを子テーブルのフィールドにコピーするようにすればOKです。ここで親テーブルのレコードを特定できるフィールドというのは、レコード内で完全に重複しないものであればなんでもかまいませんが、人間が入力したデータで、完全に重複しないようにするのは不可能です。
そこで、必要となるのがインデックスといわれるフィールドです。データベースソフトには必ずインデックスフィールドを作るための機能がついています。これは数値型のフィールドに、1から順に自然数を自動で入力する機能で、オートナンバーと呼ばれているものです。
子テーブルには親テーブルのインデックスを入力するフィールド作っておき、子レコードを入力する際に自動でコピーされるようにしておきます。
データの削除 |
不要なデータは削除したくなるものですが、データベースでは、安易にデータを消去するのはおススメできません。データベースではユーザーが不要になった情報でも、テーブルの中に置いておく方がよいのです。
野口悠紀雄さんではないですが、「整理するな！ 検索せよ！」がデータベースの正しい使い方です。
システム開発でよく行われているのが”Delete Flag”というフィールドを作成しておき、ここに入るデータによってレコードの要、不要を分別するやり方です。
| DB便利帳 | |||
|---|---|---|---|
| 基本用語 | グループ化 | リレーション | |
| データ形式 | データの削除 | ||
| データの鮮度管理(1) | データの鮮度管理(2) | データの鮮度管理(3) | 使用頻度の管理 |
