😺

【アンチパターン】マスタテーブルを集約したテーブル設計はわかりにくいのでやめよう

2024/04/16に公開

db設計

単一参照テーブルとは

以下のようなテーブルを指す。

コードタイプ	コード	コード名
com_cd	C001	A商事
com_cd	C002	B化学
com_cd	C003	C建設
com_cd	C999	Z工業
pref_cd	P001	北海道
pref_cd	P013	東京
pref_cd	P047	沖縄
dep_cd	D001	E部署
dep_cd	D002	F部署
dep_cd	D003	G部署

RDBMSにおいて、あらゆるタイプのマスタテーブルを、一つのテーブルにまとめたもの」は単一参照テーブルと呼ばれています。
上記では「会社」、「都道府県」、「部署」と３つのマスターテーブルが一つのテーブルにまとめられている。
本来であれば次のような三つのテーブルになる。

取引先コード	会社名
C001	A商事
C002	B化学
C003	C建設
C999	Z工業

都道府県コード	都道府県名
P001	北海道
P013	東京
P047	沖縄

部署コード	部署名
D001	E部署
D002	F部署
D003	G部署

単一参照テーブルが発生する要因

正規化作業の特性上、テーブル数が増えていきます。
増えていくテーブルの中でも、「都道府県」や「取引先」といったマスタ類のエンティティはテーブル構造が類似している場合も少なくありません。
こうしたマスタ群を「集約したい/共通化したい」という発想から単一参照テーブルが発生します。
一見すると便利そうな発想ではありますが、メリットよりデメリットが大きいため、単一参照テーブルは避けられる傾向にあります。

メリット / デメリット

【メリット】

マスタテーブルの数が減るため、ER図/スキーマがシンプルになる
コード検索のSQLを共通化できる

【デメリット】

「コードタイプ」、「コード名」など各列ともに様々な値を格納するため、余裕を持ったサイズ（大きめの可変長文字列型）で宣言する必要がある
その余裕を超えた場合、別途新たなマスタテーブルが生まれる場合がある。
一つのテーブルに多くのレコードを格納するため、種類や数からレコード数が増え、検索のパフォーマンスが悪化する
コードタイプやコード値を間違えて指定してもエラーが起きず、バグに気づきにくい
ER図がすっきりするが正確さはなくなるため、かえって可読性を下げることになる

なぜわかりにくいのか

単一参照テーブルはシステムから見るとシーンや条件に応じて振る舞いが異なるテーブルとなります。
設計担当者は理解できても、新しくアサインされたメンバ何ができるのか、そのテーブルが何のためのものなのか理解しにくくなります。
こうした「わかりにくい」というものは、開発者の「システムへの理解」や「コミュニケーション」を阻害します。
毎度毎度メンバに有識者が設計意図を説明するのでしょうか？
独自ノウハウも大切と思いますが「原則」、「理論」といった共通言語を活用してみてください。

まとめ

単一参照テーブルは便利そうに見えますが、システム全体の可読性を低下させ、開発者の理解を阻害します。
その利点に魅了されるかもしれませんが、その裏には多くのリスクが潜んでいます。
設計の透明性とコミュニケーションの円滑化のために、原則と共通言語を活用しましょう。