第10章データを保存できる形にする

第9章では、支援ステータス機能について、利用者、目的、用語、状態、イベント、ルール、境界、受け入れ条件を整理した。第10章では、それをデータベースに保存できる形へ変換する。

データベース設計は、表を作る作業ではない。業務上の事実を、あとから矛盾なく扱える形で保存する作業である。何を保存するのか、どの関係を守るのか、どの値を許すのか、どの操作をSQLで確認できるのかを決める。

ここでの設計は、第11章のAPI設計へ直接つながる。保存の形が曖昧なら、request、response、入力検証、認可、エラーも曖昧になる。だからこの章では、支援ステータス機能を題材に、テーブル、カラム、制約、SQL確認、DB変更メモまでを一続きで扱う。

この章でできるようになること

この章を読み終えた時点で、次のことを自分の言葉で説明できる状態を目指す。

第9章の用語、状態、ルールを、テーブル候補、カラム、制約へ変換できる。
テーブルにするもの、カラムや値として持つもの、保存しないものを理由つきで分けられる。
主キー、外部キー、NOT NULL、UNIQUE、CHECKの役割を、支援ステータス機能の例で説明できる。
支援ステータスを現在値だけで持つ案と、履歴も持つ案の違いを説明できる。
DB制約で守ることと、APIやアプリケーションコードで確認することを分けられる。
SELECT、JOIN、INSERT、UPDATE、制約違反のSQLで、設計がユースケースを満たすか確認できる。
DB変更メモに、変更理由、既存データへの影響、確認SQL、第11章への引き継ぎを書ける。
AIが出したスキーマ案やSQLを、使うDBMS、既存スキーマ、実行結果に照らして検証できる。

データベースは、業務上の事実を守る場所である

Webアプリケーションのデータベースは、値を入れておく箱ではない。後から取り出し、更新し、関係をたどり、間違ったデータを拒否する場所である。

第9章で整理した支援ステータス機能には、保存すべき事実がある。メンターがいる。受講者がいる。メンターは受講者を担当する。受講者は学習ログを提出する。受講者には、メンターが見る支援ステータスがある。

これらをただ文字として保存するだけでは足りない。どの学習ログがどの受講者のものか。どのメンターがどの受講者を担当しているか。支援ステータスには、定義した値だけが入るか。存在しない受講者に支援ステータスを付けられないか。同じ担当関係が重複しないか。

データベース設計では、このような問いを保存の形へ変える。

たとえば、第9章のルールは次のようにDB設計へつながる。

第9章で整理したこと	DB設計で考えること	DBだけでは完結しないこと
メンターがいる	`mentors` テーブルを作り、メンターを一意に識別する	ログインユーザーとメンターの対応づけ
受講者がいる	`learners` テーブルを作り、受講者を一意に識別する	受講者の表示名や個人情報をどこまで持つか
メンターは担当受講者を支援する	担当関係を外部キーや中間テーブルで表す	ログイン中のメンターが担当者かをAPIで認可する
支援ステータスは決まった値から選ぶ	`CHECK` 制約や候補値で許可値を守る	値を変えてよいタイミングや業務判断
自動アラートは初回リリース対象外	アラート用テーブルやジョブ設定を作らない	将来追加する余地をメモに残す

RDBは、テーブル、行、カラム、関係で考える

RDBは Relational Database の略で、データをテーブルと関係として扱うデータベースである。テーブルは、同じ種類の事実を集める場所である。行は、テーブルに保存される1件のデータである。カラムは、一つの行に保存する属性である。

Excelのシートに似て見えるかもしれない。しかし、RDBでは関係と制約まで設計する。関係とは、あるテーブルの行が別のテーブルの行とつながることである。制約とは、入れてよいデータの条件をデータベース側で守る仕組みである。

主キーは、行を一意に識別する値である。たとえば learners.id は、受講者を一人ずつ見分けるために使える。

外部キーは、別のテーブルとの関係を守る値である。たとえば learning_logs.learner_id が learners.id を参照すれば、どの学習ログがどの受講者のものかをDB上でたどれる。

主キーは、業務上の番号や文字列をそのまま使う場合もあれば、DBで採番したIDを使う場合もある。どちらを選ぶ場合でも、後から意味が変わりにくく、同じ行を安定して指せることが大事である。

