CRUD図は、縦に機能、横にエンティティ(テーブル)を並べ、交点に「C(作成)」「R(参照)」「U(更新)」「D(削除)」を書き込む表です。CRUDマトリクスとも呼ばれ、機能の作り忘れを見つけたり、テーブルを変えるときに影響を受ける機能を洗い出したりするのに使います。この記事では、IPAのガイドが示す定義と書き方のコツに沿ってER図と機能一覧から手で書く手順を示したうえで、既存システムのSQLからCRUD図を自動で作るPythonスクリプト、PostgreSQLの統計で本番の実態と突き合わせる方法、影響調査での読み方までを順に解説します。
まとめ:CRUD図は機能一覧とER図から作り、列の点検と影響調査で元を取る表
CRUD図の材料は2つだけです。機能一覧(画面・バッチ・API)を行に、ER図のエンティティを列に置き、各機能のSQLや処理仕様を見ながらC・R・U・Dを埋めます。書き終えたら、列を縦に読んで「Cを持つ機能が1つも無いエンティティ」「どこからも参照されないエンティティ」を探してください。ここで見つかる穴が、CRUD図を作る最大の見返りです。
改修を繰り返す既存システムでは、手で書くより既存のSQLから抽出したほうが正確で速く済みます。抽出結果と本番の統計を突き合わせれば、設計書に無い更新や使われていないテーブルも確認できる形です。逆に、画面とテーブルが1対1で対応する小規模なアプリでは、CRUD図は作らずにER図とソースコードで足ります。
CRUD図の構造:機能とエンティティの行列にC・R・U・Dを書く表の読み方
CRUD図は記法の決まった図ではなく、行と列の意味が決まった表です。何を行と列に置き、セルに何を書くかを先にそろえておくと、担当者ごとの書き方のぶれが消えます。
IPAの機能要件の合意形成ガイドが定めるCRUD図の定義と6つの構成要素
IPAの機能要件の合意形成ガイド 分冊4 データモデル編(2010年3月31日)は、CRUD図を「エンティティのインスタンスの作成、参照、更新、削除をエンティティと機能のマトリックスで表現したもの」と定義しています。同ガイドの構成要素は、書誌情報としてCRUD図のID・名称・概要(範囲と目的)、本体としてエンティティ・機能・CRUDの計6項目です。機能の欄には「システムの機能(または業務、組織、画面)など」を置いてよいとされ、行の単位は案件ごとに選べます。
同ガイドは、CRUD図そのものを発注者がレビューすることは少なく、それを使った検証結果がレビューの対象になると注記しています。つまりCRUD図は開発側が設計の穴を探すための道具で、発注者向けの説明資料ではありません。発注者との合意に使う図の全体像は要件定義と設計で作る図の一覧で整理しています。
SQL文の種類とC・R・U・Dの対応表とUPSERT・論理削除の書き分け
セルに書く文字は、実装ではSQL文の種類にほぼ対応します。迷いやすいのは、1文で2つの操作をするUPSERTと、行を消さない論理削除です。
| SQL・処理 | CRUD図に書く文字 | 注意点 |
|---|---|---|
| INSERT | C | INSERT … SELECTは取得元にもR |
| SELECT・JOIN・サブクエリ | R | UPDATE … FROM で読む表もR |
| UPDATE | U | WHERE句で読むだけの別表はR |
| DELETE | D | DELETE FROM の表は参照扱いにしない |
| MERGE・UPSERT | CU | 新規と既存の両方を書き込む |
| 論理削除(削除フラグの更新) | U(凡例でD扱いと明記) | 表記をプロジェクトで1つに決める |
表中のUPSERTは、PostgreSQLの INSERT … ON CONFLICT DO UPDATE のように、行が無ければ作り、あれば更新する文を指します。論理削除をUと書くかDと書くかに正解はありません。ただ、混在させると「Dの無い列」を探す点検が機能しなくなるため、凡例に1行書いて統一してください。削除フラグ自体の設計上の落とし穴はDB設計のアンチパターン一覧で扱っています。
ER図と機能一覧からCRUD図を書く手順:軸の準備から記入と並べ替えまで
新規開発の基本設計で手書きする場合の手順です。ER図と機能一覧の両方がそろってから着手します。片方しか無い段階で書くと、軸の差し替えで二度手間になります。
縦軸に機能一覧、横軸にER図のエンティティを置く準備の4ステップ
Excelやスプレッドシートで十分に作れます。準備は次の順で進めます。
- ER図から論理エンティティを書き出す。コード値だけを持つ小さなマスタは、別のCRUD図に分けてもよい
- 機能一覧から画面・バッチ・外部連携APIを書き出す。1行=利用者から見た1操作(「受注登録」「受注取消」)にそろえる
- CRUD図のID・名称・対象範囲(例:受注サブシステム)を表の上に書く
- 凡例を書く。論理削除・UPSERT・Retrieve表記など、前の対応表で決めた書き方を1行ずつ残す
エンティティの抜き出し方やリレーションの読み方はER図とは?記号・線の読み方と書き方の手順を先に確認しておくと、列の粒度で迷いません。基本設計の成果物のどこにCRUD図を置くかは基本設計の成果物一覧が参考になります。
IPAのコツ04D301〜04D304に沿った行と列の並べ方と記入欄の固定
前述のIPAガイドは、CRUD図を分かりやすくする書き方のコツを4つ挙げています。どれも表の並べ方の工夫で、記入の手間は増えません。
- 04D301:Create・Read・Update・Deleteの記載欄を明確に分けて固定する(実装例:1セルを4つの小欄に割り、C・R・U・Dの位置を常に同じにする)
- 04D302:エンティティをイベント系(受注・出荷など)とリソース系(顧客・商品など)に分けて並べる
- 04D303:ビジネスプロセスを実際の業務の時系列順に上から並べる
- 04D304:イベント系エンティティを、作成される順番に沿って左から並べる
04D303と04D304を両方守ると、Cが表の左上から右下へ階段状に並びます。階段から外れたCは、業務の順序と処理の順序がずれている箇所です。レビューで最初に目を向けるのはそこになります。
受注業務5機能×5エンティティで書くCRUD図の記入例とセルの粒度
受注業務を例にした記入例です。後半で紹介する抽出スクリプトに、同じ業務のSQLを読ませた結果と一致させています。
| 機能\エンティティ | 顧客 | 商品 | 受注 | 受注明細 | 在庫 |
|---|---|---|---|---|---|
| 顧客登録 | C | ||||
| 商品取込(バッチ) | CU | ||||
| 受注登録 | R | R | C | C | U |
| 受注検索 | R | R | |||
| 受注取消 | U | RD | U |
セルの粒度は「その機能が1回の操作で行う処理」です。受注登録の中で在庫の引当数を加算するなら、在庫の列にUを書きます。項目単位まで書き分けたくなったら、そのエンティティだけ別シートに項目×機能の表を作り、本体のCRUD図は文字1〜4個に保つと読みやすさが崩れません。
列と行を点検してCRUD図から機能の作り忘れと責務の偏りを見つける
CRUD図は書いた時点では何も教えてくれません。列を縦に、行を横に読んで初めて設計の穴が出てきます。
Cの無い列・Dの無い列・Rの無い列が示す作り忘れと削除方針の未決
点検の優先順位は次のとおりです。上の記入例にもそのまま当てはめられます。
- Cが1つも無い列:そのデータを作る機能がありません。記入例の在庫列はUだけで、在庫の初期登録や入荷の機能が抜けています。データ移行で作る想定なら、その旨を凡例に書きます
- Rが1つも無い列:作っても誰も見ないデータです。帳票や外部連携で読むなら、その機能を行に足します。足せないなら、エンティティ自体が不要か確かめます
- Dが1つも無い列:削除しない方針なのか、決めていないだけなのかを区別します。個人情報を含む顧客の列でDが無いなら、保存期間と消去の手段を決める宿題として残します
行のほうは、Cだけが並んでRが無い機能に注意します。入力値の重複や存在を確かめずに登録している可能性があります。
多くの機能が同じ列にUを持つ構成が示す更新責務の分散と排他制御の漏れ
1つの列にUが集中しているエンティティは、更新の規則が複数の機能に散らばっています。在庫の引当数を受注登録・受注取消・出荷・棚卸の4機能が別々に更新している状態がその典型です。同じ行を同時に更新する経路が増えるほど、楽観ロックや行ロックの付け忘れが混じりやすくなります。
この列を見つけたら、更新を1つのサービスやストアドプロシージャにまとめられないかを検討します。まとめられないなら、少なくともUを持つ機能の一覧を排他制御の試験項目に転記しておきます。
既存システムのSQLからCRUD図を自動抽出するPythonスクリプトの作り方
運用中のシステムでは、設計書のCRUD図が実装とずれていることがほとんどです。改修前の影響調査に使うなら、実際に動いているSQLから作り直したほうが早く、外れも少なく済みます。
1ファイル1機能のSQLからテーブル別のCRUDを拾うスクリプト全文
機能ごとにSQLを1ファイルへまとめたフォルダを読み、テーブル別のC・R・U・DをCSVで出すスクリプトです。Python 3の標準ライブラリだけで動きます。MyBatisのマッパーXMLなど、SQLがファイルに分かれている構成ならそのまま、そうでなければ機能ごとにSQLを書き出してから使います。
import re
import sys
from pathlib import Path
# 1ファイル=1機能として、SQLからテーブルごとのCRUDを拾う
PATTERNS = [
("C", r"\binsert\s+into\s+([\w.]+)"),
("C", r"\bmerge\s+into\s+([\w.]+)"),
("U", r"\bmerge\s+into\s+([\w.]+)"),
("U", r"\bupdate\s+([\w.]+)(?:\s+(?:as\s+)?\w+)?\s+set\b"),
("D", r"\bdelete\s+from\s+([\w.]+)"),
]
READ = r"\b(?:from|join|using)\s+([\w.]+)"
ORDER = "CRUD"
def strip_sql(text):
text = re.sub(r"--[^\n]*", " ", text) # 行コメント
text = re.sub(r"/\*.*?\*/", " ", text, flags=re.S) # ブロックコメント
text = re.sub(r"'(?:[^']|'')*'", "''", text) # 文字列リテラル
return text.lower()
def scan(sql):
ops = {}
cte = set(re.findall(r"\b(\w+)\s+as\s*\(", sql)) # WITH句の名前はテーブルではない
for op, pat in PATTERNS:
for table in re.findall(pat, sql):
if table in cte:
continue
ops.setdefault(table, set()).add(op)
# FROM/JOIN/USING は参照(R)。ただし DELETE FROM t の FROM は除いて数える
reads = re.findall(READ, sql)
for table in re.findall(r"\bdelete\s+from\s+([\w.]+)", sql):
reads.remove(table)
for table in reads:
if table not in cte:
ops.setdefault(table, set()).add("R")
# ON CONFLICT ... DO UPDATE は更新(U)も持つ
for table in re.findall(r"\binsert\s+into\s+([\w.]+)[^;]*?on\s+conflict[^;]*?do\s+update", sql, flags=re.S):
ops[table].add("U")
return ops
def main(root):
matrix = {}
for path in sorted(Path(root).glob("*.sql")):
matrix[path.stem] = scan(strip_sql(path.read_text(encoding="utf-8")))
tables = sorted({t for ops in matrix.values() for t in ops})
print("function," + ",".join(tables))
for func, ops in matrix.items():
cells = ["".join(c for c in ORDER if c in ops.get(t, ())) for t in tables]
print(func + "," + ",".join(cells))
# 列ごとの点検: C が無い表・誰も R しない表を出す
for t in tables:
col = "".join("".join(ops.get(t, ())) for ops in matrix.values())
missing = [c for c in "CR" if c not in col]
if missing:
print(f"# check {t}: no function does {'/'.join(missing)}", file=sys.stderr)
if __name__ == "__main__":
main(sys.argv[1] if len(sys.argv) > 1 else "sql")
WITH句で付けた名前(CTE)はテーブルではないので除外し、UPDATE stock s SET のような別名付きの更新も拾います。DELETE FROM の FROM は参照と数えず、同じSQL内の UPDATE … FROM や JOIN で読んでいる場合だけRを残します。
サンプルSQL5本の実行結果と在庫テーブルにCが無いという検出例
受注業務の5機能(顧客登録・受注登録・受注取消・受注検索・商品取込)のSQLを sql フォルダに置き、実行した結果です。商品取込は INSERT … ON CONFLICT (product_id) DO UPDATE のUPSERTで、受注検索はWITH句を使っています。
$ python crud_matrix.py sql
# check stock: no function does C/R
function,customers,order_items,orders,products,stock
01_customer_register,C,,,,
02_order_register,R,C,C,R,U
03_order_cancel,,RD,U,,U
04_order_search,R,,R,,
05_product_import,,,,CU,
標準エラーに出た1行が点検の結果です。在庫(stock)には作成する機能も参照する機能もありません。前の章で手作業の点検が見つけた「在庫の初期登録が無い」と同じ指摘を、機械的に出せています。標準出力のCSVはExcelで開けばそのままCRUD図になり、行と列を入れ替えれば前述の04D304の並びにも直せます。
正規表現の抽出が拾えない動的SQL・ORM・ストアドプロシージャの補い方
このスクリプトは、ファイルに書かれたSQLの文字列だけを見ています。次の3つは取りこぼすので、別の手段で補います。
- 実行時に文字列を連結して作る動的SQL:テーブル名が変数のときは拾えません。後述の pg_stat_statements で、実際に実行されたSQL文を取り出して同じスクリプトに流します
- ORMが生成するSQL:Hibernate・Django ORM・Prisma などはSQLをコードに書きません。ORMのSQLログを有効にして結合試験を1周し、機能ごとにログを分けて保存してから読ませます
- ビュー・ストアドプロシージャ・トリガー:ビューを読む機能は、ビューの元テーブルにもRを持ちます。PostgreSQLなら information_schema.view_table_usage でビューと元テーブルの対応を引けます。ただし現在のロールが所有するテーブルしか載らないため、所有者のロールで実行してください
正規表現で済ませる理由は、依存ライブラリなしでどの開発端末でも動かせるからです。方言の多いSQLを厳密に扱いたい段階になったら、SQLパーサーへの置き換えを検討します。
PostgreSQLの統計ビューで本番のCRUDと設計上のCRUD図を突き合わせる
ソースから作ったCRUD図は「書かれている処理」の一覧で、本番で実際に動いた処理とは限りません。PostgreSQLは表ごとの読み書きの回数を統計として持っているので、それと比べると死んだ機能や設計書に無い更新が見えます。
pg_stat_user_tablesの挿入・更新・削除件数で使われない表を探すSQL
PostgreSQL 18の累積統計システムでは、pg_stat_user_tables が表ごとに逐次走査の回数(seq_scan)、インデックス走査の回数(idx_scan)、挿入・更新・削除した行数(n_tup_ins・n_tup_upd・n_tup_del)を返します。最後に走査した時刻の last_seq_scan・last_idx_scan はPostgreSQL 16で追加された列です。
SELECT relname,
seq_scan + COALESCE(idx_scan, 0) AS r_scans,
n_tup_ins AS c_rows,
n_tup_upd AS u_rows,
n_tup_del AS d_rows,
GREATEST(last_seq_scan, last_idx_scan) AS last_read
FROM pg_stat_user_tables
ORDER BY r_scans, relname;
r_scans が0の表は、統計を取り始めてから一度も読まれていません。CRUD図でRを持つ機能があるのに0なら、その機能は使われていないか、別経路に置き換わっています。逆にCRUD図ではDが無いのに d_rows が増えている表は、設計書に載っていない削除処理(夜間バッチや手作業のSQL)が動いている証拠です。
統計は最後にリセットした時点からの累計で、pg_stat_database の stats_reset 列でその時刻を確認できます。月末だけ動く締め処理を見落とさないよう、最低1か月分は貯めてから判断してください。読み取りをレプリカに振っている構成では、プライマリの統計に参照が現れない点にも注意が要ります。
pg_stat_statementsのSQL文を抽出スクリプトに流して実CRUDを得る
表単位の統計では「どの機能が」読み書きしたかまでは分かりません。そこを補うのが拡張機能の pg_stat_statements です。postgresql.conf の shared_preload_libraries に追加してサーバーを再起動し、対象データベースで CREATE EXTENSION pg_stat_statements を実行すると、実行されたSQL文ごとの回数(calls)と代表的な文(query)が貯まります。
リテラルの定数は $1 のような記号に置き換えて集約されるため、同じ形のSQLは1行にまとまる仕組みです。この query 列を取り出してファイルに書き、前述のスクリプトに読ませれば、動的SQLやORMが実際に発行した文も含めたCRUDが得られます。機能名との対応は、接続ごとの application_name を機能単位で設定しておくと付けやすくなります。
影響調査でCRUD図を使う手順:カラム変更から改修範囲と試験範囲を決める
CRUD図が最も役に立つのは、作った後の改修です。テーブル定義を変えるときに、どの機能を直し、どの機能を試験し直すかを表から読み取れます。
変更するエンティティの列を縦に読みC・U・Dを持つ機能から改修する
例として、受注に「納品希望日」の列を必須で追加する改修を考えます。受注の列を縦に読むと、Cを持つのは受注登録、Uを持つのは受注取消です。必須項目の追加では、Cを持つ機能は値を渡さないとINSERTが失敗するため、必ず改修対象になります。Uを持つ機能は、更新する項目に含めるかを仕様で決めます。
型や桁の変更なら、C・Uを持つ機能の入力チェックと、Rを持つ機能の表示や帳票の桁あふれが対象です。列の削除なら、Rを持つ機能のSELECT句まで含めて全数が改修対象になります。変更の種類によって、列のどの文字を拾うかが変わる点を押さえておけば、調査の抜けは出にくくなります。
Rだけを持つ機能を回帰試験に入れる条件と影響調査表への転記項目
Rしか持たない機能は、コードを変えなくても結果が変わることがあります。受注検索が受注を読んでいるなら、追加した列がNULLのままの旧データをどう表示するかで画面の結果が変わります。項目を増やすだけの改修でも、SELECT * で全列を取っている機能や、CSV出力で列の位置に依存している連携は回帰試験に入れてください。
影響調査表には、CRUD図から次の項目を転記します。変更するエンティティ、影響を受ける機能、その機能が持つC・R・U・D、改修の要否、試験の要否の5つです。CRUD図をマーメイド記法などのテキストで管理していれば差分をレビューに載せやすくなります(マーメイド記法の書き方)。
CRUD図を作るべきプロジェクトと作らずに済ませる場面の判断基準
CRUD図は作るだけでも工数がかかり、更新を止めた瞬間から実装とずれ始めます。作るかどうかは、保守まで続けられるかで決めます。
改修案件・複数チーム・バッチ連携ありならCRUD図を作って保守する
次のどれかに当てはまるなら作ります。既存システムの改修や移行で影響範囲を説明する必要がある場合、複数のチームが同じテーブルを更新する場合、画面のほかに夜間バッチや外部連携が同じデータを書き換える場合です。どれも、1人の開発者の頭の中だけでは更新経路を追い切れない状況です。
既存システムでは手で書かず、前述のスクリプトと統計で作ってから人が直す順にします。テーブル数が多く設計書も古い状態から影響調査とDB設計の見直しを並行して進めるなら、データベース設計・移行支援のように、現行DBの調査から引き受ける外部の手を借りるのも選択肢です。
画面と表が1対1の小規模CRUDアプリならCRUD図は作らずに済ませる
1つの画面が1つのテーブルを登録・一覧・編集・削除するだけのアプリでは、CRUD図はどの行も同じ形になり、点検で見つかるものがありません。ORMのモデル定義が正本になっていて、マイグレーションでスキーマを管理している場合も同様です。この場合はER図とソースコードの検索で影響調査が足ります。
作らないと決めたなら、その理由を設計書に1行残しておきます。後から改修を担当する人が「CRUD図が無いのは作り忘れか」と迷わずに済みます。
よくある質問
CRUD図の書き方や運用について、よく寄せられる質問に答えます。
CRUD図とCRUDマトリクスは同じものですか?
呼び方は異なりますが、指しているものは同じです。IPAのガイドも「CRUD図」という名称で、中身を「エンティティと機能のCRUDマトリックス」と説明しています。現場では「CRUD表」「CRUDマトリックス」とも呼ばれます。名称より、行に機能・列にエンティティを置くのか、その逆なのかをチーム内でそろえるほうが大切です。IPAの表記例とこの記事の記入例は、行に機能、列にエンティティを置いています。
CRUD図はExcelで作っても問題ありませんか?
問題ありません。多くの現場で作成に使われているのは、Excelやスプレッドシートです。ただしエンティティが数十を超えると列が横に伸びて読みにくくなり、フィルタでの検索もしにくくなるのが難点です。その規模になったら、記事中のスクリプトのようにCSVを正本にして、必要な列だけをExcelで開く運用に切り替えると扱いやすくなります。CSVならGitで差分も取れます。
CRUD図は要件定義と基本設計のどちらで作りますか?
本格的に作るのは基本設計(外部設計)で、ER図と機能一覧がそろってからです。要件定義の段階では、主要な業務とエンティティだけの粗いCRUD図で業務の漏れを確かめる程度にとどめます。発注者の承認対象に入れるかどうかは契約の成果物一覧で決めることで、IPAのガイドもCRUD図自体を発注者がレビューすることは少ないとしています。
論理削除はDとUのどちらで書けばよいですか?
実装に合わせるならU、業務上の意味に合わせるならDです。どちらでも構いませんが、プロジェクト内で1つに決めて凡例に書いてください。混在すると「Dの無い列」を探す点検が使えなくなります。SQLから自動抽出する場合は削除フラグの更新もUとして出てくるので、抽出結果を正本にするならUにそろえるのが手間の少ない選び方です。
CRUDのRはReadとRetrieveのどちらの意味ですか?
どちらも「参照」の意味で、CRUD図の書き方は変わりません。IPAのガイドはRead、@ITのデータモデリング連載などはRetrieveと書いています。社内の設計書で表記が混ざっていても、セルに書く文字はRで共通です。気になる場合は凡例に「R:参照(Read)」と1行書いておけば十分です。
関連記事
- ER図とは?記号・線の読み方と書き方の手順、IE記法・IPA試験の表記とSQLへの落とし方:CRUD図の列になるエンティティの抜き出し方
- 要件定義と設計で作る図の一覧|業務フロー図・画面遷移図とデザイナーの担当範囲:CRUD図を要件定義でどこまで作るかの判断
- 基本設計とは?どこまで決めるか・成果物一覧と要件定義・詳細設計との違い:CRUD図を置く設計工程の全体像
- DB設計のアンチパターン一覧|SQLアンチパターン第2版27章と、中間テーブル・削除フラグなど現場の定番失敗:CRUD図の点検で見つかる設計の歪みの直し方
- マーメイド記法の書き方|フローチャート・シーケンス図・ER図をテキスト管理する手順:設計図をテキストで差分管理する方法