データベース設計の基本

この記事の内容

• 知っておく必要のあるいくつかのデータベース用語

• 適切なデータベース設計とは

• デザインの手順

• データベースの目的を決定する

• 必要な情報を検索して整理する

• 情報をテーブルに分割する

• 情報の項目を列に変える

• 主キーを指定する

• テーブル リレーションシップを作成する

• デザインを調整する

• 正規化ルールを適用する

知っておく必要のあるいくつかのデータベース用語

Access は、会計士のパッドやスプレッドシートを思わせる行と列のリストを 表に整理します。 単純なデータベースでは、テーブルが 1 つだけの場合があります。 ほとんどのデータベースでは、複数のデータベースが必要になります。 たとえば、製品に関する情報を格納するテーブル、注文に関する情報を格納する別のテーブル、顧客に関する情報を含む別のテーブルがあるとします。

各行は正しくはレコードと呼ばれ、各列は正しくはフィールドと呼ばれます。 レコードとは、何かに関する情報を、わかりやすく一貫性のある方法でまとめたものです。 フィールドとは、すべてのレコードが持つ、アイテムの種類 (1 項目の情報) です。 たとえば、製品テーブルでは、各行または各レコードに 1 製品に関する情報があります。 各列または各フィールドは、製品名や製品価格など製品に関する情報の一部を保持します。

ページの先頭へ

適切なデータベース設計とは

データベースの設計手順には、いくつかの原則があります。 まず、情報の重複 (冗長データとも呼ばれる) は、スペースを無駄にし、エラーや不整合を発生させる可能性を高くするので、好ましくありません。 2 番目に、情報の正確さと完全性が重要です。 データベースに不正な情報があると、そのデータベースから情報を取得するすべてのレポートの情報も不正になります。 その結果、それらのレポートを使用して行われるすべての決定は、不正になります。

したがって、適切なデータベース設計は次のようになります。

• データの冗長性を少なくするために、情報が主題ごとのテーブルに分割されている。

• 必要に応じてテーブル内の必要な情報を結合できるように Access に情報を提供できる。

• 情報が正確で整合性が取れていることが保証されている。

• データ処理とレポート作成のニーズに対応している。

ページの先頭へ

設計プロセス

設計プロセスは、次の手順で構成されています。

• データベースの目的を決定する    

  これにより、残りの手順を準備することができます。

• 必要な情報を見つけて整理する     

  製品名や注文番号など、データベースに記録する必要があるすべての種類の情報を収集します。

• 情報をテーブルに分割する    

  情報の項目を主なエンティティや主題 (製品、注文など) に分割します。 各主題がテーブルになります。

• 情報の項目を列に変える    

  各テーブルに格納する情報を決定します。 各項目がフィールドになり、テーブルの列として表示されます。 たとえば、従業員テーブルには、姓や雇用日などのフィールドが含まれます。

• 主キーを指定する    

  各テーブルの主キーを選択します。 主キーとは、各行を一意に識別するために使用される列です。 例として、製品 ID や受注 ID があります。

• テーブル リレーションシップを設定する    

  各テーブルを確認し、1 つのテーブルのデータと他のテーブルのデータとの関係を決定します。 リレーションシップを明確にするため、必要に応じてテーブルにフィールドを追加したり、新しいテーブルを作成します。

• 設計を調整する    

  設計にエラーがないか分析します。 テーブルを作成し、サンプルのレコードをいくつか追加します。 テーブルから必要な結果を得られるか確認します。 必要に応じて設計を調整します。

• 正規化ルールを適用する    

  データの正規化ルールを適用して、テーブルの構造が正しいか確認します。 必要に応じてテーブルを調整します。

ページの先頭へ

データベースの目的を決定する

