データベース構造と設計

適切に構築されたデータベースには、以下の特長があります。

• 冗長データを排除し、ディスク容量を節約する。
• データの正確性と整合性を保つ。
• 便利な方法でのデータへのアクセスを可能にする。

効率的で有用なデータベース設計のためには、以下のフェーズを含め、適切なプロセスの実行が肝要です。

1. 要件の分析やデータベースの目的の定義
2. 表形式でのデータの整理
3. 主キーの指定と関連の分析
4. 正規化による表の標準化

それでは、各ステップを詳細に見ていきましょう。本ガイドでは、(階層型、ネットワークやオブジェクトデータモデルではなく) SQL で記述されたエドガー・コッドの関係データベースモデルを取り扱います。データベースモデルについての詳細は、こちらのガイドを参照にしてください。

データベースの目的を理解することで、設計プロセスにおける選択肢が自然と決まってきます。あらゆる観点から作成するデータベースについて考察するようにしましょう。例えば、公共図書館のデータベース構築の場合には、利用者や司書がデータへのアクセスに利用する方法などから検討を始めます。

データベース作成前の情報収集の方法には、以下のようなものがあります。

• データベースを利用するユーザーに対する聞き込み
• 請求書、タイムシートやアンケートなどの業務用フォームの分析
• 既存のデータシステム (物理/デジタル両ファイルを含め) を隅々までチェック

まず、データベースに含める既存のデータの収集から始めます。その後、格納したいデータの種別、そして、以下のような、これらのデータが記述する人、事物、場所やイベントなどの実体をリストアップします。

顧客

• 氏名
• 住所
• 市、州、郵便番号
• メールアドレス

製品

• 氏名
• 価格
• 在庫数量
• 注文数量

注文

• 注文 ID
• 担当者
• 日付
• 製品
• 数量
• 価格
• 合計金額

この情報は、後にデータベース内の表とフィールドの概要を表すデータディクショナリの一部となります。情報は必ず利用可能な最小単位に分割するようにします。例えば、後で居住国別に個人をフィルターできるよう、国と住所を分けることもできるでしょう。また、無駄に複雑にならないよう、複数の表に同じデータポイントを含めることは避けます。

データベースに含めるデータの種別、データの取得元、データベースの用途を把握できたら、実際のデータベース設計に入る準備が整います。

次の手順では、データベースの視覚的なレイアウトを決定します。そのためには、関係データベースの公正を正確に理解しておく必要があります。

データベース内では、関連するデータは表形式にグループ化されます。表はそれぞれ、スプレッドシートのように行 (組) と列で構成されています。

データのリストを表に変換するには、まず、製品、売上、顧客や注文など、実体の型別に表を作成します。以下に例を示します。

表の各行は履歴と呼ばれます。履歴には、特定の顧客など、何らかの人や事物に関するデータが含まれます。これに対して、列 (フィールドや属性とも呼ばれます) には、表に記載された顧客全員の住所など、各履歴に示される情報の1つの型が含まれます。

名前	姓	年齢	郵便番号
Roger	Williams	43	34760
Jerrica	Jorgensen	32	97453
Samantha	Hopkins	56	64829

履歴間でのデータの一貫性を保つため、各列に適切なデータ型を割り当てます。一般的なデータ型には以下のようなものがあります。

• CHAR - 固定長文字列
• VARCHAR - 可変長文字列
• TEXT - 大量の文字列
• INT - 正または負の整数
• FLOAT, DOUBLE - 浮動小数点数の格納も可能
• BLOB - バイナリデータ

データベース管理システムの中には、行別に一意の番号を自動生成するオートナンバーデータ型が含まれるものもあります。

データベースを視覚的に概観する実体関連図を作成するためには、表の代わりに図内のボックスを用います。各ボックスのタイトルは、対象の表のデータが記述する内容を示し、他方で属性は以下のように列挙されます。

最後に、必要に応じて、各表の主キーとして機能する属性を決定します。主キー (PK) は所与の実体の一意の識別子を指します。つまり、その値しか分からない場合でも、顧客を正確に選択できるということになります。

主キーとして選択する属性は、一意かつ不変で、常に存在する (NULL または空とならない) ものとします。このため、例えば注文番号やユーザー名は主キーに適しており、電話番号や住所は適していません。主キーに併せて複数のフィールドを用いる (複合キー) こともできます。

実際のデータベース作成段階では、論理データ構造と物理データ構造を、使用しているデータベース管理システムが対応しているデータ定義言語にする必要があります。その時点では、必要なパフォーマンスレベルとストレージ容量が得られるよう、データベースのサイズを見積もる必要もあります。

データが表形式に変換され、これらの表間の関連を分析する準備ができました。濃度とは、2つの関連する表間でやり取りする要素の量を指します。濃度を特定することで、データを表へ効率的に分割できたかどうかを確認することができます。

実体はそれぞれ、他のあらゆる実体と関連をもつことができますが。これらの関連は通常、以下の3つの種類のいずれかに該当します。

