DBのマスターテーブルの設計に関して

Question

DBのマスターファイルに関してなんですが、
画面上に表示されているラジオボタンリスト、チェックボックスリスト、セレクタの各要素などの表示と裏側のコードをマスター化したいとします。

その場合皆さんはどのような設計にしているのでしょうか？
テーブルのレコードにどのようなものを置いておけばいいか、どういうテーブルが必要でどのようなリレーションを張ればいいのか。またその利点と欠点を教えてください。よろしくお願いします。

ちなみに僕の場合以下のようにしていますが、便利な反面DB設計的には納得がいきません。その理由も書きますが、それ以外に利点欠点があれば、また改善できる余地があればそれもご教授願います。

M_CODENAME_KBN
CODE_KBN_CD //PK
KBN_NM //区分名
SORT_NO //ソート順
UNDISPLAY_FLG //非表示フラグ
UPDATE_TIME //最終更新時間
UPDATE_USER //最終更新者
M_CODENAME
ID //PK
CODE_KBN_CD //区分のPK
CODE_CD //コード
CODE_NM //表示名
CODE_SNM //表示名略称
CONVERT_CD //他社と連携する必要があるばあい変換に使用
UNDISPLAY_FLG //非表示フラグ
DEL_FLG //論理削除フラグ
SORT_NO //ソート順
UPDATE_TIME //最終更新時間
UPDATE_USER //最終更新者

M_CODENAME_KBNはチェックリストやラジオボタンリスト、セレクタ全般の大分類です。
M_CODENAMEは大分類中の各コードと名称が入っている小分類です。
IDはCODE_KBN_CDとCODE_CDで事足りますが、CODE_KBN_CDとCODE_CDのみだと、マスターを編集する管理画面などのコーディングが煩雑になる恐れがあると思い（たとえばAのコードがBに、BのコードがAだったので変更したいとかある場合、PKを持っていないと一時的に同じ主キーになってしまう）、あえて連番をつけました。

この構成のいいところは一括管理ができること。アプリケーション側のカスタムコントロールなどのライブラリにも容易に組み込めるため、プログラマはコードを入れる作業を簡略化できることなどがあります。

逆にデメリットとして、設計の観点から、CODE_CDとCODE_NMというペアはCODE_KBN_CDが違うと全く別のものです。
ある行のCODE_CDは曜日を表すコードだが、別の行のCDは大中小を表す。また別の行のCDは都道府県が入っており、、、などとダブルミーニングどころの話じゃありません（表示物を表すということのみに関しては一貫性はありますが）。

Answer 1

これは、顧客が自由に設定ダイアログのようなものを組み上げられる仕組みを作ろうとしている、という解釈でよろしいでしょうか。
そのような機能要求であればこの設計しかありえないかと思います。
そうでないとしたら、この機能はもともとRDB向きではないのだという解釈からMongoDBのような文書型DBを使うとか、PostgreSQLのJSON型を（コントロール定義にもデータ保存にも）使うとかいう方向になります。

この問題は、SQLアンチパターンの「Entity Attribute Value」にだいたい該当していると言えるでしょう。
資料をご参考までに。
5章 EAV (エンティティ・アトリビュート・バリュー)
SQLアンチパターン

Comments

[yuu341]

やはり、皆さんの言われるようにマスタ定義はコード：名称の単純な組み合わせにおいてでも個別に分けた方が無難ということですか。
社内でもそういう実装ばかりで個人的にもそこまで悪くないとは思っていたのですが
他のSQL指南書にも、1つの列にダブルミーニング、トリプルミーニングとなるようなことをしてはならないとも書かれていますし、上記サイトのEAVの説明を拝見する限りではあとあと尾を引きそうな問題に直面する可能性がありますね。
次から設計する場合、他の方もおっしゃる通り別実装を検討する方がいいか、そこを汎用的にするかどうかを要検討という方針にしたいと思います。

Answer 2

はじめまして。
私の経験からですが、マスタ系のテーブルは役割ごとに小分けにした方が良いと思います。
各テーブルが何のマスタなのか一目でわかるようになっていれば、保守しやすいですし
更新系のPGもシンプルなもので済みます。
(例えば、会社マスタ、カレンダーマスタなど)

現状のテーブルは汎用性はありますが、一目で何のマスタなのか分かり辛いものになってしまっていると思います。
各項目の影響度などもPG側で考慮しないといけないですし。。(UNDISPLAY_FLGが冗長である、CONVERT_CDはPG側からすると良くわからない、など)
また、M_CODENAME、M_CODENAME_KBNはコードマスタのようなものとして纏めたほうが良いかもしれませんね。

Comments

[yuu341]

返信遅くなり申し訳ありません。
解答ありがとうございます。

マスタの構成に関してですが、おっしゃる通り汎用性に欠けるものに関しては上記の仕様適用外ということで、別マスタを設けています。
例えば住所などは階層構造を持たせたかったりしますし、ユーザ情報などは有効期限やハッシュ化されたパスワード、ロール関係などを持たせたりとこの限りではありません。

今回このテーブルに使用するようなものの対象となるのは
1:性別(1:男 2:女 3:不明)や
2:年号(1:平成 2:昭和 3:大正 4:明治)
3:国名(1:日本 2:アメリカ 3:中国 4:イギリス 5:フランス ....)
などのような、単純なコードと名前の変換のみのものを選択させる方式のものです。

おそらくは文中に出てきたコードマスタというのが僕の言っているもので間違いないと思います。
コードマスタとして考えるならそれほど間違った構成でもないんですかね・・・

補足になりますが
UNDISPLAY_FLGに関して、これを書いている理由としては以下の想定をしているためです。
　1.今まで選択肢一覧に選択可能として表示してきたが、この項目を省きたい
　2.ただし、選択不可になるとはいえ今までユーザが入力したデータはそのまま残したい。

CONVERT_CDに関しては、これは別システムと連携をするためのものであり、主にPLSQL内部や、変換をする専用ルーチンにて使います。
例えば、他社システムと自社システムとを連携する際に、両者間でデータを同期させる必要がありますが、その際にこの変換テーブルを使います。
PG側は連携担当者でない限り、普段これを意識する必要はありません。