外部キーは、参照先が存在することをDBに確認させる。これを参照整合性という。たとえば、存在しない受講者IDに支援ステータスを付けると、画面には表示できても、後からJOINしたときに孤立したデータになる。外部キーは、そのような孤立を防ぐための仕組みである。

NULLは「値がない、または不明である」ことを表す。空文字 ''、数値の 0、支援ステータスの none とは意味が違う。たとえば、支援メモが任意なら note をNULL可にする案もある。一方で、画面やAPIで常に文字列として扱いたいなら、note TEXT NOT NULL DEFAULT '' として空文字を入れる案もある。どちらが正しいかではなく、意味と扱いをそろえることが重要である。

この章では、RDBの理論を網羅しない。アプリケーションDB設計として、保存、取得、更新、関係、制約、確認SQLを説明できることを目標にする。

ドメインモデルから、テーブル候補を作る

テーブル候補は、名詞を機械的に並べて作るものではない。第9章のドメインモデルを見ながら、後から取得、更新、参照する必要がある概念を選ぶ。

支援ステータス機能では、次の候補が考えられる。

learners：受講者。
mentors：メンター。
mentor_assignments：メンターと受講者の担当関係。
learning_logs：学習ログ。
learner_support_statuses：受講者ごとの現在の支援ステータス。
support_status_history：支援ステータスの変更履歴。必要なら検討する。

テーブルにしない用語も書く。たとえば「未提出」は、支援ステータスの値ではなく、学習ログの提出状態かもしれない。「要支援」は、支援ステータスの候補値として扱えるかもしれない。「困っている」は、メンターの判断を表す曖昧な表現で、直接DB値にするには定義が足りないかもしれない。

テーブル候補を考えるときは、次のように扱いを分ける。

用語	DBでの扱いの例	理由
受講者	`learners` テーブル	一覧、担当、ログ、支援ステータスから参照する中心になる
メンター	`mentors` テーブル	担当関係や更新者として参照する
担当関係	`mentor_assignments` テーブル、または `learners.mentor_id`	メンターと受講者の関係を確認するために必要
支援ステータス	`learner_support_statuses.status` の値	受講者ごとの現在状態として扱える
支援メモ	`learner_support_statuses.note` カラム	支援ステータスに付随する説明として扱える
未提出	学習ログの提出日時や提出有無から導く値	支援ステータスそのものではなく、学習ログから判断できる可能性がある
要支援	`needs_support` という状態値	テーブルではなく、許可された状態の一つとして扱える
困っている	メモ、困りごとカラム、または未定義として保留	何をもって困っていると判断するか定義が必要

保存しない判断も設計である。何でもテーブルにすると、設計は大きくなり、更新や整合性の確認も増える。逆に、保存すべき事実を保存しないと、後から表示、検索、履歴確認、監査ができなくなる。

担当関係を、関係として保存する

支援ステータス機能では、担当関係が重要である。メンターは担当受講者だけを支援できる、という第9章のルールがあるからである。

この関係は、mentor_assignments のようなテーブルで表せる。たとえば、mentor_id と learner_id の組み合わせを保存する。同じ組み合わせを重複登録できないように、主キーまたはUNIQUE制約を置く。

このテーブルがあると、メンターの担当受講者一覧を取得できる。また、第11章のAPIで、担当外の受講者を更新できないように確認する材料にもなる。

ただし、担当外更新をDB制約だけで完全に守れるとは限らない。どのログインユーザーがどのメンターなのか、requestがどの受講者を更新しようとしているのかは、API側の認可でも確認する必要がある。 DBで守ることと、アプリケーションコードで守ることを分けるのが設計である。

担当関係の保存形には、少なくとも二つのよくある案がある。

案	向いている場合	注意点
`learners.mentor_id` を持つ	受講者に担当メンターが常に一人だけ	複数担当、担当履歴、期間の概念を入れにくい
`mentor_assignments` テーブルを作る	複数担当や担当関係そのものを明示したい	JOINが一つ増え、制約やデータ投入も増える

この章と課題10では、関係を明示して練習するために mentor_assignments を使う。一方で、スターターアプリの実装では、初期範囲を小さくするために learners.mentor_id で表している箇所がある。これは矛盾ではなく、前提の違う設計選択である。自分の成果物では、どちらを選んだかと、その前提を data-model.md に書く。

