【PostgreSQL】マスタテーブルの最適構造は？

# 1. はじめに

PostgreSQLに限った話ではないが、データベースを扱う上でマスタテーブルを作成することがある。
当たり前のように教科書にお決まりの構造（IDと項目の組み合わせ）が載ってはいるが、なぜそのような構造になるのかを記載する。

# 2. マスタテーブルの設計

次のテーブルを作る場合どのような構造が良いかを考える。

- 業務日報を付けるため、業務の種類をマスタとして作成する。
- 業務マスタは、大分類・小分類・項目の3階層で構成する。

マスタ化したい内容を表でまとめると、次のようになる。
（これが適切な分類かどうかは置いておく。）

|大分類|小分類|項目|
|:-:|:-:|:-:|
|定常業務|開発|設計|
|定常業務|開発|コーディング|
|定常業務|庶務|日報作成|
|非定常業務|開発|メンテナンス|
|非定常業務|開発|ログ分析|
|非定常業務|庶務|クレーム対応|

## NG例

```sql:
CREATE TABLE mst_works (
	category TEXT,  -- 大分類
	sub_category TEXT,  -- 小分類
	work_item TEXT  -- 項目
);
```

先ほどの表をそのままテーブルに落とすならこうなるのだろう･･･。
問題点を挙げる。

- 一意性が保証できない。
- 冗長である。
- リレーション（関連付け）が不明確。
- パフォーマンスの低下
- ストレージの浪費

### 一意性

まず、マスタから項目を選択する場合、同じ項目があるとユーザはどちらを選べばよいか分からない。今回のような業務内容であれば同名は存在しないが、人名などは同名が存在する。

一意性を確保するためには、テーブルに主キーや一意キーを設定する。

### 冗長

同じ大分類や小分類が各レコードに存在する。大分類や小分類を変更する場合に、関連する全てのレコードを変更する必要がある。このような構造では、変更忘れなどによる不整合のリスクがある。

冗長さを取り除くために、各概念ごとにテーブルを分け、外部キーを利用したリレーションを設ける。

### リレーション

大分類・小分類・項目のように別テーブルに入れるデータに関連付けする場合は、外部キーを利用することでリレーションを明確化する。

### パフォーマンス

ドキュメント曰く、文字列は検索や比較などのパフォーマンスが他のデータ型に比べて劣っている。
対して、bool型やinteger型が高速である。

### ストレージ効率

このマスタを使うとなると、トランザクションテーブル（登録側の蓄積テーブル）に項目の文字列を積み上げていくことになる。
文字列はデータサイズが比較的大きい。
対して、bool型やsmallint型などは小さい。


## OK例

```sql
-- 大分類
CREATE TABLE work_categories (
	-- id SMALLSERIAL PRIMARY KEY,  -- ID（主キー）
    id SMALLINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- ID（主キー）
	code CHARACTER VARYING(10) NOT NULL UNIQUE,  -- コード（一意キー）
	name CHARACTER VARYING(20) NOT NULL UNIQUE ,  -- 名前（一意キー）
	description TEXT NOT NULL DEFAULT ''  -- 説明
);

-- 小分類
CREATE TABLE work_sub_categories (
	-- id SMALLSERIAL PRIMARY KEY,  -- ID（主キー）
    id SMALLINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- ID（主キー）
	category_id SMALLINT NOT NULL REFERENCES work_categories(id),  -- 大分類ID（外部キー）
	code CHARACTER VARYING(10) NOT NULL UNIQUE,  -- コード（一意キー）
	name CHARACTER VARYING(20) NOT NULL UNIQUE,  -- 名前（一意キー）
	description TEXT NOT NULL DEFAULT ''  -- 説明
);

-- 項目
CREATE TABLE work_items (
	-- id SMALLSERIAL PRIMARY KEY,  -- ID
    id SMALLINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- ID
	sub_category_id SMALLINT NOT NULL REFERENCES work_sub_categories(id),  -- 小分類ID（外部キー）
	code CHARACTER VARYING(10) NOT NULL UNIQUE,  -- コード（一意キー）
	name CHARACTER VARYING(20) NOT NULL UNIQUE,  -- 名前（一意キー）
	description TEXT NOT NULL DEFAULT ''  -- 説明
);
```

改善点は下記。

- テーブルを分けることで、冗長性を無くした。
- 主キーや一意キーを設定することで、一意性を確保。
- 外部キーを設定することで、関連性を明確化。また、「上位階層のデータが存在する場合のみ下位階層のデータが存在する」という整合性の取れた構造。
- idには整数型を利用することで高速化。連番であれば手動である必要が無いため、自動採番のほうが良い。（`IDENTITY` のほうが推奨されている ~~らしいが、pgAdminのGUIを使った行追加でデフォルト値が入らなかったのでシーケンスを採用した。~~ ）
- 基本的にストレージは有限であるため、smallint型やvarchar(n)型を採用することで、トランザクションテーブルのストレージの圧迫を緩和する。必要に応じてinteger, bigint のように拡張していけばよいが、優先すべきはデータサイズが小さい型。
- コードや名前を追加。それぞれに単独で一意キーを持たせることで重複防止。インデックスサイズが大きくなるが、重複防止による不整合のリスクを減らすほうを優先してみた。
- 説明カラムを設けることで、各レコードの情報の明確化。システム側で利用しなくても、マスタ管理者が見るカラムがあっても良いかもしれないので。

<font color="red">**2025/04/23追記**</font>

`IDENTITY` を使った際にpgAdminのGUIを使った行追加でデフォルト値が入らないと記載したが、再度試してみたら入った。当時のバージョン9.0では `[null]` と表示されたが、更新後の9.2では `[default]` と表示された。どうやらpgAdminのバグのようであるため、上記のSQL例を変更した。シーケンスはコメントアウトし、`IDENTITY` のカラム設定を記載した。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/366217/2ed4055c-f55d-4896-8c4c-58ee082adb8a.png)



# 3. まとめ

データを保存することだけでなく、データの整合性やパフォーマンスとの兼ね合いなどを考慮した設計をするべき。

# 4. その他

- 外部キーをつけることで、親テーブルの `UPDATE`, `DELETE` が遅くなる可能性がある。外部キーにはインデックスを付けたほうが良いかもしれない。

- トランザクションテーブルにはIDのみ保存すること。ストレージの圧迫緩和を図る。また、マスタテーブル内のコードや名前を後から変更しても、トランザクションテーブルへの影響させないことを図る。

- 有効/無効を切り替えるカラム `is_active` などがあると便利かもしれない。

- 個人的には、パフォーマンスやストレージの観点から、**文字列は悪である** と意識している。そのため、IDには整数連番を使用した。では、注文書IDなど日々大量に発行されるようなデータの場合も、対応テーブルにIDと文字列を管理すべきか？否、大量にデータを蓄積して様々なテーブルと結合するのは、それはそれでパフォーマンスの低下になりそう。IDで使っている連番が上限に達した際の処置も増えるかもしれない。あくまで、マスタは整数IDをキーにするのが良いと感じた。

# 5. 参考URL

- [PostgreSQL 16.4文書](https://www.postgresql.jp/document/16/html/)