DDLだけでは不十分？LLMで「実態」に即したER図を再構築するための検証プロセス

Posted at 2026-05-12

はじめに

ドキュメントが存在しない（あるいは陳腐化している）レガシーシステムのDB設計を復元したいとき、LLMは強力な味方です。DDLを貼り付けて「ER図を作って」と頼めば、数分で整ったドキュメントが出てきます。

ところが、ある業務システムのWMSデータベースを解析していたとき、こんな問いが飛んできました。

「ER図では Customer テーブルに BRANCH_ID が存在しているのですが、システム画面上でその紐づけをする機能が見当たらないんです。あと、実際にデータを吐き出すと BRANCH_ID も LOCATION_ID も全部NULLになっています…」

LLMが生成したER図はDDL上の列定義を忠実に反映していましたが、実際のデータ構造とは別物でした。

この記事では、その誤判定がなぜ起きるのか、そして実データのNULL統計をフィードバックとして使う検証プロセスで正しいER図を引き出す方法を解説します。

1. DDLをそのままLLMに渡すと何が起きるか

LLMがDDLからER図を生成するとき、FK制約（FOREIGN KEY句）が明示されていなくても、カラム名のパターンで関連を推定します。

推定ロジックは概ねこういうものです。

カラム名が XXX_ID という形式 → XXX テーブルへの外部キー候補
同名のテーブルが存在する → FK関係として結線
NOT NULL制約があれば必須の関連、NULLableなら任意の関連

これは多くの場合うまく機能します。しかし**レガシーシステムでよくある「設計時に追加されたが結局使われなかったカラム」**に対しては、このヒューリスティックが誤判定を起こします。

LLMには「カラムが存在する＝使われている」という暗黙の前提があるからです。

2. 実際に起きた誤判定（Before）

今回解析したDDLは（一般化すると）こんな構造でした。

CREATE TABLE customer (
    CUSTOMER_ID   VARCHAR(20) NOT NULL,
    CUSTOMER_NAME VARCHAR(100),
    BRANCH_ID     VARCHAR(10),      -- NULL可、FK制約なし
    LOCATION_ID   VARCHAR(10),      -- NULL可、FK制約なし
    WMS_CODE      VARCHAR(20),
    PRIMARY KEY (CUSTOMER_ID)
);

CREATE TABLE branch (
    BRANCH_ID   VARCHAR(10) NOT NULL,
    BRANCH_NAME VARCHAR(50),
    PRIMARY KEY (BRANCH_ID)
);

CREATE TABLE location (
    LOCATION_ID   VARCHAR(10) NOT NULL,
    LOCATION_NAME VARCHAR(50),
    BRANCH_ID     VARCHAR(10) NOT NULL,  -- branchへのFK（制約あり）
    PRIMARY KEY (LOCATION_ID),
    CONSTRAINT fk_location_branch FOREIGN KEY (BRANCH_ID) REFERENCES branch(BRANCH_ID)
);

CREATE TABLE customer_assignment (
    CUSTOMER_ID  VARCHAR(20) NOT NULL,
    BRANCH_ID    VARCHAR(10) NOT NULL,
    LOCATION_ID  VARCHAR(10),           -- NULL可
    PRIMARY KEY (CUSTOMER_ID, BRANCH_ID)
);

このDDLをそのままLLMに渡すと、次のようなER図が生成されます。

[Branch] 1────N [Customer] N────1 [Location]
                    │
                    └── N ── [CustomerAssignment]（存在するが役割が不明確）

LLMの判断はこうです。

customer.BRANCH_ID が存在する → Branch:Customer = 1:N の関係
customer.LOCATION_ID が存在する → Location:Customer = 1:N の関係
customer_assignment も BRANCH_ID / LOCATION_ID を持っている → 重複？冗長？

結果として、customer_assignment が何者なのか理解できないまま、直接参照と中間テーブル参照が両立した矛盾したER図が出来上がります。

3. 問題の発見：「これ全部NULLなんです」

エンジニアからの指摘で気づいたのが「実データではこのカラムがすべてNULL」という事実でした。

さっそく確認するクエリを実行します。

SELECT
    COUNT(*)               AS total_rows,
    COUNT(BRANCH_ID)       AS branch_not_null,
    COUNT(LOCATION_ID)     AS location_not_null,
    ROUND(
        COUNT(BRANCH_ID) * 100.0 / COUNT(*), 1
    )                      AS branch_fill_rate