データベースの目的、用途、その使用者などのデータベースの目的を用紙に書き出すとよいでしょう。 たとえば、在宅ビジネス用の小さいデータベースが必要な場合は、"メーリング リストやレポートを生成するために、顧客データベースに顧客情報のリストを保持する" というような簡単な目的を書き出します。 企業の設定でよくあるように、データベースがより複雑であったり、多数のユーザーが使用する場合、目的が 1 段落にも及ぶことが多く、各ユーザーがそのデータベースをいつ、どのように使用するかを含める必要があります。 設計手順全体を通して参照できる、完成度の高いミッション ステートメントを作ることが目的です。 そのようなステートメントがあると、意思決定のときに目的に焦点を絞ることができます。

ページの先頭へ

必要な情報を検索して整理する

必要な情報を見つけて整理するには、既存の情報から始めます。 たとえば、台帳に発注書を記録したり、ファイル キャビネット内の用紙フォームに顧客情報を保持したりできます。 これらのドキュメントを収集し、表示される各種類の情報 (フォームに入力する各ボックスなど) を一覧表示します。 既存のフォームがない場合は、代わりに顧客情報を記録するフォームを設計する必要があるとします。 フォームにはどのような情報を入力しますか? どのような入力ボックスを作成しますか? これらの各項目を特定して一覧表示します。 たとえば、現在、顧客リストをインデックス カードに保持するとします。 これらのカードを調べると、各カードが顧客の名前、住所、市区町村、州、郵便番号、電話番号を保持している可能性があります。 これらの各項目は、テーブル内の潜在的な列を表します。

リストを準備する場合、最初から完璧を目指す必要はありません。 代わりに、頭に浮かんだものを順次列挙していきます。 そのデータベースを使用するユーザーが他にもいる場合、そのユーザーにアイデアを求めます。 リストは後で調整できます。

次に、データベースから生成する可能性があるレポートまたはメーリングの種類を検討します。 たとえば、製品販売レポートに地域別の売上を表示したり、製品の在庫レベルを示す在庫の概要レポートを表示したりできます。 また、販売イベントを発表したり、プレミアムを提供したりする顧客に送信するフォームレターを生成することもできます。 あなたの心の中でレポートを設計し、それがどうなるかを想像してください。 レポートにはどのような情報を配置しますか? 各項目を一覧表示します。 フォーム文字と、作成する可能性がある他のレポートにも同じ操作を行います。

必要なレポートやメールを念頭に入れると、データベースに入れる必要のある項目を識別する助けとなります。 たとえば、顧客がオプトイン (またはオプトアウト) することができる、定期的に送信されるメールによる更新情報があり、そのオプトインしている登録者のリストを印刷するとします。 その情報を記録するには、顧客テーブルに “電子メールの送信” 列を追加します。 顧客ごとに、このフィールドに、”はい” や ”いいえ” を設定します。

顧客にメールを送信する必要がある場合、もう 1 つ記録する項目があります。 顧客が電子メール メッセージを受信することを希望している場合、その送信先の電子メール アドレスも必要になります。 したがって、顧客ごとに電子メール アドレスを記録しなければなりません。

各レポートまたは出力リストのプロトタイプを作成し、レポートを生成するために必要な項目を検討することをお勧めします。 たとえば、フォームレターを調べると、いくつかのことが思い浮かびます。 あいさつ文を開始する "Mr.、"Mrs"、"Ms" 文字列など、適切なあいさつ文を含める場合は、あいさつ項目を作成する必要があります。 また、通常、「親愛なる」ではなく「親愛なるスミスさん」の手紙を始めるかもしれません。 Sylvester Smith 氏"。 これは、通常、姓を名とは別に保存することを示唆しています。

各情報は、有用な最小単位に分割することが重要であることを覚えておいてください。 名前に関してであれば、すぐに使用できるように、名と姓の 2 つの部分に分割します。 たとえば、姓でレポートを並べ替える場合、顧客の姓が別に保存されていると便利です。 通常、情報を項目 (顧客の名など) で並べ替え、検索、計算、またはレポート作成する場合、その項目を別のフィールドに保存する必要があります。

データベースが回答する可能性がある質問について考えます。 たとえば、先月、おすすめ製品の売上をいくつ閉じていましたか? 最高の顧客はどこに住んでいるのですか? あなたのベストセラー製品のサプライヤーは誰ですか? これらの質問を予想すると、記録する追加項目をゼロにするのに役立ちます。