支援ステータスを、現在値として持つか、履歴として持つか

支援ステータスには、現在値だけを持つ案と、変更履歴も持つ案がある。

現在値だけを持つ案では、learner_support_statuses に受講者ごとの現在の状態を保存する。一覧表示や更新はシンプルになる。初回リリースで、メンターが今の支援状態を見られればよいなら、この案で十分かもしれない。

履歴を持つ案では、support_status_history のようなテーブルに、いつ、誰が、どの状態へ変更したかを残す。後から支援の経緯を追える一方で、設計、API、画面、テストは複雑になる。

どちらが正しいかは、一般論だけでは決まらない。第9章のユースケースと境界に戻る。初回リリースで必要なのは、現在の支援状態を一覧で見られることか。それとも、支援の経緯や引き継ぎまで必要なのか。

判断は、次のように比べると説明しやすい。

案	できること	失うもの、増えるもの
現在値だけ	一覧表示と更新が簡単。受講者1人につき現在状態を1行で扱える	過去に誰がどう変更したかは残らない
履歴だけ	変更の経緯をすべて残せる	現在値を毎回計算する必要があり、SQLやAPIが複雑になる
現在値と履歴の両方	一覧は速く、監査や引き継ぎもできる	二つのテーブルを同時に更新するため、トランザクションと整合性確認が必要になる

この章では、初期案として現在値だけを持つ。履歴は検討事項として残す。この判断を data-model.md と db-change-note.md に理由つきで書く。

カラムは、意味、型、NULL可否、制約で説明する

カラム設計では、名前だけを決めても足りない。意味、型、必須か任意か、入れてよい値、誰が変えるか、いつ変わるかを考える。

learner_support_statuses なら、次のようなカラムが考えられる。

learner_id：受講者ID。外部キー。主キーとしても使える。
status：支援ステータス。定義した値だけを許す。
note：支援メモ。任意。
updated_by：更新したメンターID。外部キー。
updated_at：更新日時。

status という名前だけでは、何の状態か分かりにくいことがある。テーブル名や文脈で支援ステータスだと分かるならよい。曖昧なら、support_status のように具体化する選択もある。

型も意味を伝える。 IDは整数かUUIDか。日時は文字列として扱うのか、timestampとして扱うのか。 statusは文字列で始めるのか、別テーブルへの外部キーにするのか。この判断は、後のAPI responseやテストの期待値にも影響する。

learner_support_statuses の初期案を、表にすると次のようになる。

カラム	意味	型の例	NULL可否	制約、補足
`learner_id`	支援ステータスを持つ受講者	`INTEGER`、`TEXT`、UUIDなど	不可	主キー。`learners.id` への外部キー
`status`	現在の支援ステータス	`TEXT`	不可	`none`、`needs_support`、`in_progress`、`resolved` だけを許す
`note`	支援メモ	`TEXT`	任意、または空文字既定	NULLと空文字の意味を決めておく
`updated_by`	最後に更新したメンター	`INTEGER`、`TEXT`、UUIDなど	不可にする案が多い	`mentors.id` への外部キー。ただし担当者かどうかはAPIでも確認する
`updated_at`	最後に更新した日時	`TEXT`、`TIMESTAMP`、`TIMESTAMPTZ`など	不可	DBMSとアプリの日時方針に合わせる

ここでの INTEGER や TEXT は、学習用の例である。実際のアプリでは、既存のID型やDBMSの型に合わせる。たとえばPostgreSQLなら時刻に TIMESTAMPTZ を使うことがあり、SQLiteなら厳密な日時型ではなく文字列として扱うことも多い。

制約は、データを壊さないための設計である

制約は、DBに入れてよいデータの条件を決める仕組みである。面倒な付属機能ではない。アプリケーションコードが間違えても、最後にデータを守る防衛線になる。

代表的な制約を、支援ステータス機能に対応づける。

PRIMARY KEY: 行を一意に識別する。 learners.id や learner_support_statuses.learner_id に使える。

FOREIGN KEY: 別のテーブルの行との関係を守る。 learner_support_statuses.learner_id が存在する受講者を参照するようにできる。

NOT NULL: 必須の値を空にしない。支援ステータスの status や updated_at が必須なら使う。