FROM customer;

結果：

total_rows	branch_not_null	location_not_null	branch_fill_rate
1,248	0	0	0.0%

全1,248件がNULL。 稼働しているシステムで、1件も値が入っていない。

この時点で「DDLに書いてあっても使われていないカラムである」と確信できます。

一方、customer_assignment を確認すると：

SELECT
    COUNT(*)             AS total_rows,
    COUNT(CUSTOMER_ID)   AS customer_not_null,
    COUNT(BRANCH_ID)     AS branch_not_null,
    COUNT(LOCATION_ID)   AS location_not_null
FROM customer_assignment;

total_rows	customer_not_null	branch_not_null	location_not_null
3,891	3,891	3,891	2,104

こちらは全行に CUSTOMER_ID と BRANCH_ID が入っています。
customer_assignment こそが customer と branch の紐付けを管理している本体だと分かります。

4. 実データ検証プロセス

この節が本記事の核心です。検証は3ステップで進めます。

Step 1：NULL率チェックSQL を標準化する

怪しいカラム全体を一括チェックできる汎用クエリを用意します。

-- 対象テーブルのID系カラムをすべてNULL率チェック
SELECT
    column_name,
    COUNT(*)                                    AS total,
    COUNT(column_value)                         AS not_null_count,
    ROUND(COUNT(column_value) * 100.0 / COUNT(*), 1) AS fill_rate_pct
FROM (
    -- 実際は対象カラムを列挙して UNION ALL する
    SELECT 'BRANCH_ID'   AS column_name, BRANCH_ID   AS column_value FROM customer
    UNION ALL
    SELECT 'LOCATION_ID' AS column_name, LOCATION_ID AS column_value FROM customer
) t
GROUP BY column_name
ORDER BY fill_rate_pct;

埋まり率が 0% のカラムは「形骸化カラム候補」、100% のカラムは「必須の紐付けキー候補」として扱います。

中途半端な値（10〜90%）のカラムは要注意です。段階的に機能移行された過渡期のスキーマである可能性があります。

Step 2：フィードバックプロンプトでLLMに再分析させる

NULL統計が手に入ったら、それをLLMへのフィードバックとして与えます。

以下のNULL統計を踏まえて、ER図を再生成してください。

【NULL統計】
- customer.BRANCH_ID   : 全1,248件がNULL（埋まり率 0%）
- customer.LOCATION_ID : 全1,248件がNULL（埋まり率 0%）

【依頼】
1. 上記のカラムを「形骸化カラム（未使用）」として扱い、ER図上では破線または注記で表現してください
2. customer と branch / location の実際の紐付けは、別テーブルを経由している可能性があります。
   BRANCH_ID または CUSTOMER_ID を両方持つテーブルを中間テーブル候補として特定してください
3. 中間テーブルを介した N:N 関係として Customer ⇔ Branch の関連を再描画してください
4. 各テーブルの NULL可否（必須/任意）も明記してください

このプロンプトのポイントは2つです。

「形骸化カラムとして扱う」という明示的な指示
曖昧に「NULLです」と伝えるだけでは、LLMが「任意の関連」として解釈することがあります。「未使用」「形骸化」という言葉で意図を明確にします。

「中間テーブル候補を自分で特定させる」という委任
どのテーブルが中間テーブルかをこちらが指定するのではなく、LLMに特定させます。こうすることで、見落としていた関連テーブルが浮かび上がることがあります。

Step 3：生成結果を実データで照合する

LLMが「customer_assignment が中間テーブル」と判断したら、設計意図と実データが整合しているか確認します。

-- 中間テーブルの多重度確認
-- 1人の顧客が複数の拠点に紐づいているか？
SELECT
    CUSTOMER_ID,
    COUNT(DISTINCT BRANCH_ID) AS branch_count
FROM customer_assignment
GROUP BY CUSTOMER_ID
ORDER BY branch_count DESC
LIMIT 10;

CUSTOMER_ID	branch_count
C001234	5
C002891	3
C000456	2
…	…

1人の顧客が複数の branch に紐づいている実データが確認できれば、N:N関係であることが裏付けられます。

5. After：正しい構造の発見

フィードバックを経て、LLMが生成し直したER図は次のような構造になりました。

[Branch] 1────N [Location]
    │
    N
    │
