テーブル設計の考え方
はじめに
個人のポートフォリオとしてWebアプリを開発した際に、初めてテーブル設計を行いました。
なぜテーブル設計を行う必要があるのか、明確に理解できていなかったので、学習の記録としてこちらの記事で共有させていただきたいと思います。
テーブル設計は簡単に言うと、「どんなデータをどのようにデータベースへ保存するか」を決めていく作業です。
ただし、テーブル設計を行う上で複数のステップがあり、意外とやらないといけないことは多いです。
テーブル設計のステップを大きく分けると、次の3ステップになります。
ステップ１：概念設計
ステップ２：論理設計
ステップ３：物理設計
それでは、それぞれのステップについて具体例を交えながら順に実施していきます。
ステップ1. 概念設計
エンティティの洗い出し
まず初めに、エンティティというものを洗い出す作業を行います。
エンティティというのは、サービスの中で扱う「もの」です。
例えば、ある大学に通う学生のデータを管理したい場合、次のようなものがエンティティとなります。
- 生徒名
- 受講している科目名（※ 以降、「科目名」とします）
データの定義
続いて、それぞれのエンティティの属性を決めていきます。
属性は、簡単に言うとテーブルのカラム名です。
例えば、「生徒」というエンティティの属性を考える場合、「生徒名」、「学部名」、「キャンパス所在地」という3つの属性を考えることができます。
具体例
ここで、エンティティと属性に具体的なデータを当てはめて、簡単なテーブルを作成してみます。
- 生徒エンティティ: 生徒名、学部名、キャンパス所在地
- 科目エンティティ：科目名
| ID | 生徒名 | 学部名 | キャンパス所在地 | 科目名 |
|---|---|---|---|---|
| 1 | 小野 | 農学部 | 岡山市 | 心理学・栄養学 |
| 2 | 内田 | 工学部 | 倉敷市 | 栄養学 |
| 3 | 山内 | 工学部 | 倉敷市 | 心理学 |
このテーブルでは、IDを一つ指定すれば、そのIDに対応したレコードを取得できる状態になっています。
しかし、このテーブル設計でデータを保存していくのは効率が悪く、データの整合性も取りづらい状態です。
例えば、工学部のキャンパス所在地が、「倉敷市」として2つのレコードに入力されています。
ここでもし、工学部のキャンパスが「津山市」に移転した場合に、レコードの内容を書き換える必要があります。
| ID | 生徒名 | 学部名 | キャンパス所在地 | 科目名 |
|---|---|---|---|---|
| 1 | 小野 | 農学部 | 岡山市 | 心理学・栄養学 |
| 2 | 内田 | 工学部 | 津山市 | 栄養学 |
| 3 | 山内 | 工学部 | 倉敷市 | 心理学 |
変更してみましたが、このテーブルの中で、問題が発生していることに気づきましたか？
実は、ID:2のレコードは、倉敷市から津山市にちゃんと変更されていますが、ID:3のレコードは倉敷市のままとなってしまっています。
このように、複数のレコードに同じ属性値を記述していると、変更漏れによるデータの不整合が発生するリスクがあります。
そこで、次のステップで行う正規化によって、上記のようなデータの不整合が発生しないように、テーブルの設計を行っていきます。
（※ 今回はエンティティが少なく構造が単純なため、ER図の作成は割愛します）
ステップ2. 論理設計
正規化を行う前に、先ほど使用したデータを少し修正します。
各エンティティごとに、IDをふっておきます。
これは、複数のレコードで重複しやすい属性値を、各エンティティごとの一意なIDに紐づけることで、管理しやすくすることが目的です。
| 生徒ID | 生徒名 | 学部名 | キャンパス所在地 | 科目ID | 科目名 |
|---|---|---|---|---|---|
| 1 | 小野 | 農学部 | 岡山市 | 1・2 | 心理学・栄養学 |
| 2 | 内田 | 工学部 | 倉敷市 | 2 | 栄養学 |
| 3 | 山内 | 工学部 | 倉敷市 | 1 | 心理学 |
実際に正規化を行う前に、簡単に正規化についての用語説明をしておきます。
用語説明
・正規形
一貫性と効率性を持った形でデータを保持するためのデータ形式のことです。
・正規化
テーブルを整理・分割することで、一貫性と効率性を持つようにデータベースを設計することです。
第一正規形 ⇒ 第二正規形 ⇒ 第三正規形と、段階的に整理・分割していきます。
第六正規形までありますが、第三正規形までで最低限必要な正規化ができるので、今回は第三正規系までの説明とします。
それでは、実際に正規化を行っていきます。
第一正規形
まずは第一正規形への正規化です。
第一正規形とは、一つのセルの中には一つの値しか含まないデータ形式です。
この正規化によって、効率よく検索することができるになります。
第一正規形への正規化の結果
| 生徒ID | 生徒名 | 学部名 | キャンパス所在地 | 科目ID | 科目名 |
|---|---|---|---|---|---|
| 1 | 小野 | 農学部 | 岡山市 | 1 | 心理学 |
| 1 | 小野 | 農学部 | 岡山市 | 2 | 栄養学 |
| 2 | 内田 | 工学部 | 倉敷市 | 2 | 栄養学 |
| 3 | 山内 | 工学部 | 倉敷市 | 1 | 心理学 |
上記のように、生徒ID:1の科目が一つのセルに2種類入力されていたのを、2つのレコードに分けています。
この正規化によって、すべてのレコードを一意に識別することができるようになります。
つまり、「生徒ID:1と科目ID:1」を指定したときに、1つのレコードが決まる状態になっています。
この「生徒ID:1 + 科目ID:1」のことを複合主キーと呼びます。
複合主キーは、複数の主キー（ここでは生徒IDと科目ID）で構成されます。
さて、ここまでで第一正規化が完了したのですが、このままでは、同じ属性値が複数のレコードで繰り返し入力されている状態です。
例えば、「小野」さんは1行目と2行目の2つのレコードに記載されています。
この状態のことを、部分関数従属が存在している状態と呼びます。
部分関数従属とは、一部の非キー属性（IDのような主キー以外の属性）が、複合主キーの一部にのみ依存することです。
つまり、「小野」さんは、「生徒ID:1 + 科目ID:1」と「生徒ID:1 + 科目ID:2」という複合主キーの、「生徒ID:1」という主キーのみに依存しているので、履修する科目の数だけレコードを生成する必要があります。
複数のレコードに同じデータが繰り返し保存されると、無駄なストレージが必要になったり、更新漏れによるデータ不整合などが発生してしまいます。
そこで、次の第二正規形への正規化によって、この「部分関数従属」を排除していきます。
第二正規形
テーブルを分割して、全ての非キー属性が主キー全体に依存する形に変換していきます。
第二正規形への正規化の結果
学生テーブル：
| 生徒ID | 生徒名 | 学部名 | キャンパス所在地 |
|---|---|---|---|
| 1 | 小野 | 農学部 | 岡山市 |
| 2 | 内田 | 工学部 | 倉敷市 |
| 3 | 山内 | 工学部 | 倉敷市 |
科目テーブル：
| 科目ID | 科目名 |
|---|---|
| 1 | 心理学 |
| 2 | 栄養学 |
履修テーブル：
| 履修ID | 生徒ID | 科目ID |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
| 4 | 3 | 1 |
上記のようにテーブルを分割し、履修テーブルという中間テーブルで、学生テーブルと科目テーブルの主キーのみを管理します。（生徒IDと科目IDは外部キーと呼ばれます）
こうすることで、複数のレコードに同じ属性値が繰り返し保存されることがなくなり、データの整合性を保つことができるようになります。
さて、ここまでで、第二正規形への正規化も完了しました。
しかし、最初の方で述べた、キャンパス所在地の変更漏れによって、データ不整合が発生するリスクがあるという問題がまだ残っています。
難しい言葉でいうと、推移的関数従属という関係性がある状態です。
推移的関数従属というのは、「非キー属性（キャンパス所在地）が他の非キー属性（学部名）に依存すること」です。
つまり、「◯◯学部は△△キャンパスにある」という関係性を現しています。
この、データ不整合が発生するリスクに関する問題を解消するために、最後に第三正規形への正規化を行います。
第三正規形
生徒テーブルをさらに分割して、全ての非キー属性が直接主キーに依存する形に変更します。
第三正規形への正規化の結果
生徒テーブル：
| 生徒ID | 生徒名 | 学部ID |
|---|---|---|
| 1 | 小野 | 1 |
| 2 | 内田 | 2 |
| 3 | 山内 | 2 |
学部テーブル：
| 学部ID | 学部名 | キャンパス所在地 |
|---|---|---|
| 1 | 農学部 | 岡山市 |
| 2 | 工学部 | 倉敷市 |
以上で正規化の作業がすべて完了しました。
ステップ3. 物理設計
最後の物理設計については、具体的な実装内容を決めていく作業です。
ここでは、用語やポイントについて簡単にまとめています。
1. テーブル名とカラム名の命名
- 英語で命名し、一貫性を保つ
- テーブル名：複数形
- カラム名：スネークケース
2. データ型
- INT / BIGINT: 整数型
- VARCHAR(n): 文字列型（n文字までの可変長）
- TEXT: 長い文字列やブロックベースエディタなどの大きなテキストフィールド
- BOOLEAN: 真偽値
- TIMESTAMP / DATETIME: 日時データ
- DECIMAL(m, n): 精度が必要な数値
3. 制約条件 (Constraints)
- Primary Key (PK): テーブルの主キー
- Foreign Key (FK): 他のテーブルのカラムに対する外部キー制約
- NOT NULL: NULL値を許可しない
- UNIQUE: 一意の値を強制
- CHECK: 特定の条件を満たす値を強制
- DEFAULT: デフォルト値を設定する
4. インデックス (Indexes)
- Primary Keyインデックス: 自動的にインデックスが作成される
- Foreign Keyインデックス: 外部キーのパフォーマンス向上のためにインデックスを付ける
- 頻繁に検索されるカラム: ユーザー名やメールアドレスなど
- 複合インデックス: 複数のカラムに対してインデックスを設定
所感
今までの学習では、当然のように「Users」や「Articles」などのテーブルを分割して作成していましたが、正規化を正しく行わないと、データ不整合や、ストレージの無駄などの問題が発生してしまうので、正規化が非常に重要な作業であることがわかりました。
実際に、ポートフォリオとして作成したアプリのテーブル設計でも、テーブル同士の関係性や、各テーブルに何のカラムを設けるかなどは、開発途中で何度も修正を行ったので、最初にエンティティや属性値を十分に洗い出す作業も重要であると痛感しました。
Discussion