テーブルは事実を正しく記録できるように設計する

Webアプリケーションの開発案件に携わっています。
多分、テーブル設計ミスった気がします。

カラム管理かレコード管理か

例えば、中学校の５教科（国語、英語、数学、理科、社会）の成績を管理するアプリを作りたいとします。

成績を管理するためのテーブルとして以下の２パターンを考えます。

パターン１：カラムによる管理

管理したい要素が５教科と決まっているので、科目ごとのカラムを用意して以下のように管理する。

成績ID	生徒ID	国語	英語	数学	理科	社会
1	10	5	5	4	4	3
2	20	4	3	5	5	4

テーブル定義は以下のイメージ。

カラム	型	PK
成績ID	integer	〇
生徒ID	integer
国語	integer
英語	integer
数学	integer
理科	integer
社会	integer

生徒の名前などは別テーブルで管理されている想定。ここでは省略。

パターン２：レコードによる管理

次は生徒・科目の組み合わせごとにレコードを分けるパターン。
データは以下のイメージ。

成績ID	生徒ID	科目	成績
1	10	国語	5
2	10	英語	5
3	10	数学	4
4	10	理科	4
5	10	社会	3
6	20	国語	4
7	20	英語	3
8	20	数学	5
9	20	理科	5
10	20	社会	4

テーブル定義は以下のイメージ。

カラム	型	PK
成績ID	integer	〇
生徒ID	integer
科目	varchar
成績	integer

成績IDは自動採番の主キー。
生徒IDと科目名の組み合わせは一意になります。
科目も生徒のように別テーブルで管理しても良いですが、ここでは説明簡略化のための文字列で直接保持します。

結論

テーブル設計として、どちらの方が良いでしょう。
結論としては、パターン２のレコード単位で管理する方式がおすすめです。

パターン１の方式を採用してもよいのは、以下のいずれかの場合のみです。

• 管理対象となる属性（今回の例では科目）が不変である場合
• 管理対象となる属性が減ることはあっても増える可能性はない場合

ただし、上記の条件を満たす場合であっても、データモデリングの観点からパターン２のレコード単位の管理方式の方が良いと思います。

カラム管理の問題点

ここでは、パターン１，パターン２のいずれかのテーブル構造に対して基本的なCRUD処理を実現するWebアプリケーションを考えます。

アーキテクチャとしては一般的なMVCモデルで開発することを想定。
ビジネスロジックをサービスやDomainモデルで、DBアクセスはDAOなどで実現し、取得した値はEntityに格納するような、いわゆるよくある従来のMVCパターン。

図にすると以下のようなイメージ。
矢印はざっくり依存先を表していると考えてください。

仕様変更で科目が追加！

ここで、例えば元の５教科に「美術」と「体育」も加えた７科目の成績を管理したいという要望が出たとします。
その場合に、テーブル設計で「パターン１：カラム管理している場合」と「パターン２：レコード管理している場合」とで、それぞれどのような影響があるかを見ていきます。

パターン１：カラム管理の場合

成績をカラム管理をしている場合、私の見立てでは影響範囲は以下のようになります。

見ての通り、かなり影響が出ます。変更の詳細は以下。

• DB：ALTER TABLEで対象テーブルにカラムを追加する必要がある
• Entity：追加したカラムに合わせてプロパティ（フィールド）を追加する必要がある
• DAO：追加したカラムに合わせてSQL文を追加する必要がある
• Domainモデル：Entityと同じくプロパティの追加が必要
• Viewモデル：上に同じくプロパティの追加が必要
• View：追加した要素を表示するための修正が必要

ControllerやServiceについては、モデル同士の変換処理をControllerやServiceで実現している場合に影響がでる可能性が高いです。

次に、CRUD観点で影響範囲を見ていきます。

• Create（insert）：影響の可能性あり
• Read（select）：影響あり
• Update：影響あり
• Delete：影響なし

deleteはレコード単位の処理なのでおそらく影響は受けないでしょう。
insertは成績用のカラムがデフォルト値nullを許可していれば影響を受けない可能性はあります。
selectとupdateは増えたカラムによって影響が出る可能性は高いでしょう。

