テーブル構造の設計

データベースの利用において、開発者は様々なテーブルを作成し、データベース操作を実行します。本記事は、開発者がデータベース内のテーブル構造を標準的に設計するための支援を目的としています。

3つの正規化

テーブル構造設計を推奨する前に、データベースの概念であるデータベース設計の3つの正規化について理解しておきましょう。冗長性を抑え、より合理的な構造のデータベースを構築するためには、データベース作成時に一定の原則に従う必要があります。リレーショナルデータベースにおいて、このような規範は正規化と呼ばれます。以下に3つの正規化について簡単に紹介します。

第一正規形（フィールドの原子性）

第一正規形は最も基本的な正規形です。データベーステーブル内のすべてのフィールド値が不可分の原子値である場合、そのテーブルは第一正規形を満たしています。

例：学生テーブル。具体的なフィールド定義は以下のとおりです。

このテーブルから明らかに、連絡先の値にはメールアドレスと携帯電話番号が含まれているため、それらは原子値ではなく、第一正規形を満たしていません。ここで連絡先をメールアドレスと携帯電話番号に分割すると、各列は原子値となり、第一正規形を満たします。修正後の学生テーブルのフィールド定義は以下のとおりです。

第二正規形（部分依存の排除）

第二正規形は、第一正規形の基礎の上で、フィールドの定義に対する要求を一段階引き上げます。第二正規形では、データベーステーブル内の各列が主キーと関連している必要があり、主キーの一部のみに関連してはなりません（主に複合主キーについて言及しています）。つまり、データベーステーブルでは、一つのテーブルに一種類のデータのみを格納し、複数の種類のデータを同一のデータベーステーブルに保存することはできません。

例：学生・教員テーブル。具体的なフィールド定義は以下のとおりです：

このテーブルの主キーは（sno、tno）です。明らかに、各フィールドは原子値であり、さらに分割することはできません。これは第一正規形を満たしていますが、sname は sno に依存し、tname は tno に依存するため、第二正規形を満たしていません。したがって、テーブルを分割する必要があります。1枚のテーブルを3枚のテーブルに分割した後の具体的なフィールドは以下のとおりです。

学生テーブル：

教員テーブル：

学生・教員関係テーブル：

上記の3枚のテーブルでは、すべてのフィールドが原子値であり、部分依存も存在しないため、第二正規形を満たしています。

第3正規形（遅延依存がないこと）

第3正規形では、データテーブルの各列のデータが主キーと直接関連していることを保証し、間接的に関連してはなりません。

例：学生と教室のテーブル。具体的なフィールド定義は以下の通りです。

この従属テーブルから明らかに、各フィールドはすべて原子値であり、部分依存も存在しないため、第1正規形および第2正規形を満たしています。しかし、cname は cno に依存し、cno は tno に依存する遅延依存が存在するため、第3正規形を満たしていません。このテーブルを2つのテーブルに分割します。詳細は以下の通りです：

クラステーブル：

学生とクラスの関係テーブル：

分割後、テーブルに遅延依存は存在しなくなり、第3正規形を満たすことができます。

通常のテーブル構造設計規範

• テーブル構造設計は単純に三つの正規化を適用するのではなく、業務パフォーマンスを指針とし、適切にデータの冗長ストレージを行い、テーブル間の結合を減らして業務パフォーマンスを向上させるべきです。冗長フィールドは以下の条件を満たす必要があります：

  • 頻繁に変更されないフィールドであること。

  • varchar の超大文字列ではないこと。

• テーブル作成時には主キーを設定する必要があります。

  • 業務フィールドを主キーまたは複合主キーとして使用することを推奨します。自動インクリメント列を主キーとして使用することは推奨されません。

  • OceanBaseデータベースのテーブルストレージモデルはインデックス集約テーブルモデル(IOT)です。ユーザーが主キーを指定しない場合、システムは隠れた主キーを自動生成します。

• テーブルには必ず gmt_create と gmt_modified の2つのフィールドが必要です。

  説明

  gmt_create および gmt_modified の型は DATE（秒単位）または TIMESTAMP WITH TIME ZONE（マイクロ秒単位、現在のタイムゾーン情報付き）を選択できます。sysdate または systimestamp 関数を使用できます。

• テーブルとフィールドには COMMENT 属性が必要です。

• テーブル内のすべてのフィールドには NOT NULL 属性を設定することを推奨します。業務ニーズに応じて DEFAULT 値を定義できます。

• 複数のテーブルに存在する同名の列は、定義を完全に一致させる必要があります。

• join 操作で結合するフィールドは、データ型を統一しておくことで暗黙的な型変換を回避します。

• blob や json などの複雑なデータ型の使用は推奨されません。

• はい/いいえの概念を表すフィールドには、unsigned tinyint をデータ型として推奨します（1ははい、0はいいえを表します）。値の内容は統一する必要があります。 例：論理削除を表すフィールド名 is_deleted、1は削除済み、0は未削除を表します。

• フィールドの意味や表す状態に変更があったり、新たな状態が追加されたりした場合は、フィールドコメントを速やかに更新することを推奨します。