[CustomerAssignment] ←── 中間テーブル（M:N の本体）
    │
    N
    │
[Customer]

関係	Before（誤）	After（正）
Customer ↔ Branch	N:1（直接）	N:N（中間テーブル経由）
Customer ↔ Location	N:1（直接）	N:N（中間テーブル経由、任意）
customer.BRANCH_ID	アクティブなFK	形骸化カラム（全NULL）
customer_assignment	役割不明	紐付けの本体

さらに副産物として、設計意図も読み解けました。

customer_assignment.LOCATION_ID が NULLableになっているのは、「拠点（倉庫）レベルの制限は任意で、部支店単位の制限だけ設定することもある」という業務ルールを反映しているためです。DDLだけではこのニュアンスは分かりませんでしたが、NULL率を見ることで「BRANCH_ID は必須・LOCATION_ID は任意」という設計意図が浮かび上がりました。

6. プロンプトテンプレート

本手法を再利用できるよう、2段階のプロンプトをまとめます。

プロンプト①：DDLからの初回生成

以下のDDLからER図を生成してください。

[DDL]
{DDLをここに貼り付ける}

【生成ルール】
- FK制約が明示されていない場合は、カラム名パターン（XXX_ID）から推定して関連を描いてください
- ただし「推定による関連」と「FK制約による関連」は視覚的に区別してください（例：実線/破線）
- NULL可否（NOT NULL / NULL可）を各テーブルの属性に明記してください
- 「このDDLで不明確な点・検証が必要な点」をER図の下に箇条書きで列挙してください

最後の「不明確な点の列挙」が重要です。LLMに自己申告させることで、確認すべきカラムの優先順位が分かります。

プロンプト②：実データフィードバックによる再生成

先ほど生成したER図について、実データのNULL統計が判明しました。
以下の情報を踏まえて再生成してください。

【NULL統計】
{テーブル名}.{カラム名} : {総件数}件中{NULL件数}件がNULL（埋まり率 {rate}%）
（複数カラムを列挙する）

【依頼内容】
1. 埋まり率0%のカラムを「形骸化カラム（未使用）」として処理してください
   - ER図上では破線または「※未使用」注記で表現
   - このカラムによる関連は描かないでください

2. 形骸化カラムがあるテーブルについて、「実際の紐付けを担っている代替テーブル」を
   DDL全体から特定してください（CUSTOMER_ID と BRANCH_ID を両方持つテーブルなど）

3. 特定した中間テーブルを使ったN:N関係として関連を再描画してください

4. 変更箇所をBefore/Afterで説明してください

7. 注意点：NULL=未使用とは限らない

本記事の手法は強力ですが、「全NULL＝使われていない」という判断には慎重さも必要です。

ケース1：段階移行中のスキーマ
古いカラムから新しいカラムへの移行が進行中の場合、両方のカラムが一時的に共存します。NULLが増えているだけで、まだ完全移行が終わっていないケースです。

ケース2：オプション機能の未設定
機能は存在するが、そのオプションを使っていない顧客ばかり、というケース。埋まり率10〜30%程度であれば「使っているが少ない」可能性があります。

判断の目安:

埋まり率	解釈
0%	形骸化カラムの可能性が高い
1〜30%	オプション機能か移行途中の可能性
31〜99%	アクティブなカラム（NULL可の任意項目）
100%	必須カラム（NOT NULL運用）

まとめ

LLMは「DDLに書かれている＝現在も使われている」という前提で動きます。これはレガシーシステムでは成立しないことが多く、形骸化カラムが混在したスキーマでは誤った関連が描かれます。

本記事で紹介した手法をまとめると：

DDLだけでまずER図を作る（初回生成）
LLMに「不明確な点」を自己申告させる（確認箇所を絞る）
NULL率チェックSQLで実データを検証する（形骸化カラムを特定）
NULL統計をフィードバックプロンプトで渡す（LLMに再分析させる）
多重度確認SQLで再生成結果を検証する（裏付けを取る）

DDLは「設計時点の意図」、実データは「現在の真実」です。LLMにとってはどちらも等価に見えますが、この2つの乖離を検出して橋渡しできるのは、業務文脈を知っている人間だけです。

LLMはその橋渡しを正確に反映した図を高速に描く存在として使う——そのバランスが、ドキュメント復元の精度を大きく左右します。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up