テーブル構造設計

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

三つの正規形

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

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

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

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

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

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

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

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

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

学生テーブル：

教員テーブル：

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

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

第3正規形（伝搬依存がないこと）

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

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

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

クラステーブル：

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

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

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

• テーブル構造設計は単純に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 は未削除を表します。

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