テーブル構造設計

データベースを使用する際、開発者はさまざまなテーブルを作成してデータベース操作を行います。本記事では、開発者がデータベース内のテーブル構造を適切に設計できるよう、設計の指針を示します。

三つの正規形

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

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

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

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

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

第二正規形（部分関数従属の排除）

第二正規形は、第一正規形を満たした上で、フィールドの定義にさらに条件を課します。第二正規形では、データベーステーブルの各列が主キー全体に依存し、主キーの一部だけに依存しないことが求められます（主に複合主キーを対象とします）。つまり、一つのテーブルに一種類のデータのみを格納し、複数の種類のデータを同一のデータベーステーブルに保存することはできません。

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

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

学生テーブル：

教員テーブル：

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

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

第三正規形（推移的関数従属の排除）

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

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

このテーブルから明らかに、各フィールドは原子値であり、部分関数従属も存在しないため、第一・第二正規形を満たしています。しかし、cnameはcnoに依存し、cnoはsnoに依存するため、推移的関数従属が存在し、第三正規形を満たしていません。このテーブルを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 は未削除を表します。

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