これらの情報を収集したら、次の手順に移ります。

ページの先頭へ

情報をテーブルに分割する

情報をテーブルに分割する場合、主要なエンティティまたは主題を選択します。 たとえば、製品の販売データベース用の情報を判別して整理すると、準備段階のリストは次のようになります。

これの主要なエンティティは、製品、仕入先、顧客、および注文です。 したがって、製品、仕入先、顧客、受注の事実情報の 4 つのテーブルで開始するのが理にかなっています。 このリストですべてではありませんが、開始地点としては適切です。 設計が正しく機能するまで、このリストを調整し続けてください。

暫定的なリスト項目を最初に見直すとき、前の図の 4 つのテーブルではなく、すべてを 1 つのテーブルに配置してしまいたくなる場合もあります。 これがなぜよくないのかこれからここで説明します。 次のテーブルを少し考えてください。

ここでは、各行に、製品とその仕入先の両方に関する情報が含まれています。 同じ仕入先から仕入れられた製品が複数ある場合、仕入先名と住所の情報を何度も繰り返す必要があります。 これはディスク領域の無駄遣いです。 代わりに、仕入先の情報を個別の仕入先テーブルに 1 回だけ記録し、そのテーブルを製品テーブルに関連付けるのがより適切な方法です。

この設計の 2 番目の問題は、仕入先に関する情報を修正する必要が生じた場合に発生します。 仕入先の住所を変更する必要があるとしましょう。 仕入先の住所は複数の場所に表示されているので、ある場所では住所を変更したのに、別の場所では無意識に変更を忘れてしまうことも考えられます。 仕入先の住所を 1 か所だけに記録すれば、この問題は解決されます。

データベースを設計する際、それぞれの事実は常に 1 度のみ記録します。 特定の仕入れ先の住所など、同じ情報を複数回繰り返し出現している場合、その情報は別のテーブルに配置します。

最後に、Coho Winery から仕入れている製品が 1 つしかなく、その製品を削除したいが、仕入先の名前と住所の情報は残しておきたいとしましょう。 仕入先情報を消失せずに製品レコードを削除するにはどうすればよいでしょうか。 それはできません。 各レコードには、仕入先に関する事実に加えて製品に関する事実情報が含まれているので、一方を削除しないで他方を削除することは不可能です。 これらの事実情報を分けて保存するには、このテーブルを 2 つのテーブル (1 つ目は製品情報、2 つ目は仕入先情報) に分割します。 このようにすると、製品レコードを削除した場合、製品に関する事実情報だけが削除され、仕入先に関する事実情報は削除されません。

テーブルの主題を選択したら、そのテーブルの列にはその主題の事実情報のみが含まれるようにする必要があります。 たとえば、製品テーブルには、製品の事実情報のみを格納します。 仕入先の住所は仕入先の事実情報であり、製品に関する事実情報ではないため、それは仕入先テーブルに属します。

ページの先頭へ

情報の項目を列に変える

テーブルの列を決める場合、テーブルに記録する主題でどのような情報を追跡する必要があるかを決定します。 たとえば、顧客テーブルの列を作るときは、名前、住所、市区町村/都道府県/郵便番号、電子メールの送信、敬称、電子メール アドレスから始めるのがよいでしょう。 テーブルの各レコードには同じ列のセットがあるので、レコードごとに名前、住所、市区町村/都道府県/郵便番号、電子メールの送信、敬称および電子メール アドレス情報を格納できます。 たとえば、住所列には顧客の住所があります。 各レコードには 1 人の顧客のデータが含まれており、住所フィールドにはその顧客の住所が含まれています。

各テーブルに入れる最初の列のセットを決めてから、後で列をさらに調整できます。 たとえば、顧客名を姓と名の 2 つの別の列で格納すれば、それらの列のみで並べ替え、検索、インデックス付けを行えます。 同様に、住所は実際には、住所、市区町村、都道府県、郵便番号、国/地域の 5 つの別のコンポーネントで構成されるので、これらも別の列に格納するのが理にかなっています。 たとえば、都道府県で検索、フィルター処理または並べ替え操作を実行する場合は、都道府県情報を別の列に格納します。