一対一の関連

実体 B のインスタンスすべてに対して実体 A のインスタンスが1つしかない場合、これらの実体は一対一 (1:1) の関連にあるとされます。ER 図では、以下のように、各端にダッシュをもつ線でこうした関連を示すことができます。

特別な理由が他にない限り、1:1の関連は通常、2つの表のデータを1つの表に統合した方がよい旨を示すものです。

ただ、特殊な条件下で1:1の関連の表を作成したい場合もあるでしょう。「説明」など、省略可能なデータのフィールドがあり、履歴の大半でこのフィールドが空白の場合には、これら「説明」すべてをまとめて別個に表を作成し、空白のスペースを排除してデータベースのパフォーマンス改善につなげることができます。

データを正しく一致させるには、各表に同一の列 (通常、主キー) を少なくとも1列含める必要があります。

一対多の関連

この関連は、1つの表内の履歴が別表の複数の入力内容に関連付けられている場合に発生します。例えば、ある顧客が複数の注文を行った場合や、利用者が一度に図書館で複数の図書を借りた場合がこれに当たります。一対多 (1:M) の関連は、以下の例のような「カラスの足記法」で示されます。

データベース構築時に1:M の関連を実装するには、関連の「1」側の表の主キーを他方の表の属性として追加します。こうして別の表に記載された主キーは、外部キーと呼ばれます。関連の「1」側の表は、「M」側の子表に対して親表とみなされます。

多対多の関連

ある表の複数の入力内容がもう1つの表の複数の入力内容に関連付けられる場合、これらは多対多 (M:N) の関連にあるとされます。この例としては、学生と生徒の関係が挙げられます。学生は多数の授業を受講でき、授業には多数の学生が参加できるためです。

ER 図では、これらの関連は以下の線で示されます。

残念ながら、データベースに直接多対多の関連を実装することはできません。2つの一対多の関連に分割する必要があります。

関連を分割するには、2つの表の間に新しい実体を作成します。販売と製品の間に M:N 関連が存在する場合には、各販売における内容を示すであろう新しい実体に「販売済み製品」と名を付けるのも一案です。販売と製品の表はいずれも、「販売済み製品」との間で1:M の関連をもつことになります。こうした橋渡し役をする実体は、さまざまなモデルでリンク表、関連実体や結合表などと呼ばれます。

このリンク表の各履歴は、隣接する表の実体2つを一致させたものです (補足情報が含まれる場合もあります)。例えば、学生 (Students) と授業 (Classes) の間のリンク表は以下のようになります。

必須か否か?

関連を分析するには、関連の一方が存在する前提として、他方が存在する必要があるかどうかを検討する方法もあります。存在が必須でない側には、線上の通常ダッシュを付ける位置に丸を付けて示すことができます。例えば、以下の例では、ある国の国連代表 (UN representative) が存在するためにはその国 (Country) 自体の存在が不可欠ですが、逆は真ではありません。

2つの実体を相互依存 (他方が存在しない場合には存在し得ない) とすることもできます。

再帰関連

時に、表がその表自体を参照することもあります。例えば、従業員の表に同じ表内の他の人を指す属性「マネージャー」が含まれる場合があります。これは再帰関連と呼ばれます。

冗長関連

関連が複数示されている場合、関連が冗長であると称されます。通常は、重要な情報を失うことなく、関連の片側を削除することができます。例えば、実体「学生」が他の実体「教師」と直接関連しており、なおかつ「授業」を通じて間接的にも「教師」と関連している場合には、「学生」と「教師」の間の関連を削除することも考えられます。「学生」は「授業」を通じてのみ「教師」に割り当てることができるため、「学生」と「教師」の間の関連は削除した方がよいでしょう。

データベースの基本設計が完了したら、正規化ルールを適用して表が正しく構成されていることを確認します。こうしたルールは業界標準といえるものです。

それはつまり、データベースの中には正規化に適さないものもあるということを意味します。一般に、ユーザーが履歴の作成、読み出し、更新や削除に関与するオンライントランザクション処理 (OLTP) データベースでは、正規化が必要となります。

分析とレポーティング向きのオンライン分析処理 (OLAP) データベースの場合には、計算速度が重視されるため、ある程度の非正規化が適している可能性があります。データを変更せずに迅速に分析する必要のある意思決定支援アプリケーションもこれに含まれます。

正規化の各形式とレベルにはいずれも、下位の形式に関連付けられたルールが含まれます。

第1正規形

第1正規形 (1NF) は、表内の各セルに設定できる値が１つのみで、複数の値を含むことがありません。したがって、以下のような表はこれに該当しません。

製品 ID	色	価格
1名	茶、黄	$15
2	赤、緑	$13
3	青、オレンジ	$11

データを別の列に分割してこのルールを回避したくなるかもしれませんが、それもまたルールに違反しています。反復、または密接な関係をもつ属性のグループを含む表は第1正規形の条件を満たさないためです。例えば、以下のような表は条件を満たしません。