パターン２：レコード管理の場合

レコードで管理している場合の影響範囲は私の見立てでは以下。

カラム管理の時と比べると、影響度のレベルが１段下がるイメージ。
CRUD観点だと以下になります。

• Create（insert）：影響なし
• Read（select）：影響の可能性あり
• Update：影響なし
• Delete：影響なし

deleteで影響が出ないのは同じですが、こちらパターンはカラム追加がないのでinsertやupdateもおそらく影響は出ないでしょう。
selectに関しては複雑なSQLを組んでいる場合には影響が出る可能性はあるけれど、ほとんどの場合影響はないでしょう。

「美術だけは表示の仕方を変える」とか、「体育だけは成績の付け方が変わる」といった、特定の科目に依存する固有のロジックが必要になる場合、ドメインモデル周りでの修正は必要になる可能性はありますが、それでも、全体としてはカラム管理の時ほど大きく影響はなくなることが予想されます。

というわけで、カラムによる管理をしている場合、管理対象の要素が変動すると影響範囲が大きくなるので、レコード管理するテーブル設計にしましょう、というのがここでの主張。

カラム管理のテーブルで実装しました

上記の説明から何となく程度想像できるかと思いますが、実際の案件にて、レコード管理した方が良さそうな情報をカラム管理するテーブル設計にしてしまい、仕様追加の対応で影響範囲が大きくなってしまいました。。

承認系のテーブル

現在関わっているシステムは、とある業種向けの業務管理システムなのですが、その業種では業務中に多くの承認フローがあります。
承認フローもシステム化の対象となっているのですが、承認フローのテーブル定義を以下のようにしてしまいました。

カラム	型	PK
承認ID	integer	〇
業務ID	integer
上長ID	integer
上長承認日時	timestamp
係長ID	integer
係長承認日時	timestamp
課長ID	integer
課長承認日時	timestamp
部長ID	integer
部長承認日時	timestamp
...	...

※実案件のカラム名からは変更しています。
実際の案件では役職名は上記の役職ではありませんが、イメージとして、上記のような、役職者ごとのユーザーIDと承認日時を１レコードにまとめて保持するようなテーブルとして設計してしまいました。

開発案件のスケジュール的には順調に進みましたが、本番稼働を前にユーザーテストをしてもらっていたところ、「とある条件の時に承認者が増える」という仕様が発覚！

カラムを追加して対応しようとしたところ、

「あれ？影響範囲多くね？」
「・・・」
「これ、テーブル設計ミスったのでは？？」

となりました。
何が良くなかったかは前述した通りです。

幸い、影響範囲が大きかったとはいえ、実装が手に負えなくなるほど複雑なものにはなりませんでした。

• 言語がJavaなのでフィールドを追加してもコンパイル時点で影響範囲がある程度分かる
• 運用が始まったばかりなので技術的負債もそれほど多くはない
• テストをある程度は自動化している

これらの理由から、影響範囲の割には修正自体は案外速く終わりました。
とはいえ、今振り返るとレコードで管理していた方がもっと簡単にできたのではないか？と思う次第です。

データモデリング観点での比較

ここまでは、プログラムへの影響範囲という観点からカラム管理よりもレコード管理の方が良いのでは？という主張をしましたが、データモデリング観点ではどうでしょうか。

データの基本は「事実」を「正しく記録すること」ことだとされています。
そう考えると、データモデリングの観点でもパターン２のレコード管理の方が良さそうに思います。

例えば、以下のテーブル構造で、課長と部長が承認をしたデータが書き込まれたレコードがあるとします。

承認ID	業務ID	承認課長ID	課長承認日時	承認部長ID	部長承認日時
1	1	10	2025-01-10 15:00:00	15	2025-01-10 16:00:00

ここで、業務内容に不備があり、承認の差し戻しが必要になった場合はどうすれば良いでしょう。単純に考えれば、承認者のIDと承認日時のカラムをnullに更新すれば承認を取り消すことができそうです。