また、データベースには国内情報のみを入れるか、同様に海外の情報も入れるか考える必要があります。 たとえば、海外の住所を格納する場合、都道府県ではなく地域列の方が、国内の都道府県と他国の地域を入れることが可能なのでより適しています。 同様に、海外の住所を格納する場合、Zip コード (米国の郵便番号) を格納するより、郵便番号の方が理にかなっています。

次に、列を決定する場合のヒントをいくつか示します。

• 計算されたデータは入れない    

  ほとんどの場合、計算結果をテーブルに含めません。 代わりに、結果を表示するときに Access に計算を実行させます。 たとえば、データベース内の製品の分類項目ごとの注文総数の小計を示すレポートがあるとします。 しかし、どのテーブルにも注文総数の小計列がないとします。 その代わり、製品テーブルには各製品の注文総数を格納する注文総数列があります。 Access はこのデータを使用して、レポートを出力するたびに小計を計算します。 小計自体はテーブルに格納しないようにします。

• 情報は最小限の論理単位で格納する    

  完全な名前、または製品名と製品の説明を 1 つのフィールドにしたい場合があります。 フィールドに複数の種類の情報を組み合わせると、後で個々のファクトを取得することは困難です。 情報を論理部分に分割してみてください。たとえば、名と姓、製品名、カテゴリ、説明に個別のフィールドを作成します。

各テーブルのデータ列を調整したら、各テーブルの主キーを選択できます。

ページの先頭へ

主キーを指定する

各テーブルには、テーブルに格納されている各行を一意に識別する列 (または一連の列) を含める必要があります。 これは、通常、従業員の ID 番号やシリアル番号などの一意の番号です。 データベース用語では、この情報をテーブルの主キーと呼びます。 Access では、主キー フィールドを使用して、複数のテーブルのデータをすばやく関連付けて、データを提供します。

カタログの各製品を一意に識別する製品番号などの一意の識別子が既にテーブルにあり、この列の値がレコードごとに必ず異なる場合、その識別子をテーブルの主キーとして使用できます。 主キーには、同じ値は使用できません。 たとえば、名前は一意ではないので、人名は主キーにしません。 同じテーブルに同名の人が 2 人いることはよくあります。

主キーには必ず値が必要です。 列値がある時点で割り当て解除される場合があったり、それが不明な (値がない) 場合、主キーのコンポーネントとして使用することはできません。

主キーは値が変わらないものを常に選択します。 テーブルを複数使用するデータベースでは、テーブルの主キーを他のテーブルの参照として使用できます。 主キーが変わった場合、そのキーが参照されているすべての箇所でそれが変更される必要があります。 変わることがない主キーを使用すると、主キーを参照する他のテーブルと同期されない可能性は減ります。

主キーには、多くの場合、任意の一意の数値が使用されます。 たとえば、各注文には一意の注文番号が割り当てられます。 注文番号の唯一の目的は、注文を識別することです。 一度割り当てると、それが変わることはありません。

適切な主キーとなる列または列の組み合わせが思い浮かばない場合、オートナンバー データ型の列の使用を検討してください。 オートナンバー データ型を使用すると、Access が自動的に値を割り当てます。 このような識別子には、それが表す行を説明する事実情報は含まれません。 事実情報を含まない識別子は、それが変わることがないため、主キーとして使用するのに最適です。 電話番号や顧客名など、行の事実情報を含む主キーは、事実情報自体が変わる可能性があるため、変わる可能性が高くなります。

1.オートナンバー データ型に設定された列は、多くの場合、主キーに適しています。 製品 ID が 2 つ同一であることはありません。

2 つ以上のフィールドを組み合わせて使用して、テーブルの主キーにすることもできます。 たとえば、注文の明細を格納する受注明細テーブルの主キーとして、2 つの列 (受注 ID と製品 ID) を使用するとします。 主キーに複数の列が使用される場合、これは複合キーと呼びます。

