正規化とは

データベースの正規化とは、データをデータベース内のテーブルと列に整理するために使用される設計プロセスです。 各テーブルには、特定のエンティティに関連するデータを含める必要があり、そのエンティティをサポートする情報のみを含める必要があります。 正規化の主な目的は、データベース内の重複データを最小限に抑えることです。これは、挿入や更新時のパフォーマンスの低下防止に役立ちます。 たとえば、顧客の住所を更新する必要があるときに、住所が Customers テーブルなどの単一の場所に格納されている場合は、変更を実装する方が簡単です。

正規化の最も一般的な形式は、第 1 正規形、第 2 正規形、第 3 正規形です。

第 1 正規形

第 1 正規形は、次の条件を満たす形式です。

• 関連性のあるデータのセットごとに個別のテーブルを作成している
• 個々のテーブルで繰り返し出てくる部分を排除している
• 関連性のあるデータのセットがそれぞれ、主キーにより特定される

このモデルでは、1 つのテーブルに似たようなデータを格納する列をいくつも設けないようにする必要があります。 たとえば、製品の色が何種類もある場合に、1 行に色の値が異なる列がいくつもあるという状況は望ましくありません。 次の最初のテーブル ProductColors は、色の繰り返し値があるため、第 1 正規形ではありません。 色が 1 つだけの製品に、無駄な領域が発生しています。 さらに、製品が 3 色を超える場合、列の最大数を設定するのは実用的ではなくなります。 代わりに、2 番目のテーブル (ProductColor) に示すようにテーブルを再作成できます。

第 1 正規形では、テーブルに一意のキーがあることも必要です。これは、各行を一意に識別する (1 つまたは複数の) 列です。 2 番目のテーブルでは、どちらの列も単独では一意ではありませんが、ProductID と Color を組み合わせると一意のキーが形成されます。 一意のキーを作成するために複数の列が必要な場合は、複合キーと呼ばれます。

• ProductColors テーブル:

  ProductID	Color1	Color2	Color3
  1	赤い	Green	黄色
  2	黄色
  3	青い	赤い
  4	青い
  5	赤い

• ProductColor テーブル:

  ProductID	カラー
  1	赤い
  1	Green
  1	黄色
  2	黄色
  3	青い
  3	赤い
  4	青い
  5	赤い

3 つ目のテーブル ProductInfo は、各行が特定の製品に対応しており、繰り返し部分がなく、ProductID 列が主キーとなっていることから、第 1 正規形です。

第 2 正規形

第 2 正規形は、第 1 正規形に必要な条件を満たしたうえで、さらに次の条件を満たす形式です。

• テーブルに複合キーが存在する場合に、属性がいずれも複合キーに従属している (複合キーの一部だけに従属していてはならない)。

第 2 正規形は、2 番目のテーブル ProductColor のように、複合キーのあるテーブルにのみ関連します。 ProductColor テーブルに製品の価格も含まれている場合を考えてみましょう。 このテーブルでは、ProductID と Color の 2 列の値を使ってはじめて行を一意に識別できるため、この 2 列の複合キーがあることになります。 色が変わっても製品の価格が変わらないとすると、データは次のテーブルに示したようになります。

このテーブルは、第 2 正規形ではありません。 価格の値は ProductID に従属しますが、Color には従属していません。 ProductID 1 の行が 3 つあり、その製品の価格が 3 回繰り返されています。 第 2 正規形に違反する問題は、価格を更新する必要がある場合は、どこでも更新されるようにする必要があるということです。 最初の行で価格を更新しても、2 行目または 3 行目では更新しない場合は、更新の異常が発生します。 更新後、ProductID 1 の実際の価格を特定できなくなります。 対策としては、Price が単独の列キーとなっているテーブルに ProductID 列を移動させることが挙げられます。この列が Price が従属する唯一の列であるためです。 たとえば、テーブル 3 を使用して Price を格納できます。

製品の価格がその色に応じて異なる場合には、4 つ目のテーブルは第 2 正規形であると言えます。価格がキーの両方の要素、つまり ProductID と Color に従属するからです。