承認ID	業務ID	承認課長ID	課長承認日時	承認部長ID	部長承認日時
1	1	null	null	null	null

そして、業務内容が修正されて、再度承認が行われると、承認者のIDと承認日時が更新されます。

承認ID	業務ID	承認課長ID	課長承認日時	承認部長ID	部長承認日時
1	1	10	2025-01-10 17:00:00	15	2025-01-10 18:00:00

最後まで承認されたのかどうかを知りたいだけであれば、このデータ構造でも特に問題ないです。
しかし、このデータ構造の場合、「15時に課長が承認した事実」「16時に部長が承認した事実」「承認を取り消した事実」がデータ上なかったことになってしまいます。

レコードで管理する場合、以下のようなデータ構造にすれば、全ての承認と解除の事実を記録できます。

承認ID	業務ID	役職	承認者ID	承認日時	承認解除日時
1	1	課長	10	2025-01-10 15:00:00	2025-01-10 16:30:00
2	1	部長	15	2025-01-10 16:00:00	2025-01-10 16:20:00
3	1	課長	10	2025-01-10 17:00:00	null
4	1	部長	15	2025-01-10 18:00:00	null

プログラム観点でどちらの方が実装しやすいかについては人によって意見は分かれるところかもしれませんが、承認フローがシステムのスコープに含まれているのだとすれば、承認に関する事実を正しく記録しておくことがデータモデリングとしてはあるべき形と言えるでしょう。

仮にシステムのスコープとして承認の履歴まで見られることを求めていないとしても、問題が起きた時の調査や、業務改善のためのデータ分析・活用などを見据えると、承認の事実が全て履歴として残っている方が良いと言えるでしょう。

その他の仕様変更

「特定の条件の時だけ承認者が増える」という仕様変更が起きたと書きましたが、実は承認フロー周りで他にも後からの仕様変更がいくつかありました。

• 業務の種別によっては、一部の承認が不要になる業務があるので、承認がスキップされるようにしてほしい
• システム管理者は他者の承認を解除できるようにしてほしい

上の「特定の種別の時に一部の承認をスキップする」という追加仕様は、データ構造による影響範囲の違いはそれほどありませんでしたが、下の「システム管理者が他人の承認を解除できる」という追加仕様は元々のデータ構造がとても重要になります。

しかし、本人以外の人が承認を解除できるようにするという仕様は、元々誰が承認していて、その後誰が承認を解除したのか、という履歴がデータとして残っていないと後からトラブルが起きそうな予感があります。
元々レコードで管理していたのであれば、データ構造を大きく変更することなく対応できますが、カラムで管理している場合、承認履歴が残らないため、他人が承認を解除できてしまうのはかなりリスクのある仕様変更になってしまいます。

なぜ設計を間違えたのか

正しいという思い込み

テーブル設計に関しては私一人で行ったわけではなく、チーム全体で行ったものにはなりますが、私もレビューして指摘できる立場ではあったので、そこで気づいて指摘できなかったのは私の落ち度だなと思います。
作業をしていた段階で、「承認のテーブルはこれで良いのだろうか？」と若干の違和感は感じたものの、「設計者の意図があって、これでうまくできる保証があるのだろう」という思い込みが働き、私自身あまり深く考えずにスルーしたのが良くなかったなと思います。

画面に寄せすぎた

今回のシステムでは、承認フローの状況を横並びに表示する箇所が多々あります。
画面を起点に考えた結果、画面にそのまま対応できるような分かりやすさを求めてカラム管理するテーブル構造を選んだ可能性はありそうだと思う。

SQLでデータを直接確認する際にもぱっと見だと横並びの方が画面に対応していて分かりやすい、という理由もありそうです。

仕様変更がない前提だった

今の案件は開発の進め方がウォーターフォール的なので、チームメンバー全体で仕様変更が起きないことを前提に設計していた部分はあるのかもしれない。
じゃあアジャイルなら設計を間違えなかったのかと言われると、多分そんなことはないのだけれど、少なくとも、どんな仕様も後から変更の可能性があるとチーム全体が考えていれば、違う設計になっていた可能性はあるでしょう。

