宇宙仮面の
C# Programming

 
SQL 2008

良いデータベースの設計

一度運用が始まってしまうと、簡単に変更はできません。一番最初に良いデータベースを設計することはとても重要です。

1.目次

1.目次
2.データの識別
3.データの定義
4.データの整合性チェック
 4.1 データの正規化
 4.2 ビジネス ルールの定義
 4.3 整合性
 4.4 データの妥当性検査
5.用語

.データの識別

インタビュー、既存データ構造の分析、ドキュメントの作成、綿密な見直しにより、アプリケーションに必要な "何を、どこで、いつ、なぜ" という情報を概念的な見地から文書化する。

.データの定義

データ型を割り当てる時の注意

  • 許容最大値と最小値
  • 既定値
  • 空値 (または NULL 値)
  • 予想される増加量
  • 予想される変更と (可能性のある範囲内での) 突発的変更

.データの整合性チェック

  • データの正規化
  • ビジネス ルールの定義
  • 参照整合性の適用
  • データの妥当性検査

4.1 データの正規化

正規化には、第1正規化から第5正規化までありますが、通常第3正規化までで十分です。

正規形 定義 操作
第1正規形 第1正規形
  • 重複する行を削除する。
  • 繰り返し現れる列をグループ化し、キーで連結する。
第2正規形 第1正規形で、すべての非キー属性が主キーに完全に従属している。
  • 完全関数従属でない非キー属性を分離する。

例:受注番号、商品番号、商品名、価格

f(商品番号)→商品名、価格なので、商品名、価格を分離する。

第3正規形 第2正規形で、すべての非キー属性が、
推移的に関数従属でない表
  • 推移的関数従属を別テーブルに分離する。

正規化しすぎると、大量で複雑なリレーションが作成されてしまう場合があります。最大でも4つ以下のテーブルの結合になるようにしたほうがよいようです。このために、 一度正規化して逆正規化を行う場合もある。

主な優れたデータベースのポイント

ポイント 解説
テーブルに主キーを追加する。 データベースの設計理論の基本規則は、各テーブルに一意な行識別子(1 つの列または複数の列)を追加する。
1 つのテーブルには 1 つの型の実体を表すデータだけを格納する。 1 つのテーブルに格納する情報が多すぎると、テーブル内のデータ管理の効率と信頼性が低下する。適切に分割することにより、冗長性を減らし、データ量を削減す る。
テーブルに NULL 値を許す列を作成しない。 特殊なケースでは NULL 値が便利なこともあります。しかし、NULL 値を許す列は、特殊な処理が必要でデータ操作が複雑になるため、極力使用しないようにする。
テーブルに繰り返し値または繰り返し列を作成しない。
データベース内のアイテムのテーブルには、個々の情報の値のリストを入れない。

4.2 ビジネス ルールの定義

ビジネスルールの適用

  • どのような適用機構を利用できるか? 
    コード、Windows フォームや Web フォームなどのユーザー インターフェイス、データ型、制約、トリガ、ストアド プロシージャ、イベント処理でルールを適用する。
  • ルールを重複して適用する必要があるか?
    場合によっては、重複してルールを適用することも必要。
  • どの程度積極的にルールを適用する必要があるか?
    "クレジット カードの番号は 16 桁である" ことを要求するルールは、積極的に適用する必要がある。 一方、"注文には備考が必要である" というルールは、常に適用する必要はない。
  • ルールの適用によってパフォーマンスにどのような影響があるか?
    GUIで入力審査するか、データベースでCHECKするかによって、パフォーマンスに影響がある。たとえば、GUIでの入力審査により、データベースとの間で情報がやり取り をなくすことができる。
  • ルールの適用によって保守性にどのような影響があるか?   
    特に複雑なルールの場合は、保守が簡単な方法での実装がよい。

4.3 整合性

  • 実体の整合性
    テーブルの識別子列または主キーの整合性を、インデックス、UNIQUE 制約、PRIMARY KEY 制約、IDENTITY プロパティによって保証します。
     
  • ドメインの整合性
    データ型によって値の型を、CHECK 制約とルールによって値の形式を、FOREIGN KEY 制約、CHECK 制約、DEFAULT 定義、NOT NULL 定義、およびルールによって値の範囲を制限する。
  • 参照整合性
    レコードが入力または削除されたとき、定義されたテーブル間の関係を維持します。参照整合性によって、テーブル間でのキー値の一貫性を維持 する。
     
  • ユーザー定義の整合性
    そのほかの整合性に当てはまらない特定のビジネス ルールを定義する。

SQL Server 2000 は、5 つの制約のクラスをサポートしています。

制約 説明
NOT NULL 列に NULL 値が受け入れられない。
CREATE TABLE cust_sample
    (
	cust_id int PRIMARY KEY,
	cust_name char(50) NOT NULL,
	CONSTRAINT chk_id 
	CHECK (cust_id BETWEEN 0 and 10000 )
    )
CHECK 制約 列に入れることができる値を制限する。
CREATE TABLE cust_sample
    (
	cust_id int PRIMARY KEY,
	CONSTRAINT chk_id 
	CHECK (cust_id BETWEEN 0 and 10000 )
    )
UNIQUE 制約 一連の列の中にある値の一意性を設定
PRIMARY KEY 制約 その値が一意にテーブル内の行を識別する列または列セットを指定
CREATE TABLE cust_sample
    (
	cust_id int PRIMARY KEY,
	cust_name char(50) NOT NULL,
	CONSTRAINT chk_id 
	CHECK (cust_id BETWEEN 0 and 10000 )
    )
FOREIGN KEY 制約 その値が一意にテーブル内の行を識別する列または列セットを指定する。

インデックスは自動的に作成されないが、結合に多用されている場合は、インデックスを作成したほうが良い。

CREATE TABLE order_part
    (
	order_nmbr int,
        part_nmbr int FOREIGN KEY 
	REFERENCES part_sample(part_nmbr) 
	ON DELETE NO ACTION,...

4.4 データの妥当性検査

すべてのデータ値が正しく適切であることをアプリケーションに対して保証します。データ正当性チェックの種類は次のとおりです。

  • データ型の正当性チェック
  • 範囲のチェック
  • コードのチェック
  • 複合正当性チェック

.用語

 

用語 解説
関数従属 f(X,Y,Z)=A のとき、Aは、X,Y,Zの関数従属
完全関数従属 f(X,Y,Z)=A で、X, Y, Zの1つでも欠けているとAが求まらない場合
推移的関数従属 f(X) = Y; g(Y) = Z; のとき、XとZは推移的関数従属
参照整合性 参照する側のテーブルの外部キーが、参照される側のテーブルの有効な行を必ず参照していなければならないこと。
ドメイン データベースの場合、列の有効な値の範囲を指す。
ドメイン整合性 属性の値が有効な値であるかどうかを保証する。