UNIQUE: 重複してはいけない値や組み合わせを守る。同じメンターと受講者の担当関係を重複させないために使える。

CHECK: 入れてよい値を条件で制限する。 status IN ('none', 'needs_support', 'in_progress', 'resolved') のように、支援ステータスの候補値を守れる。

DB制約で守ることと、アプリケーションコードで守ることは分けて考える。存在しない受講者を参照しないことはDB制約で守りやすい。ログイン中のメンターが担当受講者だけを更新できることは、API側の認可でも確認する必要がある。

支援ステータス機能では、責任分担を次のように見る。

確認したいこと	DB制約で守りやすいか	APIやアプリケーションコードで見ること
受講者IDが存在する	守りやすい。外部キーを使う	エラー時に画面へどう伝えるか
メンターIDが存在する	守りやすい。外部キーを使う	ログインユーザーとメンターIDの対応
支援ステータスが許可値だけ	守りやすい。CHECK制約を使う	入力エラーのメッセージ、画面の選択肢
同じ担当関係を重複登録しない	守りやすい。複合主キーやUNIQUEを使う	重複登録しようとした操作の扱い
担当外の受講者を更新しない	DBだけでは扱いにくい	認証された利用者、担当関係、対象受講者をAPIで確認する
支援メモが長すぎない、空でよいか	DBで一部は可能	UI、入力検証、業務上の意味をそろえる

updated_by に外部キーを置くと、存在するメンターだけを更新者として保存できる。しかし、そのメンターが対象受講者を担当していることまでは自動では保証しない。この違いを混同しない。

ここからのSQL例は、課題10の schema-draft.sql に近い、学習用のたたき台である。たとえば、初期案をSQLiteで検証するなら、次のようなDDLから始められる。 DDLは Data Definition Language の略で、テーブルや制約の形を定義するSQLである。 SQLiteでは外部キー制約を接続ごとに有効化する必要があるため、検証時は PRAGMA foreign_keys = ON; を明示する。 PostgreSQLなど別のDBMSでは、型、日時、外部キー設定の扱い、UPSERTの細部が変わる。

PRAGMA foreign_keys = ON;