第 3 正規形

ほとんどの OLTP データベースが通常目指す形が、第 3 正規形です。 第 3 正規形は、第 2 正規形に必要な条件を満たしたうえで、さらに次の条件を満たす形です。

• キー列でないすべての列は、主キーに非推移的に依存します。

推移的なリレーションシップとは、テーブル内のある列が、何か別の列を挟んだうえで、さらに別の列に関係していることを指します。 依存関係とは、この関係の結果として、列の値を別の列から派生させることができることを意味します。 たとえば、生年月日からは年齢が特定できるので、年齢は生年月日に従属しています。 3 番目のテーブル ProductInfo に戻りましょう。 このテーブルは第 2 正規形ですが、第 3 正規形にはなっていません。 ShortLocation 列は、キーではない ProductionCountry 列に依存します。 第 3 正規形に違反すると、第 2 正規形と同じように更新不整合が発生する可能性があります。 ある行で ShortLocation を更新したものの、その場所が出てきている行のすべては更新しなかった場合には、データに不整合が発生します。 これを防ぐには、別のテーブルを作成して国や地域の名前とその短縮形を格納できます。

非正規化

第 3 正規形は理論上望ましいものではあるのですが、あらゆるデータで常に実現できるわけではありません。 また、データベースを正規化すれば必ず最善のパフォーマンスが得られるとも限りません。 正規化したデータは、何回か結合操作を経ないと、1 回のクエリの結果を返すのに必要なデータの全部を取得できないことが少なくありません。 データの正規化と非正規化には、トレードオフの関係があります。前者はクエリ結果を返すうえで必要な結合の回数が原因で CPU 使用率が高くなるのに対し、後者は必要な結合回数と CPU 使用率を少なくできるものの、更新不整合のリスクが生じます。

データ ウェアハウスのように読み取りの負荷が大きなワークロードの場合には特に、データを非正規化するとクエリの効率が良くなることがあります。 そのような場合には、列を追加することによって、もっと優れたクエリ パターンやシンプルなクエリを使えるようになる可能性があるのです。

スター スキーマ

ほとんどの正規化は OLTP ワークロードを対象としていますが、データ ウェアハウスには独自のモデリング構造があります。それは、通常、非正規化モデルです。 この設計では、ファクト テーブルを使用して、売上などの特定のイベントの測定値またはメトリックを記録し、ディメンション テーブルに結合します。 ディメンション テーブルは行数の観点からは小さくなりますが、ファクト データを記述するための列が多くなる場合があります。 ディメンションの例としては、在庫、時間、地理などがあります。 この設計パターンにより、データベースのクエリが容易になり、読み取りワークロードのパフォーマンスが向上します。

この図は、日付、通貨、製品の FactResellerSales ファクト テーブルとディメンションを特徴とするスター スキーマの例を示しています。 ファクト テーブルには販売トランザクションに関連するデータが含まれますが、ディメンションには売上データの特定の要素に関連するデータのみが含まれます。 たとえば、FactResellerSales テーブルには、販売された製品を示す ProductKey のみが含まれます。 各製品に関するすべての詳細は、DimProduct テーブルに格納され、ProductKey 列を使用してファクト テーブルに関連付けられます。

スター スキーマに関連した設計として、スノーフレーク スキーマがあります。これは、1 つのビジネス エンティティに関して、スター スキーマよりも正規化の度合いを強めた一連のテーブルを使用します。 次の図は、スノーフレーク スキーマの 1 つのディメンションの例を示しています。 Products ディメンションは正規化され、DimProductCategory、DimProductSubcategory、DimProduct の 3 つのテーブルに格納されます。

スター スキーマとスノーフレーク スキーマの主な違いは、スノーフレーク スキーマのディメンションは正規化により冗長性が抑えられており、記憶領域を節約できるという点です。 その代わり、クエリで発生する結合回数が多くなるため、複雑さが増し、パフォーマンスが低下する可能性があります。