はじめに
この記事は、前回の達人に学ぶDB設計徹底指南書~初級者で終わりたくないあなたへ~を読んで Part1の続きです。もしよければPart1にざっと目を通して読んでいただけると分かりやすいかと思います。
それでは早速前回の続きである正規化について確認していきます。
#1 おさらい
Part1でも書きましたが、正規化とはデータのCRUD操作が整合的に行えるようにエンティティを整える事です。RDBでは定義されたエンティティをテーブルという単位で扱いますので、以降はテーブルと表記しますが、論理設計で定義されたエンティティと同義です。
#2 テーブルとは？
いきなりですが、クイズです。下記の表はRDBにおけるテーブルでしょうか？難しく考えずに自分なりの回答を決めてください！
| 項目1 | 項目2 | 項目3 | 項目4 |
|---|---|---|---|
| 大谷翔平 | 30本塁打 | メジャーリーガー | 左打ち |
| 歯ブラシ | 髭剃り | ファンデーション | 耳かき |
| マイケルジャクソン | 歌手 | ポップ | アメリカ |
| 高校生 | 2年生 | 数学 | 微積分 |
回答は決まりましたでしょうか？？
正解は・・・・
テーブルではありません！
理由としては、テーブル要件である共通点を持ったレコードの集合体という点を満たしていないからです。
DB設計では、大前提としてテーブル要件を満たしていることが必要です。自身でテーブルを作成する際、この要件を満たしているかどうか見極める簡単な方法があります。
テーブル名は複数形または複数名詞で書ける
これで判断できます。
以下のテーブルであれば、employeesとなり、これはRDBにおけるテーブルの要件を満たしていると判断できます。
| 社員ID | 社員名 | 所属 | 年齢 |
|---|---|---|---|
| A001 | 田中 | 営業 | 25 |
| A002 | 鈴木 | 経理 | 48 |
| A003 | 山田 | 人事 | 33 |
テーブルの構成要素について
テーブル構成用については以下のものがあります。
上記の共通点を持ったレコードの集合体という特徴以外にも、以下の要素を持つことが必要です。
- 主キー
Primary Keyとも呼ばれるもので、この値を指定すれば、必ず1行のレコードを特定できるような列の組み合わせの事を指し、テーブルには必ず1つ存在しなければならず、かつ、1つしか存在できません。
上記のテーブルで言えば、社員番号が主キーとなり得ます。 - 外部キー
Foreign Key(FK)と呼ばれるもので、2つのテーブル間で設定するもので、参照先のテーブルにない情報を登録できないようにする制約設定するもの。
#3. 第1正規形について
テーブルの定義が確認できたところで、第1正規形の定義について確認します。
第1正規形の定義: 一つのセルの中には一つの値(スカラ値)しか含まない
当たり前過ぎるだろ・・・と私も読んでいて思いましたが、ふと思い返すと、客先のテーブルの中にTEXT型のところにJSON風に50個ぐらいデータを詰め込んであった事を思い出し、意外と身近にあるものだと思いました（笑）
ということで、例えば以下のような修正前テーブルを修正後テーブルに直すことが、第1正規形への正規化となります。
(修正前)
| 社員ID | 社員名 | 子供名 |
|---|---|---|
| A001 | 田中 | 太郎、花子 |
| A002 | 鈴木 | |
| A003 | 山田 | 一郎、二郎、三郎 |
(修正後)
| 社員ID | 社員名 | 子供名 |
|---|---|---|
| A001 | 田中 | 太郎 |
| A001 | 田中 | 花子 |
| A002 | 鈴木 | |
| A003 | 山田 | 一郎 |
| A003 | 山田 | 二郎 |
| A003 | 山田 | 三郎 |
なぜ列方向に増やすのでなく、行方向に増やすのか
以下のように正規化してもよいのでは？と考えた方もいらっしゃるのではないでしょうか| 社員ID | 社員名 | 子供1 | 子供2 | 子供3 |
|---|---|---|---|---|
| A001 | 田中 | 太郎 | 花子 | |
| A002 | 鈴木 | |||
| A003 | 山田 | 一郎 | 二郎 | 三郎 |
ただ、この形式は以下の理由により推奨できません。
-
1つのエンティティが複数列(属性)に分解されてしまっている
RDBでは1つの属性に対して1つの値と決まっていますが、属性(今回で言えば子供)が複数に分かれてしまっているので、設計として破綻してしまう -
拡張性がない(スケーラビリティの破綻)
4人目が出来た場合に拡張しにくい。また、最初から10人目までなど列数を持つと、余計なデータが増える -
関数従属性が破綻している
関数従属性とは、主キーを指定すると、他の列が確定(行を特定)すること
一見すると、3の関数従属性は列方向に増やした方が良さそうですが、子供の例の場合、本質的に考えると社員ID→子供が1つに決まることが正解ではありません。
この場合、子は親に対して複数値とる属性であり、 [1:多] の関係になります。
新しく行方向に増やしたテーブルでは、主キーは(社員ID + 子供)になり、これによって関数従属性は担保されます。
以上のことから、列方向に増やす事で、データ取得のしやすさは上ですが、設計の本質的には破綻しているため、行方向へと増やす正規化を行いました。
#4. 第2正規形について
第1正規形への正規化が終われば、次は第2正規形への正規化を行います。
第2正規形の定義: 部分関数従属を解消させる
部分関数従属とは何かという点も含めて詳しく見ていきます。
今回は下記の第1正規形テーブルを第2正規形へと正規化していきます。
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| A123456 | Z電気 | 000A | 山田 | 45 | B01 | 営業 |
| A123456 | Z電気 | 001A | 斎藤 | 23 | B02 | 人事 |
| A123456 | Z電気 | 002A | 渡辺 | 55 | B03 | 総務 |
| B987654 | A工業 | 000A | 小田 | 40 | B04 | 開発 |
| B987654 | A工業 | 005A | 北条 | 43 | B01 | 営業 |
| B987654 | A工業 | 010A | 山崎 | 38 | B03 | 総務 |
主キーは (会社コード)+(社員ID) です。
この時、主キーの一部に対して従属している、つまり、一部の主キーが決まることで値が一意に決まってしまうような列がある場合、この関係を部分関数従属と言います。
そして、このようにある値を決めると他の値が決まる性質の事を関数従属性があるといいます。
上記のテーブルで言えば、
(会社コード) → (会社名)
となっています。この部分関数従属を解消し、主キーを構成するすべての列があることで、その他の列が特定できるようになる状態(完全関数従属)にすることが、第2正規形への正規化です。
第2正規形は元のテーブルに加えて会社テーブルを追加した以下の2つのテーブルになります。
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| A123456 | 000A | 山田 | 45 | B01 | 営業 |
| A123456 | 001A | 斎藤 | 23 | B02 | 人事 |
| A123456 | 002A | 渡辺 | 55 | B03 | 総務 |
| B987654 | 000A | 小田 | 40 | B04 | 開発 |
| B987654 | 005A | 北条 | 43 | B01 | 営業 |
| B987654 | 010A | 山崎 | 38 | B03 | 総務 |
| 会社コード | 会社名 |
|---|---|
| A123456 | Z電気 |
| B987654 | A工業 |
Primary Key: 会社コード + 社員ID
Foreign Key: 会社コード
第2正規形を行うことのメリット
メリットとしては以下の2点です。
- 登録出来ない情報がなくなる
正規化前のテーブルでは、会社コードと社員IDが主キーだったため、従業員情報の無い会社'Y銀行'などが登録できない、または、社員IDにダミー値を入れて仮登録しなければなりませんでした。正規化を行うことで、社員情報がなくても会社テーブルに追加出来るので、データの整合性が保たれるというメリットがあります。 - 誤情報登録を防止できる
正規化前のテーブルでは、A123456 Z電気 というレコード以外に、 A123456 Z電機 と誤入力も登録できるような形式になっています。会社テーブルを作成してFKで参照することで、誤情報登録を防止できます。
#5. 第3正規形について
最後に第3正規形を行います。
第3正規形の定義: 推移的関数従属を解消させる
先ほど作成した第2正規形の2つのテーブルを使用して、推移的関数従属とは何かという点も含めて詳しく見ていきます。
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| A123456 | 000A | 山田 | 45 | B01 | 営業 |
| A123456 | 001A | 斎藤 | 23 | B02 | 人事 |
| A123456 | 002A | 渡辺 | 55 | B03 | 総務 |
| B987654 | 000A | 小田 | 40 | B04 | 開発 |
| B987654 | 005A | 北条 | 43 | B01 | 営業 |
| B987654 | 010A | 山崎 | 38 | B03 | 総務 |
| 会社コード | 会社名 |
|---|---|
| A123456 | Z電気 |
| B987654 | A工業 |
今Z電気には【営業, 人事, 総務】の3部署がありますが、所属社員がいないだけで【経理】という部署があるとします。現状では、PKが会社コード+社員IDとなっているため、所属社員がいないと登録できません(挿入異常)。つまり、部署コードと部署名の間には関数従属性があります。
そして、社員IDが決まれば部署コードも一意に決まります。つまり
社員ID → 部署コード → 部署名
となっています。
このように段階的な従属関係の事を推移的関数従属といい、これを解消することが第3正規形への正規化です。
(部署コードはPKではないので、第2正規形への正規化である、部分関数従属を解消するというタイミングでは処理されませんでした)
正規化を行い、第3正規形へ変換を行った結果が以下の通りです。
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード |
|---|---|---|---|---|
| A123456 | 000A | 山田 | 45 | B01 |
| A123456 | 001A | 斎藤 | 23 | B02 |
| A123456 | 002A | 渡辺 | 55 | B03 |
| B987654 | 000A | 小田 | 40 | B04 |
| B987654 | 005A | 北条 | 43 | B01 |
| B987654 | 010A | 山崎 | 38 | B03 |
| 会社コード | 会社名 |
|---|---|
| A123456 | Z電気 |
| B987654 | A工業 |
| 部署コード | 部署名 |
|---|---|
| B01 | 営業 |
| B02 | 人事 |
| B03 | 総務 |
| B04 | 開発 |
#6. 以降の正規化について
一般的なDB設計では、第3正規形まで行っておけば事足りると本書では書かれておりますので、以降は割愛しますが、気になる方は以下のキーワードで調べてみてください。
- ボイス・コッド正規形
- 第4正規形
- 第5正規形
#7. 正規化についてのまとめ
正規化のポイントは以下の3つがあります。
1. 正規化とは、データ更新時の不都合、不整合を排除するために行う
2. 従属性を見抜くこと
3. 正しく正規化されたテーブルは、いつでも非正規化テーブル(元のテーブル)に復元できる
最後に、正規化って必ずしなければならないのか？しなくても大丈夫なんじゃない？という疑問があるかもしれません。
これに対しても本書では以下のように回答していました。
第3正規形までは、原則として行う
理由としては、上記に挙げた3点です。
また、以下のようにも書かれていました。
パフォーマンス改善にあたって物理設計やその他調整できることはすべて調整して、これ以上打つ手がないという状況ではない限り、非正規形のテーブルを定義すべきではない(最終手段)
ただ、正規化を行うことで、しばしばシステムとして実用に耐えないぐらいの副作用があります。
#8. 正規化のデメリットについて
パフォーマンスが落ちる
結論として、正規化のデメリットはコストが高い操作が増えることにより、検索パフォーマンスが落ちるという点です。
SQLにおいて結合は非常にコストの高い操作になりますが、正規化されたテーブルから情報を取得しようと思おうと、内部結合、外部結合などの結合操作が増えます。
例えば、第3正規形まで正規化した後に、「山田さんの会社名と部署を知りたい」となったときに、会社テーブルと部署テーブルを従業員テーブルに結合してから検索をするという流れになります。
一方で、第1正規形であれば、テーブル1つで完結します。つまり、結合を利用しないので、パフォーマンスは正規化されたものよりも良くなります。
つまり正規化とSQLの検索パフォーマンスはトレードオフの関係にあり、厳しく正規化すればパフォーマンスが落ち、パフォーマンス重視で非正規化を進めると、データ不整合が発生しやすくなります。
非正規化への変換例、どのような観点から非正規化を検討するかということも本書には記載されていますが、それ以上に、先ほども書きましたが、本書でも何度も以下のように記載されています。
パフォーマンス改善にあたって物理設計やその他調整できることはすべて調整して、これ以上打つ手がないという状況ではない限り、非正規形のテーブルを定義すべきではない(最終手段)
ということなので、ここではこれ以上取り上げません。詳しく知りたい方は実際に本書で確認してみてください。
#9. 練習問題
Geminiに作成してもらった正規化の練習問題を載せておきます。
実際に自分で手を動かすことで理解が深まると思いますので、どの程度自分が理解できたか試してみてください。
正規化後の状態を、実行可能な CREATE TABLE 文で回答してください（必要に応じて FOREIGN KEY などの制約も付与してください）
問題、正解のコードはPSQLで書いています。
Q1 経費精算・レシート管理システムのDB正規化
CREATE TABLE raw_receipt_expenses (
receipt_id VARCHAR(50), -- レシート(経費申請)を一意に識別するID
item_line_no INTEGER, -- レシート内の明細行番号 (1, 2, 3...)
upload_date DATE, -- レシートのアップロード日
employee_id VARCHAR(20), -- 経費を申請した社員のID
employee_name VARCHAR(100), -- 社員名
department_code VARCHAR(10), -- 社員が所属する部署のコード
department_name VARCHAR(50), -- 部署名
store_name VARCHAR(100), -- 購入した店舗名
store_category VARCHAR(50), -- 店舗の業種（例: コンビニ、書店、カフェ）
item_name VARCHAR(100), -- 購入した商品名
unit_price NUMERIC, -- 商品の単価
quantity INTEGER, -- 購入数量
PRIMARY KEY (receipt_id, item_line_no)
);
データの前提条件（業務ルール）:
-
1人の社員（employee_id）は、必ず1つの部署（department_code）に所属しています。
-
部署コード（department_code）が決まれば、部署名（department_name）は一意に決まります。
-
1つのレシート（receipt_id）は、1人の社員によって、特定の1つの店舗（store_name）で決済された1回のお買い物を表します。
-
店舗名（store_name）が決まれば、その店舗の業種（store_category）は一意に決まると仮定します。
-
1つのレシートの中には、複数の商品明細（item_line_no）が含まれることがあります。
回答
-- 1. 部署テーブル (マスタ)
CREATE TABLE departments (
department_code VARCHAR(10) PRIMARY KEY,
department_name VARCHAR(50) NOT NULL
);
-- 2. 社員テーブル (マスタ)
CREATE TABLE employees (
employee_id VARCHAR(20) PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_code VARCHAR(10) NOT NULL,
CONSTRAINT fk_emp_department
FOREIGN KEY (department_code) REFERENCES departments(department_code)
);
-- 3. 店舗カテゴリテーブル (マスタ)
-- ※本来は店舗マスタを作るか、店舗名自体をマスタ化するか要件次第ですが、
-- 今回は提示された条件に従い店舗名からカテゴリが引けるようにします。
CREATE TABLE store_categories (
store_name VARCHAR(100) PRIMARY KEY,
store_category VARCHAR(50) NOT NULL
);
-- 4. レシートテーブル (トランザクション：ヘッダー情報)
CREATE TABLE receipts (
receipt_id VARCHAR(50) PRIMARY KEY,
upload_date DATE NOT NULL,
employee_id VARCHAR(20) NOT NULL,
store_name VARCHAR(100) NOT NULL,
CONSTRAINT fk_receipt_employee
FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
CONSTRAINT fk_receipt_store
FOREIGN KEY (store_name) REFERENCES store_categories(store_name)
);
-- 外部キーには自動でインデックスが張られないため、検索要件に応じてインデックスを付与します。
CREATE INDEX idx_receipt_employee_id ON receipts(employee_id);
-- 5. レシート明細テーブル (トランザクション：明細情報)
CREATE TABLE receipt_details (
receipt_id VARCHAR(50),
item_line_no INTEGER,
item_name VARCHAR(100) NOT NULL,
unit_price NUMERIC NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (receipt_id, item_line_no), -- 複合主キー
CONSTRAINT fk_detail_receipt
FOREIGN KEY (receipt_id) REFERENCES receipts(receipt_id)
ON DELETE CASCADE -- レシートが消えたら明細も消える設定(実務では論理削除にするか検討が必要)
);
Q2. 日報・工数管理システムのDB正規化
CREATE TABLE raw_work_reports (
report_id VARCHAR(50), -- 日報を一意に識別するID
task_line_no INTEGER, -- 日報内の作業明細行番号 (1, 2, 3...)
report_date DATE, -- 作業日（日報の対象日）
engineer_id VARCHAR(20), -- 作業を行ったエンジニアのID
engineer_name VARCHAR(100), -- エンジニア名
skill_grade_code VARCHAR(10),-- エンジニアの職務等級コード (例: J1, S2)
skill_grade_name VARCHAR(50),-- 職務等級名 (例: ジュニア, シニア)
project_code VARCHAR(20), -- 作業対象のプロジェクトコード
client_name VARCHAR(100), -- プロジェクトの発注元クライアント企業名
task_content VARCHAR(200), -- 具体的な作業内容 (例: 「RAG検証エージェントの実装」など)
working_hours NUMERIC, -- その作業に費やした時間 (例: 2.5)
PRIMARY KEY (report_id, task_line_no)
);
データの前提条件（業務ルール）:
-
1人のエンジニア（engineer_id）は、必ず1つの職務等級（skill_grade_code）に紐づいています。
-
職務等級コード（skill_grade_code）が決まれば、等級名（skill_grade_name）は一意に決まります。
-
1つの日報（report_id）は、1人のエンジニアが、特定の1つのプロジェクト（project_code）に対して行った「1日分の作業」をまとめたものです。
-
プロジェクトコード（project_code）が決まれば、発注元クライアント名（client_name）は一意に決まると仮定します。
-
1つの日報の中には、複数の作業明細（task_line_no）が含まれることがあります（例：1行目は要件定義書の作成で2時間、2行目はJavaScriptのフロント実装で3時間、など）。
回答
-- 1. 職務等級テーブル (マスタ)
CREATE TABLE skill_grades (
skill_grade_code VARCHAR(10) PRIMARY KEY,
skill_grade_name VARCHAR(50) NOT NULL
);
-- 2. プロジェクトテーブル (マスタ)
CREATE TABLE projects (
project_code VARCHAR(20) PRIMARY KEY,
client_name VARCHAR(100) NOT NULL
);
-- 3. エンジニアテーブル (マスタ)
CREATE TABLE engineers (
engineer_id VARCHAR(20) PRIMARY KEY,
engineer_name VARCHAR(100) NOT NULL,
skill_grade_code VARCHAR(10) NOT NULL,
CONSTRAINT fk_engineers_skill
FOREIGN KEY (skill_grade_code) REFERENCES skill_grades(skill_grade_code)
);
-- 4. レポートテーブル (トランザクション：ヘッダー)
CREATE TABLE reports (
report_id VARCHAR(50) PRIMARY KEY,
report_date DATE NOT NULL,
engineer_id VARCHAR(20) NOT NULL,
project_code VARCHAR(20) NOT NULL,
CONSTRAINT fk_reports_engineers
FOREIGN KEY (engineer_id) REFERENCES engineers(engineer_id),
CONSTRAINT fk_reports_projects
FOREIGN KEY (project_code) REFERENCES projects(project_code)
);
CREATE INDEX idx_reports_engineer ON reports(engineer_id);
CREATE INDEX idx_reports_project ON reports(project_code);
-- 5. レポート詳細テーブル (トランザクション：明細)
CREATE TABLE report_details (
report_id VARCHAR(50) NOT NULL,
task_line_no INTEGER NOT NULL,
task_content VARCHAR(200) NOT NULL,
working_hours NUMERIC NOT NULL,
PRIMARY KEY (report_id, task_line_no),
CONSTRAINT fk_details_reports
FOREIGN KEY (report_id) REFERENCES reports(report_id)
ON DELETE CASCADE -- 日報本体が削除されたら明細も一緒に削除する
);
Q3. 多対多のリレーションDB正規化
-- エージェント管理テーブル
CREATE TABLE ai_agents (
agent_id VARCHAR(20) PRIMARY KEY,
agent_name VARCHAR(100) NOT NULL,
description TEXT,
equipped_tools VARCHAR(200) -- ここにカンマ区切りでツール名を入れる想定らしい
);
-- データ挿入のイメージ（カンマ区切りで無理やり入れている）
-- INSERT INTO ai_agents VALUES ('A001', '要件定義エージェント', '要件定義書を作成します', 'WebSearch, RAG_Retriever, File_Writer');
データの前提条件（業務ルール）:
-
1つのエージェントは、複数のツールを使用することができます。
（例：要件定義作成エージェントは、「Web検索ツール」と「社内ドキュメントRAGツール」の両方を使う） -
1つのツールは、複数のエージェントから共有して使用されます。
（例：「Web検索ツール」は、要件定義作成エージェントも、コード生成エージェントも使う）
回答
-- 1. エージェントテーブル (マスタ)
CREATE TABLE agents (
agent_id VARCHAR(20) PRIMARY KEY,
agent_name VARCHAR(100) NOT NULL,
description TEXT
);
-- 2. ツールテーブル (マスタ)
CREATE TABLE tools (
tool_id VARCHAR(10) PRIMARY KEY,
tool_name VARCHAR(100) NOT NULL,
description TEXT
);
-- 3. 中間テーブル (エージェントとツールの紐付け)
CREATE TABLE agent_tool (
agent_id VARCHAR(20) NOT NULL,
tool_id VARCHAR(10) NOT NULL,
-- ★ポイント：複合主キーを設定し、重複登録を防止する
PRIMARY KEY (agent_id, tool_id),
CONSTRAINT fk_agent_tool_agents
FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
CONSTRAINT fk_agent_tool_tools
FOREIGN KEY (tool_id) REFERENCES tools(tool_id) ON DELETE CASCADE
);
CREATE INDEX idx_agent_tool_tool_id ON agent_tool(tool_id);
Q4. 中間テーブルが独自の属性を持つDB正規化
CREATE TABLE raw_doc_generation_jobs (
job_id VARCHAR(50), -- ドキュメント生成の実行ジョブID
project_name VARCHAR(100), -- 紐づくプロジェクト名（例：社内システム刷新）
client_company VARCHAR(100), -- プロジェクトの発注元クライアント企業名
doc_type VARCHAR(50), -- 生成するドキュメントの種類（要件定義書、基本設計書など）
target_language VARCHAR(20), -- 出力対象の言語（JavaScript, Pythonなど）
pipeline_step_no INTEGER, -- パイプラインにおける実行ステップ順 (1, 2, 3...)
model_id VARCHAR(20), -- そのステップで使用したAIモデルのID
model_name VARCHAR(50), -- モデル名（GPT-4, LLaVA-VLMなど）
provider VARCHAR(50), -- 提供元（OpenAI, Googleなど）
step_role VARCHAR(50), -- このステップでの役割（RAG検索用, 画像解析VLM, メイン文章生成など）
tokens_used INTEGER, -- このステップで消費したトークン数
PRIMARY KEY (job_id, pipeline_step_no)
);
データの前提条件（業務ルール）:
-
1つのジョブ（job_id）は、1つのプロジェクト（project_name）に関する、特定のドキュメント（doc_type）と対象言語（target_language）を生成するための1回の実行単位です。
-
プロジェクト名（project_name）が決まれば、クライアント企業名（client_company）は一意に決まります。
-
1つのモデルID（model_id）が決まれば、モデル名（model_name）と提供元（provider）は一意に決まります。
-
多対多の関係と交差データの存在:
-
1つのジョブは、ステップ（pipeline_step_no）ごとに複数のAIモデルを順番に呼び出します。
-
1つのAIモデルは、複数のジョブで何度も利用されます。
-
さらに、ジョブとAIモデルが交差する「各ステップ」において、「どのような役割で呼び出されたか（step_role）」「トークンをいくつ消費したか（tokens_used）」という固有の記録が発生します。
-
-
注意点: 1つのジョブの中で、同じAIモデルが複数回（例えばステップ1とステップ3で）呼び出されることもあり得ます。
-
注意点: project_nameは変更する場合がある
-
job_idやmodel_idは型を変更してもよい
回答
-- 1. プロジェクトテーブル
CREATE TABLE projects (
project_id BIGSERIAL PRIMARY KEY, -- ポイント: idを追加
project_name VARCHAR(100) NOT NULL,
client_company VARCHAR(100) NOT NULL
);
-- 2. モデルテーブル
CREATE TABLE models (
model_id BIGSERIAL PRIMARY KEY, -- ポイント: VARCHARからBIGSERIALへ変更
model_name VARCHAR(50) NOT NULL,
provider VARCHAR(50) NOT NULL
);
-- 3. ジョブテーブル
CREATE TABLE jobs (
job_id BIGSERIAL PRIMARY KEY, -- ポイント: VARCHARからBIGSERIALへ変更
project_id BIGINT NOT NULL,
doc_type VARCHAR(50) NOT NULL,
target_language VARCHAR(20) NOT NULL,
CONSTRAINT fk_jobs_projects
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);
-- 4. パイプライン明細テーブル (中間テーブル 兼 トランザクション詳細)
CREATE TABLE jobs_models (
job_id BIGINT NOT NULL,
pipeline_step_no INTEGER NOT NULL,
model_id BIGINT NOT NULL,
step_role VARCHAR(50),
tokens_used INTEGER,
PRIMARY KEY (job_id, pipeline_step_no),
CONSTRAINT fk_jobs_models_jobs
FOREIGN KEY (job_id) REFERENCES jobs(job_id) ON DELETE CASCADE,
CONSTRAINT fk_jobs_models_models
FOREIGN KEY (model_id) REFERENCES models(model_id) ON DELETE CASCADE
);
CREATE INDEX idx_jobmodel_modelid ON jobs_models(model_id);
Q5. 自己参照リレーションDB正規化
CREATE TABLE raw_folders (
folder_id BIGSERIAL PRIMARY KEY, -- フォルダのID
folder_name VARCHAR(100) NOT NULL, -- フォルダ名
owner_user_id VARCHAR(20) NOT NULL, -- このフォルダを作成したユーザーのID
owner_user_name VARCHAR(100) NOT NULL,-- ユーザー名
parent_folder_id BIGINT, -- 1つ上の階層（親）のフォルダID
parent_folder_name VARCHAR(100), -- 親フォルダの名前
root_folder_id BIGINT -- 一番上の階層（ルート）のフォルダID
);
データの前提条件（業務ルール）:
-
1つのフォルダ（folder_id）は、必ず1人のユーザー（owner_user_id）によって作成・所有されます。
-
ユーザーID（owner_user_id）が決まれば、ユーザー名（owner_user_name）は一意に決まります。
-
階層のルール: 1つのフォルダは、**最大で1つの「親フォルダ（parent_folder_id）」**を持ちます。（親が複数いることはありません）
-
一番上の階層にあるフォルダ（誰の子でもないフォルダ）の場合、親フォルダは存在しないため、親を示す値は空（NULL）になります。
回答
-- 1. ユーザーテーブル (マスタ)
CREATE TABLE users (
user_id BIGSERIAL PRIMARY KEY,
user_name VARCHAR(100) NOT NULL
);
-- 2. フォルダテーブル (トランザクション 兼 自己参照)
CREATE TABLE folders (
folder_id BIGSERIAL PRIMARY KEY,
folder_name VARCHAR(100) NOT NULL,
owner_user_id BIGINT NOT NULL, -- フォルダには必ず所有者がいるのでNOT NULL
-- ★ここが最大のポイント：自分自身を参照するためのカラム
parent_folder_id BIGINT, -- ★ルートフォルダの場合はここが NULL になる
CONSTRAINT fk_folders_users
FOREIGN KEY (owner_user_id) REFERENCES users(user_id),
-- ★自己参照制約：親フォルダのIDも、このfoldersテーブルの中に存在する
CONSTRAINT fk_folders_parents
FOREIGN KEY (parent_folder_id) REFERENCES folders(folder_id)
ON DELETE CASCADE -- 親フォルダが削除されたら、その中身(子フォルダ)も連鎖的に削除される
);
-- 親フォルダIDで検索（このフォルダの中身一覧を取得）することが多いため、インデックスを張る
CREATE INDEX idx_folders_parent_id ON folders(parent_folder_id);