CREATE TABLE learners (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE mentors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE mentor_assignments (
  mentor_id INTEGER NOT NULL REFERENCES mentors(id),
  learner_id INTEGER NOT NULL REFERENCES learners(id),
  PRIMARY KEY (mentor_id, learner_id)
);

CREATE TABLE learner_support_statuses (
  learner_id INTEGER PRIMARY KEY REFERENCES learners(id),
  status TEXT NOT NULL CHECK (status IN ('none', 'needs_support', 'in_progress', 'resolved')),
  note TEXT,
  updated_by INTEGER NOT NULL REFERENCES mentors(id),
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

この例では、受講者ごとの現在ステータスを1行だけ持つため、learner_support_statuses.learner_id を主キーにしている。担当関係は、mentor_assignments の複合主キーで重複を防いでいる。支援ステータスの値はCHECK制約で固定している。このようにSQLを書くと、設計文書で話していた主キー、外部キー、NOT NULL、CHECKが、実際の保存形と結びつく。

スターターアプリの starter-apps/learning-log-sample/schema.sql では、PostgreSQLを前提に、IDを TEXT、日時を TIMESTAMPTZ とし、担当関係を learners.mentor_id で表している。また、支援メモは note TEXT NOT NULL DEFAULT '' で、空文字を既定値にしている。課題10のSQLドラフトとスターターアプリのSQLは、目的が少し違う。前者は設計判断を練習するためのたたき台であり、後者は動くサンプルアプリの実スキーマである。実装に進むときは、使うDBMSと既存スキーマに合わせてSQLを書き換える。

正規化は、重複による不整合を減らす考え方である

正規化は、同じ意味のデータを何度も持たないための考え方である。厳密な正規形をこの章で暗記する必要はない。まずは、重複したデータが後で食い違わないかを見る。

たとえば、受講者名を learning_logs の各行にコピーして保存するとする。受講者名が変わったとき、古い行だけ更新されずに残るかもしれない。受講者名は learners に置き、学習ログからは learner_id で参照すると、重複による不整合を減らせる。

一方で、何でも分ければよいわけでもない。支援ステータスの値が少なく、初回リリースでは固定でよいなら、文字列とCHECK制約で始めてもよい。状態値を管理画面から増やしたい、説明文や表示順をDBで管理したい、という要求が出てから別テーブルを考える選択もある。

設計は、将来を全て先取りすることではない。今のユースケースに必要な関係を守り、後で変えられる余地を説明することである。

SQLで、ユースケースを満たせるか確認する

SQLは、データベースに対して保存、取得、更新、結合、絞り込みを行う言語である。 SQL確認の目的は、構文が通るかだけではない。設計したテーブルで、実際のユースケースを満たせるかを見ることである。

最初に確認するのは、メンターの担当受講者一覧を取得できるかである。たとえば、learners、mentor_assignments、learner_support_statuses をJOINし、特定のメンターが担当する受講者と現在の支援ステータスを一覧にする。

SELECT
  learners.id,
  learners.name,
  COALESCE(learner_support_statuses.status, 'none') AS support_status,
  learner_support_statuses.note,
  learner_support_statuses.updated_at
FROM mentor_assignments
JOIN learners ON learners.id = mentor_assignments.learner_id
LEFT JOIN learner_support_statuses
  ON learner_support_statuses.learner_id = learners.id
WHERE mentor_assignments.mentor_id = ?
ORDER BY learners.id;

JOIN は、関係するテーブルをつないで読むための操作である。 LEFT JOIN を使うと、まだ支援ステータス行がない受講者も一覧に出せる。 COALESCE は、値がNULLの場合に代わりの値を返す関数である。この確認では、まだ行がない受講者を画面上では none として扱えるかを見る。

SQL中の ? は、アプリケーションから値を渡すためのプレースホルダーである。 SQLiteでは ?、PostgreSQLのライブラリでは $1、$2 のように書くことがある。文字列連結でSQLを作るのではなく、ライブラリのパラメータ機能で値を渡す。これは第14章のSQLインジェクション対策にもつながる。

SQL確認ログには、SQLだけでなく期待した結果も書く。たとえば「mentor_id = 1 の担当受講者が2件返り、ステータス行がない受講者は none と表示できる」のように書く。結果が空だった場合、データがないのか、JOIN条件が間違っているのかを切り分ける。

次に、支援ステータスを更新できるかを見る。現在値だけを持つ設計なら、同じ受講者の行をinsertまたはupdateする必要がある。 SQLiteやPostgreSQLでは ON CONFLICT のような書き方が使える場合があるが、DB製品によって書き方は違う。大切なのは、同じ意図をSQLで確認することである。

INSERT INTO learner_support_statuses (learner_id, status, note, updated_by)
VALUES (?, 'needs_support', '提出が止まっているため確認する', ?)
ON CONFLICT (learner_id) DO UPDATE SET
  status = excluded.status,
  note = excluded.note,
  updated_by = excluded.updated_by,
  updated_at = CURRENT_TIMESTAMP;

ON CONFLICT は、主キーやUNIQUE制約にぶつかったときの扱いを決める書き方である。このSQLでは、まだ行がなければ追加し、既に行があれば現在値を更新する。このSQLは、現在値だけを持つ設計と相性がよい。履歴も持つ設計へ変えるなら、現在値の更新と履歴行の追加を同じトランザクションで扱う必要が出る。

さらに、失敗すべき操作も試す。定義外の支援ステータスを入れようとしたら失敗するか。存在しない受講者IDを参照しようとしたら失敗するか。同じ担当関係を重複登録しようとしたら失敗するか。次の例は、課題10のサンプルデータのように、受講者 1、メンター 1、担当関係 (1, 1)、受講者 1 の支援ステータス行が既にある前提である。

-- CHECK制約で失敗することを確認する
UPDATE learner_support_statuses
SET status = 'urgent'
WHERE learner_id = 1;

-- 外部キー制約で失敗することを確認する
INSERT INTO learner_support_statuses (learner_id, status, updated_by)
VALUES (9999, 'needs_support', 1);

-- 主キーまたはUNIQUE制約で失敗することを確認する
INSERT INTO mentor_assignments (mentor_id, learner_id)
VALUES (1, 1);

成功するSQLだけを書くと、制約が効いているか分からない。失敗すべきものが失敗することを確認して、設計の意味が見える。失敗確認では、エラーメッセージを丸写しするだけでなく、どの制約が効いたのかを書く。たとえば「status のCHECK制約で urgent が拒否された」「learner_id の外部キーで存在しない受講者が拒否された」のように残す。

インデックス、トランザクション、マイグレーションの入口

インデックスは、検索や結合を助けるための仕組みである。よく使う検索条件や、表をつなぐ条件から候補を考える。たとえば、メンターIDで担当受講者を探すことが多いなら、mentor_assignments.mentor_id はインデックス候補になる。ただし、インデックスは増やせばいつも良いわけではない。更新コストや運用も考える必要があるため、この章では候補として記録する程度でよい。

トランザクションは、複数の更新をひとまとまりとして成功または失敗させる仕組みである。たとえば、支援ステータスを更新し、同時に履歴を追加する場合、片方だけ成功すると困る。現在値だけの初期案では単純でも、履歴を持つ案ではトランザクションを考える必要が出る。

マイグレーションは、アプリの変更に合わせてDBスキーマを段階的に変更する作業である。テーブル追加、カラム追加、制約追加は、既存データに影響する。たとえば、既存の受講者に支援ステータスを追加するなら、初期値をどうするかを決める必要がある。

インデックス、トランザクション、マイグレーションは入口だけを扱う。ただし、DB変更メモに、考慮が必要な項目として残す。

この章では、次の粒度で十分である。

観点	何を見るか	今回の書き方
インデックス	よく絞り込むカラム、JOINに使うカラム	`mentor_assignments.mentor_id` は候補、とメモする
トランザクション	複数更新の片方だけ成功すると困るか	履歴を追加する将来案では必要、とメモする
マイグレーション	既存データに新しい制約やカラムが当たるか	初期値、バックフィル、ロールバック方針をメモする

バックフィルは、既存データに新しいカラムの値を埋める作業である。たとえば、既存の受講者全員に learner_support_statuses の none を作るのか、行がない場合に画面で none とみなすのかは、移行方針として決める。

DB変更メモは、PRとAPI設計への橋である

DB変更は、コード差分より影響が長く残ることが多い。だから、変更理由と影響を短いメモとして残す。

db-change-note.mdには、次を書く。

変更理由。
追加するテーブル、カラム、制約。
既存データへの影響。
初期データやデフォルト値。
確認SQL。
アプリケーションコード側で必要な対応。
リスク。
PR本文に書くこと。
第11章への引き継ぎ。

たとえば、今回のDB変更メモには次のように書ける。

## 変更理由

メンターが担当受講者の現在の支援ステータスを一覧で確認し、手動で更新できるようにするため。

## 追加するテーブル、カラム、制約

- learner_support_statuses を追加する。
- learner_id は learners.id への外部キーで、受講者1人につき現在値を1行だけ持つ。
- status は none / needs_support / in_progress / resolved のみ許可する。

## 既存データへの影響

- 既存受講者にステータス行を作るか、行がない場合にAPIで none とみなすかを決める。
- 初回リリースでは履歴テーブルは作らない。

## 第11章への引き継ぎ

- GETで担当受講者一覧と現在ステータスを返す。
- PATCHでstatusとnoteを更新する。
- 更新時はログイン中のメンターが対象受講者を担当しているか確認する。

第11章への引き継ぎでは、APIで必要になりそうなendpoint、requestとresponseに含める項目、入力検証と認可で注意することを書く。たとえば、支援ステータスを更新するAPIでは、learner_id、status、note、更新者、担当関係の確認が論点になる。

DB変更メモは、完璧な設計書ではない。レビューする人が、なぜこの保存形にしたのか、何を確認したのか、次のAPI設計で何を見るべきかを理解するための橋である。

AIは、スキーマ案ではなく確認観点にも使う

AIは、テーブル候補、カラム候補、制約候補、確認SQL、正規化や重複の観点、インデックス候補の洗い出しに使える。ただし、実データや機密情報を渡してはいけない。また、AIが出したスキーマ案を、そのまま採用してはいけない。

AIに依頼するときは、第9章で決めた用語、ルール、対象外を渡す。たとえば、メンターは担当受講者だけを支援できる、支援ステータスは手動で変更する、自動アラートや分析ダッシュボードは対象外、という前提である。

出してもらうものは、テーブル候補と役割、主キー、外部キー、UNIQUE、CHECK、現在値だけ持つ案と履歴を持つ案の違い、SELECT、INSERT、UPDATE、JOINの確認SQL、既存データへの影響として確認すべきことなどである。

AIの回答は、設計案であって決定ではない。既存データへの影響、命名規則、使うDBで動くか、DB制約とアプリケーションコードの役割分担は、人が確認する。実行していないSQLを、確認済みとして扱ってはいけない。

AIの出力を受け取ったら、最低限次を確認する。

第9章で対象外にした自動アラート、分析ダッシュボード、CSV出力を勝手に追加していないか。
既存スキーマのID型、日時型、テーブル名に合っているか。
使うDBMSで動くSQLか。SQLite、PostgreSQL、MySQLでは文法や型が違う。
主キー、外部キー、NOT NULL、UNIQUE、CHECKが、業務ルールと対応しているか。
DB制約だけで認可まで守れるように書いていないか。
成功するSQLだけでなく、失敗すべきSQLも確認しているか。
実データ、個人情報、秘密情報をAIに渡していないか。

スキーマとSQLで確認すること

data-model.md、constraint-checklist.md、schema-draft.sql、sql-check-log.md、db-change-note.md を作る。

data-model.md には、テーブル候補、業務上の意味、主な利用場面、関係、テーブルにしない用語、現在値か履歴かの判断を書く。 constraint-checklist.md には、テーブル別のカラム、型、必須かどうか、主キー、外部キー、UNIQUE、CHECK、DB制約で守ること、アプリケーションコードで守ることを書く。 schema-draft.sql には、テーブル案、制約、サンプルデータを書く。 sql-check-log.md には、担当受講者一覧の取得、支援ステータス更新、制約違反の確認、設計を見直したい点、第11章へ渡す材料を書く。 db-change-note.md には、変更理由、既存データへの影響、確認SQL、リスク、PR本文に書くこと、第11章への引き継ぎを書く。

成果物の目的は、DB製品ごとの文法を網羅することではない。なぜそのテーブルや制約が必要か、SQLでユースケースを確認できたか、既存データやAPIにどんな影響があるかを説明できるようにすることである。また、この章はアプリケーションDB設計に集中する。分析SQL、データ基盤、ダッシュボード、CSV出力へ広げすぎない。広げたくなった論点は、対象外または将来検討として db-change-note.md に分けて残す。

DB設計で起きやすい誤解

すべての概念をテーブルにする。
すべてを文字列カラムにして、状態、日時、IDの意味を失う。
支援ステータスの現在値と履歴を、理由なく同時に作る。
DB制約で守ることと、API側で確認することを分けない。
外部キーがあるだけで、担当外更新の認可まで守れたと思う。
NULL、空文字、none、0 を同じ意味として扱う。
サンプルデータが入ったことだけで、制約やJOINも確認できたと思う。
SQLiteで動いたSQLが、そのままPostgreSQLやMySQLでも動くと思う。
スターターアプリの実スキーマと課題用ドラフトの違いを、前提の違いとして説明しない。
成功するSELECTだけを書き、制約違反が失敗することを確認しない。
既存データへの影響や初期値を考えずにNOT NULLカラムを追加する。
AIが出したSQLを、実行または設計材料との照合なしに採用する。

データを保存する章で持ち帰ること

第10章で身につけるべきことは、第9章の言葉、状態、ルールを、保存できる形へ変換することである。テーブルは、保存すべき事実を集める場所である。カラムは、行に保存する属性であり、型、NULL可否、制約とセットで考える。主キーと外部キーは、行の識別と関係を守る。 NOT NULL、UNIQUE、CHECKは、入れてよい値を守る。 SQL確認では、成功する操作だけでなく、失敗すべき操作も試す。

データベース設計は、後続のAPI、画面、テスト、運用に長く効く。保存の意味が曖昧なら、後の実装も曖昧になる。

APIで画面と業務をつなぐ章へ

次章では、このデータ設計を画面から呼び出せるAPI契約へ変換する。支援ステータスを取得するendpoint、更新するendpoint、requestとresponse、入力検証、認可、DB制約違反時のエラーを考える。第10章で作ったdata-model.md、constraint-checklist.md、sql-check-log.md、db-change-note.mdが、第11章のAPI設計の材料になる。

第10章 データを保存できる形にする