😺
【アンチパターン】マスタテーブルを集約したテーブル設計はわかりにくいのでやめよう
単一参照テーブルとは
以下のようなテーブルを指す。
| コードタイプ | コード | コード名 |
|---|---|---|
| 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図がすっきりするが正確さはなくなるため、かえって可読性を下げることになる
なぜわかりにくいのか
単一参照テーブルはシステムから見るとシーンや条件に応じて振る舞いが異なるテーブルとなります。
設計担当者は理解できても、新しくアサインされたメンバ何ができるのか、そのテーブルが何のためのものなのか理解しにくくなります。
こうした「わかりにくい」というものは、開発者の「システムへの理解」や「コミュニケーション」を阻害します。
毎度毎度メンバに有識者が設計意図を説明するのでしょうか？
独自ノウハウも大切と思いますが「原則」、「理論」といった共通言語を活用してみてください。
まとめ
単一参照テーブルは便利そうに見えますが、システム全体の可読性を低下させ、開発者の理解を阻害します。
その利点に魅了されるかもしれませんが、その裏には多くのリスクが潜んでいます。
設計の透明性とコミュニケーションの円滑化のために、原則と共通言語を活用しましょう。
Discussion