1. はじめに
PostgreSQLに限った話ではないが、データベースを扱う上でマスタテーブルを作成することがある。
当たり前のように教科書にお決まりの構造（IDと項目の組み合わせ）が載ってはいるが、なぜそのような構造になるのかを記載する。
2. マスタテーブルの設計
次のテーブルを作る場合どのような構造が良いかを考える。
- 業務日報を付けるため、業務の種類をマスタとして作成する。
- 業務マスタは、大分類・小分類・項目の3階層で構成する。
マスタ化したい内容を表でまとめると、次のようになる。
（これが適切な分類かどうかは置いておく。）
| 大分類 | 小分類 | 項目 |
|---|---|---|
| 定常業務 | 開発 | 設計 |
| 定常業務 | 開発 | コーディング |
| 定常業務 | 庶務 | 日報作成 |
| 非定常業務 | 開発 | メンテナンス |
| 非定常業務 | 開発 | ログ分析 |
| 非定常業務 | 庶務 | クレーム対応 |
NG例
CREATE TABLE mst_works (
category TEXT, -- 大分類
sub_category TEXT, -- 小分類
work_item TEXT -- 項目
);
先ほどの表をそのままテーブルに落とすならこうなるのだろう･･･。
問題点を挙げる。
- 一意性が保証できない。
- 冗長である。
- リレーション（関連付け）が不明確。
- パフォーマンスの低下
- ストレージの浪費
一意性
まず、マスタから項目を選択する場合、同じ項目があるとユーザはどちらを選べばよいか分からない。今回のような業務内容であれば同名は存在しないが、人名などは同名が存在する。
一意性を確保するためには、テーブルに主キーや一意キーを設定する。
冗長
同じ大分類や小分類が各レコードに存在する。大分類や小分類を変更する場合に、関連する全てのレコードを変更する必要がある。このような構造では、変更忘れなどによる不整合のリスクがある。
冗長さを取り除くために、各概念ごとにテーブルを分け、外部キーを利用したリレーションを設ける。
リレーション
大分類・小分類・項目のように別テーブルに入れるデータに関連付けする場合は、外部キーを利用することでリレーションを明確化する。
パフォーマンス
ドキュメント曰く、文字列は検索や比較などのパフォーマンスが他のデータ型に比べて劣っている。
対して、bool型やinteger型が高速である。
ストレージ効率
このマスタを使うとなると、トランザクションテーブル（登録側の蓄積テーブル）に項目の文字列を積み上げていくことになる。
文字列はデータサイズが比較的大きい。
対して、bool型やsmallint型などは小さい。
OK例
-- 大分類
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 のように拡張していけばよいが、優先すべきはデータサイズが小さい型。
- コードや名前を追加。それぞれに単独で一意キーを持たせることで重複防止。インデックスサイズが大きくなるが、重複防止による不整合のリスクを減らすほうを優先してみた。
- 説明カラムを設けることで、各レコードの情報の明確化。システム側で利用しなくても、マスタ管理者が見るカラムがあっても良いかもしれないので。
2025/04/23追記
IDENTITY を使った際にpgAdminのGUIを使った行追加でデフォルト値が入らないと記載したが、再度試してみたら入った。当時のバージョン9.0では [null] と表示されたが、更新後の9.2では [default] と表示された。どうやらpgAdminのバグのようであるため、上記のSQL例を変更した。シーケンスはコメントアウトし、IDENTITY のカラム設定を記載した。
3. まとめ
データを保存することだけでなく、データの整合性やパフォーマンスとの兼ね合いなどを考慮した設計をするべき。
4. その他
-
外部キーをつけることで、親テーブルの
UPDATE,DELETEが遅くなる可能性がある。外部キーにはインデックスを付けたほうが良いかもしれない。 -
トランザクションテーブルにはIDのみ保存すること。ストレージの圧迫緩和を図る。また、マスタテーブル内のコードや名前を後から変更しても、トランザクションテーブルへの影響させないことを図る。
-
有効/無効を切り替えるカラム
is_activeなどがあると便利かもしれない。 -
個人的には、パフォーマンスやストレージの観点から、文字列は悪である と意識している。そのため、IDには整数連番を使用した。では、注文書IDなど日々大量に発行されるようなデータの場合も、対応テーブルにIDと文字列を管理すべきか？否、大量にデータを蓄積して様々なテーブルと結合するのは、それはそれでパフォーマンスの低下になりそう。IDで使っている連番が上限に達した際の処置も増えるかもしれない。あくまで、マスタは整数IDをキーにするのが良いと感じた。