製品の販売データベースでは、次の各テーブルにオートナンバー列を作成して主キーにできます。製品テーブルの製品 ID、受注テーブルの受注 ID、顧客テーブルの顧客 ID、仕入先テーブルの仕入先 ID。

ページの先頭へ

テーブル リレーションシップの作成

これで情報をテーブルに分割できたので、意味のある方法で情報を再度結合する方法が必要です。 たとえば、次のフォームは、いくつかのテーブルからの情報を含んでいます。

1. このフォームは、[得意先] テーブルと、

2. ...従業員テーブル...

3. ...受注テーブル...

4. ...製品テーブル...

5.… 受注明細テーブルからのものです。

Access は、リレーショナル データベース管理システムです。 リレーショナル データベースでは、情報を主題ごとに別のテーブルに分割します。 そして、テーブルのリレーションシップを使用して、必要に応じて情報を結合します。

ページの先頭へ

デザインを調整する

必要なテーブル、フィールド、リレーションシップを作成し、サンプル データをテーブルに設定し、クエリの作成、新しいレコードの追加などの情報を操作する必要があります。 これにより、潜在的な問題が浮き彫りになります。たとえば、デザイン フェーズで挿入し忘れた列を追加する必要がある場合や、重複を削除するために 2 つのテーブルに分割する必要があるテーブルがある場合があります。

データベースを使用して、必要な回答を得られるかご確認ください。 フォームとレポートの下書きを大まかに作成し、期待しているデータが表示されるか確認します。 不要なデータの重複を探し、それを見つけたら、設計を変更してそれを削除します。

最初のデータベースを試すと、おそらく改善点が見つかるでしょう。 次に確認すべき、いくつかの事柄を示します。

• 列を忘れたのですか? その場合、情報は既存のテーブルに属していますか? 他の何かに関する情報である場合は、別のテーブルを作成する必要がある場合があります。 追跡する必要があるすべての情報項目の列を作成します。 他の列から情報を計算できない場合は、新しい列が必要になる可能性があります。

• 既存のフィールドから計算できるため、列は不要ですか? たとえば、小売価格から計算された割引価格など、他の既存の列から情報項目を計算できる場合は、通常、それを行い、新しい列を作成しないようにすることをお勧めします。

• いずれかのテーブルに重複する情報を繰り返し入力していますか? その場合は、テーブルを 1 対多リレーションシップを持つ 2 つのテーブルに分割する必要があります。

• 多数のフィールド、限られた数のレコード、および個々のレコードの空のフィールドが多数あるテーブルはありますか? その場合は、テーブルの再設計を考慮して、フィールドが少なく、レコードが増えるようにします。

• 各情報項目は、その最小の有用な部分に分割されていますか? 情報の項目に対してレポート、並べ替え、検索、または計算を行う必要がある場合は、その項目を独自の列に配置します。

• 各列には、テーブルの件名に関するファクトが含まれていますか? テーブルのサブジェクトに関する情報が列に含まれていない場合は、別のテーブルに属します。

• テーブル間のすべてのリレーションシップは、共通フィールドまたは 3 番目のテーブルによって表されますか? 一対一リレーションシップと一対多リレーションシップには、共通の列が必要です。 多対多リレーションシップには、3 番目のテーブルが必要です。

正規化ルールを適用する

設計の次の手順では、(正規化ルールと呼ばれることもある) データの正規化ルールを適用します。 これらのルールを使用すると、テーブルの構造が正しいか確認することができます。 データベースの設計にこのルールを適用する手順は、データベースの正規化または単に正規化と呼ばれています。

正規化は、すべての情報項目を表現し、仮の設計に到達した後に便利です。 これで、情報項目を正しいテーブルに分割したかを確認することができます。 正規化でできないことは、まず正しいデータ項目がすべてあることを確認することです。

ルールは連続して適用し、各手順で設計が "正規形" と呼ばれる形式になることを確認します。 正規形には、第 1 正規形から第 5 正規形までの広く受け入れられている 5 つのものがあります。 この記事では、多くのデータベース設計ではここまで行えば十分である最初の 3 つについて説明します。