質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.22%

[DB設計]汎用区分マスタについて

解決済

回答 2

投稿

  • 評価
  • クリップ 6
  • VIEW 19K+

mona

score 28

データベース設計について質問です。

現在、Webシステムの基本設計段階で
「汎用区分マスタ」というテーブルを設計しています。

・多言語対応するために、一つの区分値に対して
複数の言語での名称を登録する必要がある
・今回は日本語対応のみ
・現在は「マスタを更新しない、参照のみの区分」しか存在しない
・将来的にはカスタマイズでマスタメンテ画面を追加し、
区分や区分値が更新対象になる可能性がある
という前提です。

開発環境は
言語:Java,JavaScript
DB:SQL Server
です。

過去に他の案件で使用していた同じ役割のテーブルを参考に
現在は下記のようなテーブル定義になっています。

分類コード    varchar(30) PK
区分値    varchar(30) PK
言語コード    varchar(3) PK
表示名    nvarchar(100) NOT NULL
表示順    smallint NOT NULL
備考    nvarchar(200)
予備項目1    nvarchar(200)
予備項目2    nvarchar(200)
予備項目3    nvarchar(200)
削除フラグ    bit
登録日時    datetime2
登録者ID    nvarchar(20)
登録APP    nvarchar(50)
更新日時    datetime2
更新者ID    nvarchar(20)
更新APP    nvarchar(50)

例えば、課税区分と国なら(一部抜粋)
分類コード    区分値    言語コード    表示名    表示順
KAZEI_KBN    1    JPN    内税    1
KAZEI_KBN    2    JPN    外税    2
KAZEI_KBN    3    JPN    非課税    3
KUNI_KBN    AD    JPN    アンドラ    16
KUNI_KBN    AE    JPN    アラブ首長国連邦    8
KUNI_KBN    AF    JPN    アフガニスタン    5


といったデータを格納します。

自分で調べてみたのですが

■メリット
・マスタメンテ画面が1つで済む
・分類コードの条件を変更すれば複数の機能や画面で表示名の取得SQLを使用できる
・個別のマスタを持つよりテーブル数が少なくなる
・新しい分類が増えてもテーブルを増やさなくて良い

■デメリット
・データの件数が多くなり、検索コストが上がる
・テーブルのレコードを見ないとどんな値を保持しているかわからない
・余裕を持って桁数の大きい項目や、予備カラムを用意している分データ容量が増える

というような内容しかわからず、個人的な感覚としては
「テーブルの数が増えても、個別のマスタを準備した方が良いのでは?」
と思うのですが、上手く説明できません。

なんとなく、個別マスタを持った方が良い条件は
・複数の画面でドロップダウンリストに表示する
・区分と名称以外の項目(上位分類など)がある
・専用の区分値更新処理(他システムからの連携、マスタメンテ画面など)がある
・区分値の有効期限がある(キーに期間の情報を持たせる)
かな?と思っているのですが
・それ以外の「主に区分値と名称の表示しか使用しない区分」は
全て汎用区分マスタにつっこんでしまえば良いの?
・将来マスタメンテ画面が出来た場合を考えると
できるだけ汎用区分マスタを使用した方が良いの?
・予備カラムって無駄じゃないの?
どんなデータが入るかわからないから桁数を
大きめに持っておくのってどうなの?
といった疑問が湧いてきます。

私にとって、初めてのデータベース設計で困っています。
区分値のマスタテーブルはどのように設計すればよいのでしょうか?

解説、経験談(設計・開発)もあればお願い致します。
このようなテーブル設計について参考になるサイトや書籍などがあれば
教えてください。
宜しくお願い致します。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+5

「汎用区分」の扱いについては、
Entity-Attribute-Value
と呼ばれるSQLアンチパターンに該当するかと思います。

以下のページに同様の質問をされている方がいらっしゃる様に思いました。

「メタデータテーブル」という設計 / 考え方について、ご意見をお聞きしたいです
 

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/01 13:16

    ありがとうございます。
    SQLアンチパターンなのですね。

    リンク先の内容も非常に参考になりました。
    1. データの整合性を担保できない
    2. SQLを構築するの面倒
    など、カスタマイズでマスタメンテ画面を作成するときは
    特に注意が必要ですね。。

    今回は要件定義の段階で「汎用区分マスタを使用する」と決定しているので、
    デメリットや実装上の注意を理解して
    立てられる対策を立てた上で使用するようにします。

    「なんとなく良くない」の理由が明確になり、助かりました。

    キャンセル

+2

Entity-Attribute-Value は汎用機からオープン系への移行で汎用機で使っていたからそれを継承する、ってお客様で多いです。
汎用機では汎用区分毎にメンテ画面を作るのにそれなりのコストと時間がかかりますが、オープン系ではテーブル毎にメンテ画面を作るのはさほどコストはかかりません。ER図は解りにくいし、SQLがかなり複雑になります。

あと、社外とデータ交換する為には国コードはISO_3166-1にして置くとデータ交換する際にコード変換が少なくて済みます。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/08 10:08

    回答ありがとうございます。

    > 汎用機からオープン系への移行で汎用機で使っていたからそれを継承する、ってお客様で多いです。
    「これまで使っていたから」「区分はまとめて1画面でメンテしたい」というお客様の要望と、上流工程を行っている上司がEntity-Attribute-Valueを推奨しているのか自社で設計を行ったほとんどのシステムにこのようなテーブルが存在しています。。
    > 汎用機では汎用区分毎にメンテ画面を作るのにそれなりのコストと時間がかかりますが、オープン系ではテーブル毎にメンテ画面を作るのはさほどコストはかかりません。ER図は解りにくいし、SQLがかなり複雑になります。
    開発を行う身からすると困ったことばかりですね。
    同じロジックを流用してテーブル単位に画面をタブで切り替える方など、今後他の方法も提案してみようと思います。

    > あと、社外とデータ交換する為には国コードはISO_3166-1にして置くとデータ交換する際にコード変換が少なくて済みます。
    こちらも教えて頂きありがとうございます。
    例に挙げたのは既存システムにあったデータなのですが、国の区分値は「ISO 3166-1 alpha-2」になっているみたいです。
    「コード変換で苦労する」という経験は他のシステムで経験したことがあるので、その点も留意して設計・開発を行いたいと思います。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.22%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる