先輩の作成したデータベースの構造が納得いきません。

Question

（解決済みにしますがなにか意見があればいただけるとうれしいです。）
社内で社員が利用する小規模なWEBアプリを構築することになりました。
データベース構築から入るということで先輩方が色々協議を重ねた結果テーブル構造が出来上がったのですが、その構造が簡単にあらわすと以下のようになっていました。

私はデータベース設計に詳しくないのですが、上記の図のようなデータベースの形というのはよくあるものなのでしょうか？
特に作業情報テーブルや項目マスタテーブル、作業場所グループマスタテーブルの形が教科書等で見たこと無いような形だったので気になりました。

プログラム的に扱いづらく、先輩方に構造を見直せないかお願いしたのですが、以下の理由でこの構造が良いといわれてしまいました。

1. 作業情報の項目数（カラム数）を動的に変えられて便利（後から作業の種類が増える場合がある）
2. 項目マスタの情報は本来もっと大量にあり、すべて作業情報テーブルに乗せようと思うとカラム数がとんでもないことになってしまう

私の知識不足で論破できなかったのですがあまり納得がいきません。私の言い分としては

1. 項目数を変えるならALTER TABLEすればいい。

なぜか先輩方はALTER TABLEが嫌いらしく納得してくれませんでした。（項目数が頻繁に変わる場合がある？）
2. カラム数がとんでもないことにならないように作業の内容を分析してテーブルを分けるべき。
項目マスタテーブルに持っている作業結果形式は本来カラムのデータ型であるべきだと思うのでここもテーブル分ければ無くせる？
3. 作業情報テーブルから特定の作業日をSELECTするのが大変。作業情報テーブルの作業結果のうち複数の値を条件にSELECTしようとするとSQLが長くなってしまう
先輩曰く１つの作業にどの項目コードが存在するかを別の設定ファイルに記述しておき、ある社員がある作業日に実施したデータをSELECTする際は、条件文に設定ファイルから読み込んだ項目コードの一覧をOR条件で付けて検索しろとのことなのですが・・・
4. 作業場所グループに属する作業場所を変えようとしたら結局ALTER TABLEが必要になる。（項目数より頻繁に変わらないからいいのか？）

できれば構造を見直していただきたいのですが、知識と経験に自信が無く構造がこれこれこうだからだめと言い返しきれません。
このデータベースの問題点を指摘していただきたいです。
私が間違えている場合はその点も指摘していただけるとうれしいです。
また調べていると、作業情報テーブルの構造はSQLアンチパターンの「ＥＡＶ」と呼ばれる構造に見えるのですがあってるでしょうか。
拙い日本語で申し訳ありませんがよろしくお願いします。

Comments

[date]

このデータベースは何でしょうか?Mysql PostgreSQL　などわかればタグに追加していただけると

[koronatail]

利用しているデータベースはSql Serverです。タグに追加しました。

Answer 1

• システムの規模
• システム要件
• システムのライフサイクル

上記はあくまで一例ですが、
これらが変わるとテーブル設計も変化します。

例えばシステムのライフサイクルを長くしたいのであれば、
基本的には正規化を推し進め、
データ整合性と拡張性を重視したテーブル設計が必要です。

またシステム要件が、
ビジネスインテリジェンスやデータウェアハウス系のシステムとなると、
パフォーマンスを考慮してあえて非正規形のテーブル中心に据えたテーブル設計となります。

テーブル設計も要件次第で正解となり得る考え方も変わるので、
常に教科書にある設計が正解とは限らないことに注意しましょう。

例示してもらったテーブル例を見ると、
個人的に気になった点はOrlofskyさんと同様で、

• 作業情報テーブルの「作業名」と、

項目マスタの「項目名」で事実の二重管理をし一事実一箇所の原則に違反しているように見える点。

• 作業情報テーブルの「作業結果」というカラムに、数値や文字列のデータが混在している点。

(項目マスタと合わせて質問者さんの挙げた「EAV」アンチパターンには該当してると思われますね)

• 作業場所グループマスタで繰り返し項目が存在している

(これはアンチパターンで言うところの「マルチカラムアトリビュート」に該当)

ただそれぞれ明確な理由があって、
そのような設計にしているのであれば、問題視する必要はないと思われます。

以下に参考程度に個人的に問題がないと判断する基準を記載します。
(※あくまで当方の主観なので鵜呑みにはしないで下さいね・・・)

---

1つ目は作業情報テーブルへのデータ量が膨大でデータ参照頻度も高いから敢えて非正規化してるとか、
作業名は場合によってはマスタ登録外の値を入れることがあるとかの意図があるなら問題なし。

2つ目は1カラムに色々な属性データが入るテーブルはごく少数で、
あちこちのテーブルで同構成を取っていないなら問題なし。
(色々な値を格納した汎用テーブルとかはよく使われる。ただデータ型の恩恵を受けたいなら「作業結果(数量)、作業結果(文字列)」みたいにあえてカラムを分けるのは１つの手かなと)

3つ目は将来的にも、
項目の増減自体がほとんど起こりえないし、一気に増え流ようなこともないなら問題なし。

Comments

[koronatail]

コメントを頂く中で、確かに拡張性など要件に沿った視点というのが無かったなと反省しています。
頂いた判断基準も参考にしつつ、見直してみたいと思います。

Answer 2

つらつら、っとみて、

作業場所グループマスターテーブル の 場所1, 場所2, 場所3 は繰り返しになりますから、
第一正規化として繰り返しを排除しなければなりません。

作業場所マスタテーブル に 場所グループID を持てば 作業場所グループマスターテーブルが不要になるかも？
繰り返しを残した時と繰り返しを排除した時で各いくつかSQLを書いてみては？

作業情報テーブル の 作業名 は 作業マスタテーブル の 項目名 だと思われるから不要では？

空白 は ソフトによっては暗黙のRTRIMされてNULLになることがあるから、空白を意味のあるコードにするのは勧めません。(汎用機の知識しかない人に空白に拘る人が多い)

Comments

[koronatail]

繰り返しの排除という基本的なフレーズがでてきませんでした・・・・。
正規化についてもう一度勉強しなおします・・・。
実際にSELECTを行うSQLを組んで見直して見たいと思います。

基準について聞いていないのですがデフォルトが空白だったりNULLだったりばらばらなのでそこも確認してみます。

Answer 3

基本的には正規化できているかという視点で見ればよいと思います。
その視点でみると概ね問題ないように思えます。

細かく解説します。

目数を変えるならALTER TABLEすればいい。

なぜか先輩方はALTER TABLEが嫌いらしく納得してくれませんでした。（項目数が頻繁に変わる場合がある？）

項目を追加すると、プログラムの修正が必要になります。データ追加とプログラム修正では十倍以上の作業量の差がでます。ALTER TABLEは避けるべきです。

カラム数がとんでもないことにならないように作業の内容を分析してテーブルを分けるべき。

項目マスタテーブルに持っている作業結果形式は本来カラムのデータ型であるべきだと思うのでここもテーブル分ければ無くせる？

あくまで項目は項目の意味で分けるべきです。データ型はカラムにつけられる制約の1つでしかありません。テーブル定義でかけられない制約などいくらでもあります。
カラムのデータ型で分けたら検索においては多少のメリットがありますが、作業項目とセットで検索することになると思いますので、それほど大事ではないと思われます。

作業情報テーブルから特定の作業日をSELECTするのが大変。作業情報テーブルの作業結果のうち複数の値を条件にSELECTしようとするとSQLが長くなってしまう

先輩曰く１つの作業にどの項目コードが存在するかを別の設定ファイルに記述しておき、ある社員がある作業日に実施したデータをSELECTする際は、条件文に設定ファイルから読み込んだ項目コードの一覧をOR条件で付けて検索しろとのことなのですが・・・

SQLが長くなるのはいやだと思います。前にも書いた通り、項目追加は手間がかかりすぎるので、検索はあきらめたほうが無難です。上手にツールなどを作成して、複雑なSQLを手で打たなくて済むように工夫するとよいと思います。

作業場所グループに属する作業場所を変えようとしたら結局ALTER TABLEが必要になる。（項目数より頻繁に変わらないからいいのか？）

そうですね。この点は同意します。理想的には下記がいいかもしれません。
場所グループ[場所グループID, グループ名]
場所グループ明細[場所グループID, (No,) 場所ID]

面倒なので下記でもいいですが・・・
場所グループ[場所グループID, グループ名, 場所ID]

Comments

[koronatail]

一つ一つありがとうございます。
＞項目を追加すると、プログラムの修正が～～～
確かにその通りな気がしました。どうしてもカラムの数が定まらない場所はこうしたほうがいいということですかね。

＞あくまで項目は項目の意味で分けるべき～～～～
途中で質問の図を変えてしまったのですが、たとえば作業情報テーブルの「歌う・踊る」と「A・B・Cを作る」という作業が、作業の中でも別の分類として分けられるのであればテーブルとして分けられるのではないかと考えました。

＞SQLが長くなるのはいやだ～検索はあきらめたほうが無難～
どうしても項目の値を参照した検索を行いたいです・・・。
SQL文を作成するツールを製作したほうがいいということでしょうか。まだ実際にSQL文を組み立ててみていないのですが、あまりに複雑になるようであれば考えて見ます。

＞～理想的には下記が～
例までありがとうございます。明細として分けると別の情報も持たせやすそうですね。
相談してみます。

[iwamoto_takaaki]

> どうしても項目の値を参照した検索を行いたいです・・・。
SQL文を作成するツールを製作したほうがいいということでしょうか。まだ実際にSQL文を組み立ててみていないのですが、あまりに複雑になるようであれば考えて見ます。

返事が遅くなりましたが、私の場合以下の手順で作業していました。
１．手間のかかる検索をするがSQLと結果のログを保存する
２．SQLをブラッシュアップする
３．テンプレート化して、キーとなる項目を設定すれば実行できるようにする
４．テンプレートをパラメータに変更し、外部からセットできるようにする
５．コマンドラインから実行できるようにする

もちろん、頻度や展開先によってはビューや画面を作るという選択肢もあります。
とにかく、ブラッシュアップさせていけばそのうち一発で実行できるようになります。

Answer 4

規模や改修の難度によって設計が教科書から外れてもいいと思います。

小規模ならマスターやIDなしで全部文字とかでもOKと私は思います。
IDが必要ならあとからALTERとUPDATEかければいいじゃんと。

※すべての事に対応できる教科書があれば教えてください！

Comments

[koronatail]

頭が固かったです・・・。
改修・変更を行うことを前提してデータベースを作る必要がありますよね。
これから経験をつんで行きたいと思います。

Answer 5

個人的にはですが運用開始後にDB構造構造をALTER TABELなどで追加するのはご法度としております。
そのために多対多などで多少効率が悪くともDB設計することがあります。
フレームワークを使わずに直接SQLを書くようなプログラムだと外部キーに継ぐ外部キーで大変かもしれませんね
プログラムを作る際にこれを使えなど言われてないでしょうか？

Comments

[koronatail]

改修の際の効率を取って設計を行っているということですね。確かに言われてみればALTER TABLEしてしまうとプログラムの修正が大変そうです・・・。
フレームワークはSpring Frameworkを利用するように言われていました。Spring Data JPAについて学習中ですがこれでうまくいくのでしょうか。

[jdbb]

ちょっと調べてみましたがSpring Frameworkなら
モデルさえちゃんと設定できていれば勝手に引っ張ってこられるそうなので
あとはこのフレームワークを調べながら進めていけばいいと思います。

[koronatail]

ありがとうございます。
このままで試してみたいと思います。

Answer 6

私が気になるのは、これを使って何をしたいか、という目的です。
もし「作業員○○がいつどこで何をしてる」が分かればいい、的なものであれば、これでのいいのかなと思いますし、社内ですからとりあえずこの程度でローンチして、あとは使いながら変更していけばいいという考え方にも納得できます。
ただ、作業内容やその結果にフォーカスする（分析する）ことが最初から分かっているのであれば、既に他の方のご指摘もありますが、作業名・作業結果のカラムについては検討の余地があると思います。例えば担当者別で作業結果の成功率を見るとか、担当者ごとに作業の種類に偏りがないかチェックするなど。「できた・できなかった」と「OK・NG」はエンドユーザにとって何が違うのか（あるいは違わないのか）も気になります。

ただいずれにしても作業場所と作業場所グループの２テーブルについては、私も整理すべきと思います。
BIなど分析系でファクトテーブルをスタースキーマにすることはありますが、これはきっと違うだろうと想像します。