これまで、信頼性とセキュリティをできる限り維持しながらCRUD操作を実行できるSQLステートメントと幾つかの機能について議論してきました。 しかし、適切なスキーマ設計がなければ、操作のパフォーマンスが低下し、データの整合性が損なわれる可能性があります。 そのため、この記事では、スキーマ設計の重要な2つの概念である正規化とインデックス作成について説明します。

正規化

リレーショナルデータベースの文脈では、正規化とは一連のルールを適用し、正規形と呼ばれる適切に構造化された論理スキーマに到達することで、 パフォーマンスと信頼性のためにデータを整理するプロセスを指します。第一正規形（1NF）から第五正規形まであり、最も緩いものから最も厳格なものまで範囲があります。 第一正規形（1NF）では、データに関する情報を伝えるためにタプルの順序を使用すること、属性値を分割可能にすること、主キーがないこと、 および属性の繰り返し（列としてitem_1、item_2など）を禁止しています。

上記のリレーションCustomer_Productは、1NFのすべてのルールに違反する（そして主キーがない）スキーマに基づいて定義されています。 これを操作する（例えば、10本以上の鉛筆を購入した顧客を顧客登録日の降順で照会する）ことや、 データの整合性を維持する（例えば、顧客Cが購入した鉛筆の数量が欠落している、4つ以上のカテゴリのアイテムを購入した顧客のために列を追加する必要がある）ことがいかに困難であるかが明らかです。 以下は1NFにおけるCustomer_Productを示しています。

ここでは、重複を避けるためにcustomer_nameとitem_nameの組み合わせが主キーとして設定され、1NFのルールに従っています。 これは以前のバージョンよりもはるかに扱いやすく見えますが、まだ改善できる点があります。例えば、顧客Aが注文をキャンセルした場合、 顧客Aのすべての情報（date_registered、loyalty_level、statusなど）が失われます（購入していない顧客Bですでに起こっているように）。 また、この情報は顧客Cについて重複しています。これらの問題は、非キー属性（date_registered、loyalty_level、status）がcustomer_nameにのみ依存しているのに対し、 主キーがcustomer_nameとitem_nameのテーブルに配置されているために発生します。したがって、第二正規形（2NF）は1NFを基に構築され、 非キー属性がキー全体に依存するように制限します。

上記は2NFに従うように修正された関係を示しています。このルールに従うために、主キーがcustomer_nameである新しいCustomerリレーションを導出しました。 また、データの整合性のためにcustomer_name属性に外部キー制約を導入することもできます。これにより、購入していない顧客Bを回復し、 購入がロールバックされても顧客情報が削除されないことを保証できます。また、以前のバージョンで見られた情報の重複も避けることができます。 一見すると上記には問題がないように見えますが、statusがloyalty_levelのみに依存する場合（推移的依存関係）にはある程度のリスクがあります。

これは、顧客のロイヤルティレベルを調整する場合、適切な場合にステータスも適切に更新されることを確認する必要があり、これを忘れる可能性があるためです。 トランザクションとトリガーを使用して一貫した更新を強制することができますが、トリガーにはオーバーヘッドがあり、 スキーマのすべてのユーザーに明示的でない場合があります。依存関係が非常に複雑で、トランザクションとトリガーの使用が複雑になる場合もあります。 したがって、第三正規形（3NF）は、非キー属性が直接かつキー全体にのみ依存するように制限することで、このリスクを排除することを目的としています。 この場合、Loyalty_Statusと呼ばれる別の関係を設定して、すべてのロイヤルティレベルを対応するステータスにマッピングすることができます。

第4および第5正規形

スキーマが3NFの場合、一般的に一貫性を維持しながら操作することは容易で直感的です（過去の記事では少なくとも3NFのスキーマ例を扱ってきました）が、 まだいくつかの重要なリスクがあります。例えば、様々なモデル、カラーバリエーション、ストレージ容量を持つスマートフォンのテーブルを設定する場合、 model、color、storageを主キーとするテーブルを作成したくなるかもしれません。このようなテーブルは3NFを満たしますが、モデルに新しい色を追加すると、 新しい色とストレージ容量のすべての組み合わせをテーブルに追加する必要があり、エラーが発生しやすくなります。 この問題は、キー内に2つの多値従属性（モデルと色の間、およびモデルとストレージの間）があるために発生します。

第4正規形（4NF）は、このような非自明な多値従属性をキーのみに制限します。スキーマを4NFにするには、Model_ColorとModel_Storageという別々のテーブルを作成することができ、 これにより単一の更新で新しい色を追加することができます。第5正規形（5NF）はさらに厳しいルールを強制し、テーブルがいくつかのテーブルを結合した論理的な結果として記述できないようにします。 つまり、テーブルは可能な限り分割する必要があります。（Customer_Productの例では、製品関連の属性を持つ別のProductテーブルを持ち、多値従属性をさらに分割する必要があります。）

常に4NFと5NFでスキーマを構築するのが最も安全ですが、4NFが過剰である場合もあります。例えば、いくつかのコースとその講師に関するデータを保存する場合で、 そのデータがほとんど変更されず、情報の正確性に関する深刻な懸念がない場合、4NFのためにコースと講師のIDを保存する追加のCourse_Instructorテーブルを持つことは冗長かもしれません。 したがって、シナリオに応じて4NFと5NFが適切かどうかを分析することが重要です。

インデックス作成

操作のパフォーマンスを最適化するために、DBMSはBツリーやB+ツリーなどのデータ構造を利用し$O(log(n))$の時間複雑性を達成します。 例えば、PostgreSQLや他の多くのDBMSは主キー（および外部キー）のインデックスを自動的に設定し、高速な挿入、クエリ、削除を実現するために（デフォルトで）Bツリーを構築します。 そのため、数百万行のテーブルでは、インデックス付き列と非インデックス列（$O(n)$の時間複雑性を持つ線形検索）に基づくクエリの間には桁違いの差があることが多いです。

自動生成されるインデックスの他に、頻繁にクエリが予想される列に追加のインデックスを設定することができます。 例えば、ホームページに最新の更新されたブログ記事を表示することを知っている場合、Blogテーブルのlast_modified_at列にカスタムインデックスを設定することができます。 これはCREATE INDEX last_modified_idx ON Blog (last_modified_at)で行うことができます。 これにより、DBMSはより高速なクエリのためにインデックスに基づいてBツリーを構築します（2D幾何データ型の最近傍検索のためのGiSTや配列上の操作のためのGINなど、 ユースケースに応じて他のデータ構造タイプを選択することもできます）。

複数の列を使用してインデックスを作成することもでき、これは多くの場合、属性値の連結を用います。複数列インデックスは複数列クエリに適しており、 複数の単一列インデックスを設定するよりもメモリオーバーヘッドを若干削減できますが、PostgreSQLは一般的に堅牢性と単純性のために単一列インデックスを使用することを推奨しています。 SELECT * FROM pg_indexes WHERE tablename=<table_name>;でテーブルに作成されたインデックスを確認し、DROP INDEX <idx_name>;でインデックスを削除することができます。 適切なテーブルの適切な列に適切なインデックスを使用し、それらのインデックスに基づいてステートメントを構築することで、 最小限のメモリオーバーヘッドでより高速なパフォーマンスを実現できます。

結論

この記事では、データの整合性と優れたパフォーマンスのためのスキーマ設計に重要な概念であるデータベース正規化とインデックス作成について説明しました。 データベースシステムを設計し、ユーザーとしてそれらと対話する際には、シナリオに応じてこれらを常に念頭に置き、適切に活用することが重要です。 次の記事では、アプリケーション側に近づき、仮想スキーマとユーザーに関する概念について議論します。

リソース

• Decomplexify. 2022. Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF. YouTube.
• PostgreSQL. n.d. PostgreSQL 17.4 Documentation. PostgreSQL.
• Supabase. 2024. Are Multi-Column Indexes a good idea?. YouTube.