改善策

設計を切り離して考える

ソフトウェアを開発する際、UI、コード、DB、インフラなど、様々な要素を設計します。この時、プログラミング経験がある程度長くなると、「DB（テーブル）がこういう構造だと実装がしやすい」など、複数の要素を混合してどうすればよいかを考えるようになります。

全体を見て設計できることは必ずしも悪いことではないですが、実装のしやすさだけにフォーカスを当ててしまうと、UIやDBの設計で知らぬ間にアンチパターンとなってしまう可能性があります。

UIはユーザビリティやアクセシビリティの観点で設計をするのが良いでしょう。
コードは、可読性や変更容易性など、保守しやすさの観点で設計するのが良いでしょう。
DB（主にテーブル）は、データの不整合が起きないようにしつつ、事実を正しく記録できるように設計するのが良いでしょう。

設計するときは、ソフトウェア全体を構成する各要素を切り離したうえで、それぞれのあるべき形で設計するのが良いのだろうと思います。

仕様変更は起きる前提で設計する

今関わっている案件は進め方がウォーターフォール的であることもあって、後になって大きな仕様変更がない前提で進めていた節はある気がします。

しかし、ソフトウェア開発において仕様変更が起きないことはあり得ないので、コードもDBも、変更は起きることを前提に設計しておくのが良いかともいます。

冷静に考えれば、承認フローの中で承認者が増えたり減ったりすることは、一般的な業務の流れでも普通に起こり得る事業ではある気がします。
そういった変更がある度にカラムを追加したりカラム定義を変えたりするのは、テーブル設計としてうまくできているとは言えないでしょう。

仕様の変更や追加は必ず起きという前提のもと設計をするのが大事だと思います。

SQLによる整形

レコード単位で管理するデータ構造にした場合、承認がどこまで進んでいるのか、SQLでデータを取得したときに人間がぱっと見で把握したい、という場合、役職ごとにカラムが分かれていた方が見やすいのは事実です。
ただ、レコードでデータを管理していたとしても、SQLでcase式などを使って工夫すれば横変換ができます。

承認データをレコード管理していた場合、例えば以下のようなSQLで縦から横の変換ができます。

select
    業務ID
    , max(case when 役職 = '課長' then 承認者ID else null end) 課長ID
    , max(case when 役職 = '課長' then 承認日時 else null end) 課長承認日時
    , max(case when 役職 = '部長' then 承認者ID else null end) 部長ID
    , max(case when 役職 = '部長' then 承認日時 else null end) 部長承認日時 
from
    承認テーブル 
where
    承認解除日時 is null 
group by
    業務ID;

結果

承認ID	業務ID	課長ID	課長承認日時	部長ID	部長承認日時
1	1	10	2025-01-10 17:00:00	15	2025-01-10 18:00:00

画面のUI上横並びに表示したいが、データがレコード単位になっていて横並びにするには複雑なループ処理が必要になってしまう、みたいなことはWeb系の開発で度々起きます。
その場合、テーブル構造がカラム管理になっていれば何も考えずにそのまま表示できるので、確かに開発が楽になる面はありますが、カラム追加が発生した際に影響範囲が大きいことには変わりありません。

仮にレコードを分けるテーブル設計にしてプログラムのロジックが複雑になるのであれば、SQLで変換してプログラムの複雑化を吸収することもできます。
そのため、UIとデータ構造でプログラムが複雑になりそうであっても、テーブル設計としてはデータモデルとしてのあるべき形、「事実を正しく記録する」ことができるように設計するのが良いかと思います。

設計全般について

最近、ソフトウェアにおける設計とは何か？をよく考えるようになりました。
色々な解釈・定義があるかと思いますが、私の中では「より少ないコストで、少ない技術的負債で、より大きな価値を提供するための取り組み」という感じです。

コードにおいても、DBにおいても、インフラにおいても、このことを念頭において設計することが大事なんだろうな、と思います。

DB設計を復習しようと思い、「楽々ERDレッスン」を読み返しています。