テーブル構造設計

データベースの利用過程において、開発者はさまざまなテーブルを作成し、データベース操作を行います。本記事は、開発者がデータベース内のテーブル構造設計を規範化するための支援を目的としています。

3つの正規形

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

第一法則（フィールドの原子値）

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

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

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

第二法則（部分依存性の排除）

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

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

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

学生テーブル：

教員テーブル：

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

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

第3正規形（非遅延依存）

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

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

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

クラステーブル：

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

分割後、テーブル内に遅延依存関係は存在せず、第3正規形を満たしています。

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

• テーブル構造設計は単純に3つの正規形に従うべきではなく、業務性能を指針として適切にデータ冗長ストレージを行い、テーブル間の結合を減らすことで業務性能を向上させるべきです。冗長フィールドは以下の原則に従う必要があります：

  • 頻繁に変更されないフィールド。

  • varchar の超長フィールドではない。

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

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

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

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

  説明

  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は未削除を意味します。

• フィールドの意味を変更したり、フィールドが表す状態に追加したりする場合は、フィールドのコメントを適時更新することを推奨します。