代わりに、各セルに含まれる値が1つのみとなり、余分な列がなくなるようにデータを複数の表や履歴に分割します。この時点で、データは利用可能な最小単位へ分割され、不可分となります。上記の表の場合には、特定の製品を売上と一致させる表「売上詳細」を追加で作成することができます。そうすることで、「売上」と「売上詳細」が一対多の関係をもつようになります。

第2正規形

第2正規形 (2NF) においては、各属性は主キー全体に完全に依存します。これはつまり、各属性が、他の属性経由で間接的にではなく、個別に直接主キーに依存しなければならないことを意味します。

例えば、属性「学生 ID」に依存する属性「生年月日」に属性「年齢」が依存している状態は、部分関数従属と称され、これらの属性を含む表は第2正規形の条件を充たしません。

さらに、複数のフィールドから構成され、主キーを含む表で、1つまたは複数のフィールドが主キーのすべての部分に従属しないものは、第2正規形に該当しません。

したがって、以下のフィールドを含む表は、属性「製品名」が「製品 ID」に従属しているものの、「注文番号」に従属しないため、第2正規形となりません。

• 注文番号 (主キー)

• 製品 ID (主キー)

• 製品名

 第3正規形

第3正規形 (3NF) は、これらのルールに、すべての非キー列がその他のすべての列から独立しているという要件を追加したものです。1つの非キー列の値を変更することで他の値が変化する場合、その表は第3正規形の条件を満たしません。

したがって、以下の「税金」列のように注文の合計金額に直接従属する派生データを表内に含めることはできなくなります。

注文	価格	税金
14325	$40.99	$2.05
14326	$13.73	$.69
14327	$24.15	$1.21

ボイス・コッド正規形、第4正規形から第6正規形やドメイン・キー正規形など、この他にも提唱された正規形はありますが、上記の3形式が最も広く用いられています。

これらの形式は一般に従うべきベストプラクティスとして有用ですが、正規化の程度はデータベースのコンテキストに依存します。

1つの種別のデータの複数のディメンションへのアクセスが必要となる場合もあることでしょう。OLAP データベースにおいては特にそうしたニーズが強いと思われます。例えば、顧客、州や月別の売上を知りたい場合などが考えられます。こうした場合には、以下のように、他の顧客、州や月の参照先となるファクト表を中心に作成するのが最適です。

また、適切なルールに従い、データを検証できるようにデータベースを構成することも重要です。Microsoft Access を始め、多くのデータベース管理システムには、こうしたルールを自動適用する機能が備わっています。

実体整合性ルールによれば、主キーを NULL とすることは決してできません。このキーが複数の列で構成される場合、いずれの列も NULL とすることはできません。この条件を満たさない場合には、履歴を一意に識別することができない可能性があります。

参照整合例ルールにおいては、1つの表に記載される外部キーはそれぞれ参照先の表内の主キーと一致することが必要となります。主キーに対して変更や削除を行った場合、これらの変更内容をデータベース全体のすべての参照元について反映させる必要が生じます。

ビジネスロジック整合性ルールは、データが特定の論理パラメータ内に収まることを確保するためのものです。例えば、予約時間であれば、通常営業時間内に収まる必要があります。

インデックスとは本質的に、並べ替えられた1つまたは複数の列のコピーで、昇順または降順で値が並ぶものを指します。インデックスを追加することで、ユーザーはより履歴を見つけやすくなります。システムは、それぞれのクエリを再度並べ替えることなく、インデックスで指定された順に履歴にアクセスすることが可能となります。

インデックスを使用することでデータ取得の速度が向上しますが、履歴に変更が加えられるたびにインデックスの再構築が必要となるため、挿入、更新や削除の速度が低下する可能性もあります。

ビューとは、データに保存されたクエリを指します。複数の表からのデータの結合や表の一部の表示の際に便利です。

基本的なレイアウトが完成したら、指示テキスト、入力マスク、特定のスキーマ、ビューや列に適用する形式設定ルールなどの拡張プロパティを使ってデータベースを調整します。これらのルールはデータベース自体に格納されるため、そのデータにアクセスする複数のプログラム全体でデータの整合性を確保できるという利点があります。

オブジェクト指向言語で作成された複雑なシステムを視覚的に表現するためのもう一つの方法が統一モデリング言語 (UML) です。本ガイドで取り上げた概念のいくつかは、UML においては別の名前で表現されます。例えば、実体は UML ではクラスと呼ばれます。

UML はかつてほどは使われなくなり、現在では、学術用途やソフトウェア設計者とクライアントの間のコミュニケーション手段として主に使われています。

利用しているデータベース管理システムにより、設計面で使える選択肢も異なります。広く使われるシステムには以下のようなものがあります。

• Oracle DB

• MySQL

• Microsoft SQL Server

• PostgreSQL

• IBM DB2

選択の際には、コスト、オペレーティングシステムや機能などに基づき適切なデータベース管理システムを選択しましょう。