はじめに
前回は、業務の現状分析から要求定義・要件定義までを行いました。
前回の記事：Python × Dify × RAGで学ぶ業務システム開発入門【第2回 要求定義・要件定義編】
第3回では、定義した機能要件をテーブル構造（ER図）と画面設計に落とし込みます。
本連載で使用するソースコードは、以下のGitHubリポジトリで公開しています。
コードを書き始める前に設計を固めることには、明確な理由があります。
ER図を先に設計する理由
→ テーブル構造が決まれば、必要なデータとSQLが見える
→ 後からカラムを変更すると、既存コードへの影響が大きい
画面設計を先に行う理由
→ 画面が決まれば、必要なURLとデータの流れが見える
→ 実装途中で画面構成や操作方法がぶれるのを防げる
本記事で学ぶこと
| 項目 | 内容 |
|---|---|
| ER図 | テーブル、カラム、リレーションの整理 |
| 画面一覧 | 必要な画面、URL、HTTPメソッド、権限の整理 |
| 画面遷移図 | 画面間の移動ルールの整理 |
| ワイヤーフレーム | 主要画面のレイアウト設計 |
ER図とは何か
ER図（Entity-Relationship Diagram）とは、データベースのテーブル構造と関係性を図で表したものです。
Entity（エンティティ） → テーブル
Relationship（リレーション） → テーブル間の関係
Attribute（属性） → テーブルのカラム
ER図を読み書きできるようになると、次のような判断をしやすくなります。
- データをどのテーブルへ保存するか
- どのテーブル同士をJOINするか
- どこに外部キーを配置するか
- 1対多や多対多の関係をどのように表現するか
テーブル設計
第2回で整理した機能要件をもとに、必要なテーブルを設計します。
テーブル一覧
本システムでは、次の8テーブルを使用します。
| テーブル名 | 概要 |
|---|---|
user |
ログインユーザー名、ハッシュ化済みパスワード、ロールを管理する |
department |
人事部・開発部・営業部などの部署名を管理する |
employee |
社員番号、氏名、部署、役職、入社日などを管理する |
training |
研修名、開始日、終了日、研修内容を管理する |
training_enrollment |
社員と研修の申込関係、受講状態を管理する |
training_history |
出席率、理解度、課題スコアなどの研修実績を管理する |
evaluation |
社員の総合スコアとAI評価コメントを履歴として管理する |
daily_report |
社員の日報の日付、作業内容、メモを管理する |
departmentとtraining_enrollmentはデータベースに作成されますが、現在の画面・サービス処理では十分に利用されていません。
-
employee.departmentには部署名を文字列として直接保存しており、department.idへの外部キーは設定されていません。 - 研修履歴画面には有効な社員がすべて表示され、
training_enrollmentを使った受講者の絞り込みは行っていません。
各テーブルの詳細設計
user（ログインユーザー）
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id |
INTEGER | PK / AUTOINCREMENT | 主キー |
username |
TEXT | NOT NULL / UNIQUE | ログイン時に使用するユーザー名 |
password |
TEXT | NOT NULL | ハッシュ化済みパスワード |
role |
TEXT | NOT NULL / DEFAULT 'user'
|
権限（admin / staff / user） |
created_at |
DATETIME | DEFAULT CURRENT_TIMESTAMP | 作成日時 |
passwordカラムには平文のパスワードを保存しません。werkzeug.security.generate_password_hash()でハッシュ化してから保存します。
department（部署マスタ）
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id |
INTEGER | PK / AUTOINCREMENT | 主キー |
dept_name |
TEXT | NOT NULL / UNIQUE | 部署名 |
created_at |
DATETIME | DEFAULT CURRENT_TIMESTAMP | 作成日時 |
初期データとして、人事部・開発部・営業部が登録されます。ただし、部署を登録・編集・削除する画面は実装されていません。
employee（社員マスタ）
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id |
INTEGER | PK / AUTOINCREMENT | 主キー |
employee_no |
TEXT | NOT NULL / UNIQUE | 社員番号 |
name |
TEXT | NOT NULL | 氏名 |
department |
TEXT | 部署名 | |
position |
TEXT | 役職 | |
hire_date |
DATE | 入社日 | |
is_active |
INTEGER | NOT NULL / DEFAULT 1 | 有効フラグ |
created_at |
DATETIME | DEFAULT CURRENT_TIMESTAMP | 作成日時 |
updated_at |
DATETIME | DEFAULT CURRENT_TIMESTAMP | 更新日時 |
社員の削除ではレコードを物理的に削除せず、is_activeを0へ変更します。これにより、社員一覧には表示しない一方で、既存の研修履歴や評価データを残せます。
training（研修マスタ）
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id |
INTEGER | PK / AUTOINCREMENT | 主キー |
training_name |
TEXT | NOT NULL | 研修名 |
start_date |
DATE | 開始日 | |
end_date |
DATE | 終了日 | |
description |
TEXT | 研修内容 | |
created_at |
DATETIME | DEFAULT CURRENT_TIMESTAMP | 作成日時 |
training_enrollment（研修申込）
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id |
INTEGER | PK / AUTOINCREMENT | 主キー |
employee_id |
INTEGER | NOT NULL / FK → employee.id
|
社員ID |
training_id |
INTEGER | NOT NULL / FK → training.id
|
研修ID |
status |
TEXT | DEFAULT 'enrolled'
|
受講状態 |
created_at |
DATETIME | DEFAULT CURRENT_TIMESTAMP | 作成日時 |
employee_idとtraining_idの組み合わせにはUNIQUE制約があり、同じ社員と研修の組み合わせが重複して登録されるのを防ぎます。
現在は、training_enrollmentへ登録する画面やサービス処理は実装されていません。今後、研修ごとの受講者を選択する機能を追加するときに利用できます。
training_history（研修受講履歴）
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id |
INTEGER | PK / AUTOINCREMENT | 主キー |
employee_id |
INTEGER | NOT NULL / FK → employee.id
|
社員ID |
training_id |
INTEGER | NOT NULL / FK → training.id
|
研修ID |
attendance_rate |
REAL | DEFAULT 0.0 | 出席率 |
understanding_level |
INTEGER | DEFAULT 0 | 理解度スコア |
report_score |
INTEGER | DEFAULT 0 | 課題・レポートスコア |
created_at |
DATETIME | DEFAULT CURRENT_TIMESTAMP | 作成日時 |
employee_idとtraining_idの組み合わせにはUNIQUE制約があります。研修履歴の保存時には、組み合わせが存在しなければINSERTし、存在する場合はUPDATEします。
出席率は日別の出欠データから自動計算するのではなく、研修履歴画面またはExcel・CSVファイルから数値として登録します。
evaluation（評価）
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id |
INTEGER | PK / AUTOINCREMENT | 主キー |
employee_id |
INTEGER | NOT NULL / FK → employee.id
|
評価対象の社員ID |
score |
REAL | 総合スコア | |
ai_comment |
TEXT | AI評価コメント | |
created_at |
DATETIME | DEFAULT CURRENT_TIMESTAMP | 評価日時 |
社員ごとに複数の評価を保存できます。評価一覧では、各社員の最新評価を表示します。
総合スコアは、社員ごとの研修実績の平均値から次の式で計算します。
総合スコア
＝ 出席率 × 30%
＋ 理解度 × 40%
＋ 課題スコア × 30%
daily_report（日報）
| カラム名 | 型 | 制約 | 説明 |
|---|---|---|---|
id |
INTEGER | PK / AUTOINCREMENT | 主キー |
employee_id |
INTEGER | NOT NULL / FK → employee.id
|
対象社員ID |
report_date |
DATE | NOT NULL | 日報の日付 |
content |
TEXT | 作業内容 | |
memo |
TEXT | 所感・メモ | |
created_at |
DATETIME | DEFAULT CURRENT_TIMESTAMP | 作成日時 |
通常のadmin・staff・userアカウントでは、日報画面で社員を選択して登録済みの日報を一覧表示します。一方、社員番号と同じユーザー名でログインした社員アカウントでは、ログイン中の社員IDが自動設定され、本人の日報だけを登録・表示します。
ER図（全体）
実装されている外部キー関係をER図で表すと、次のようになります。
USERとDEPARTMENTは、現在の実装ではほかのテーブルと外部キーで接続されていません。ただし認証時には、user.usernameとemployee.employee_noを動的に照合します。一致する有効な社員が見つかった場合は、その社員IDと氏名をセッションへ保存し、社員専用マイページを表示します。
たとえば初期ユーザーEMP001は、社員番号EMP001の「山田 太郎」と照合されます。これはデータベース上の外部キーではなく、認証処理内のLEFT JOINによる論理的な関連付けです。
リレーションの読み方
| 関係 | 説明 |
|---|---|
employee 1 → 多 training_enrollment
|
1人の社員が複数の研修へ申し込める |
training 1 → 多 training_enrollment
|
1件の研修に複数の社員が申し込める |
employee 1 → 多 training_history
|
1人の社員が複数の研修履歴を持てる |
training 1 → 多 training_history
|
1件の研修に複数社員の実績を保存できる |
employee 1 → 多 evaluation
|
1人の社員について複数回の評価を保存できる |
employee 1 → 多 daily_report
|
1人の社員が複数の日報を持てる |
画面設計
テーブル設計が終わったら、次は画面設計です。「どのような画面が必要か」「画面間をどのように移動するか」を整理します。
画面一覧
| 画面名 | URL | HTTPメソッド | 主な操作 | 権限 |
|---|---|---|---|---|
| ログイン | /login |
GET / POST | ユーザー名・パスワードによる認証 | 未ログインユーザー |
| ログアウト | /logout |
GET | セッションを破棄してログイン画面へ移動 | 全ロール |
| ダッシュボード | / |
GET | 利用可能な機能への移動。社員連携済みの場合はマイページへ移動 | 全ロール |
| 社員マイページ | /my-page |
GET | 本人の社員情報、研修履歴、最新評価、直近の日報を確認 | 社員情報と連携したログインユーザー |
| 社員一覧 | /employees |
GET | 一覧表示、社員番号・氏名・部署による検索 |
staff / admin
|
| 社員登録 | /employees/new |
GET / POST | 社員情報の登録 |
staff / admin
|
| 社員編集 | /employees/<int:employee_id>/edit |
GET / POST | 社員情報の編集 |
staff / admin
|
| 社員削除 | /employees/<int:employee_id>/delete |
POST | 社員の論理削除 | admin |
| 研修一覧 | /trainings |
GET | 研修一覧の表示 |
staff / admin
|
| 研修登録 | /trainings/new |
GET / POST | 研修情報の登録 |
staff / admin
|
| 研修履歴 | /trainings/<int:training_id>/history |
GET / POST | 出席率・理解度・課題スコアの入力 |
staff / admin
|
| 評価管理 | /evaluations |
GET | 集計値、総合スコア、AIコメントの表示 |
staff / admin
|
| AIコメント生成 | /evaluations/<int:employee_id>/generate |
POST | 生成中UIを表示し、AI評価コメントを生成・保存 |
staff / admin
|
| Excel取込 | /import |
GET / POST | 社員台帳・研修結果の取込 |
staff / admin
|
| Excel出力 | /export |
GET | 評価一覧のExcelダウンロード |
staff / admin
|
| 日報管理 | /reports |
GET / POST | 日報の登録・一覧表示。社員連携済みユーザーは本人分だけを扱う | 全ロール |
| 社内文書検索 | /search |
GET / POST | Difyを利用した社内文書検索 | 全ロール |
| FAQチャット | /chat |
GET | チャット画面の表示 | 全ロール |
| FAQメッセージ送信API | /chat/send |
POST | 質問の送信とJSON形式の回答取得 | 全ロール |
/reports/newというURLも定義されていますが、日報登録画面を個別に表示するのではなく、/reportsへリダイレクトします。日報の登録は/reportsへのPOSTで行います。
画面遷移図
画面間の主な移動ルールを整理します。
[ログイン]
├── admin / staff / user ──> [ダッシュボード]
└── 社員番号アカウント（例：EMP001） ──> [社員マイページ]
[ダッシュボード]
├── [社員一覧]
│ ├── [社員登録] ── 登録完了 ──> [社員一覧]
│ ├── [社員編集] ── 更新完了 ──> [社員一覧]
│ └── 社員削除 ── 削除完了 ──> [社員一覧]
│
├── [研修一覧]
│ ├── [研修登録] ── 登録完了 ──> [研修一覧]
│ └── [研修履歴] ── 保存完了 ──> [研修履歴]
│
├── [評価管理]
│ ├── AIコメント生成 ── 生成中表示 ──> [評価管理]
│ └── Excel出力 ──> ファイルダウンロード
│
├── [Excel取込] ── 取込完了 ──> [Excel取込]
├── [日報管理] ── 登録完了 ──> [日報管理]
├── [社内文書検索] ──> 同じ画面に検索結果を表示
└── [FAQチャット] ──> 同じ画面に質問と回答を追加
各画面のナビゲーション
└── ログアウト ──> [ログイン]
[社員マイページ]
├── 本人の社員情報・研修履歴・最新評価・直近5件の日報を確認
├── [日報管理] ── 本人の日報だけを登録・表示
├── [社内文書検索]
└── [FAQチャット]
userロールのダッシュボードには、社員管理・研修管理・評価管理・Excel入出力は表示されません。これらのURLへ直接アクセスした場合も、roles_required()によってアクセスが拒否されます。
初期ユーザーEMP001（パスワード：password）もロールはuserですが、ユーザー名が社員番号と一致するため、ログイン後は/my-pageへ移動します。/へアクセスした場合も社員マイページへリダイレクトされます。
ワイヤーフレーム（主要画面）
実際の開発では、Figmaなどのツールを使用して画面レイアウトを設計する方法もあります。本記事では、主要画面の完成イメージを確認します。
ダッシュボード
社員一覧
社員登録・編集フォーム
評価一覧・AIコメント生成
「AI生成」または「再生成」ボタンを押すと、押したボタンの表示が「生成中...」に切り替わり、画面中央にローディング表示が出ます。処理中はすべての生成ボタンを無効化し、Dify APIへの二重送信を防ぎます。生成が完了すると評価一覧へ戻り、保存されたコメントを表示します。
社員専用マイページ
社員番号アカウントでログインした社員向けの画面です。本人の基本情報、研修履歴、最新評価、直近5件の日報を1画面で確認できます。
社内文書検索（RAG）
設計をコードに反映する
ER図と画面設計が決まれば、実装の見通しが立ちます。設計と実装の対応関係を確認します。
テーブル設計 → database.py
次のコードでは、研修履歴テーブルに社員と研修への外部キーを設定しています。また、同じ社員と研修の組み合わせが重複しないようにUNIQUE制約を設定しています。
# database.py（抜粋）
CREATE TABLE IF NOT EXISTS training_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employee_id INTEGER NOT NULL REFERENCES employee(id),
training_id INTEGER NOT NULL REFERENCES training(id),
attendance_rate REAL DEFAULT 0.0,
understanding_level INTEGER DEFAULT 0,
report_score INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(employee_id, training_id)
);
画面設計 → app.pyのルーティング
URL、HTTPメソッド、パラメーター、権限をFlaskのルーティングへ反映します。
@app.route("/employees")
@login_required
@roles_required("staff", "admin")
def employee_list():
...
@app.route("/employees/<int:employee_id>/edit", methods=["GET", "POST"])
@login_required
@roles_required("staff", "admin")
def employee_edit(employee_id):
...
@app.route("/employees/<int:employee_id>/delete", methods=["POST"])
@login_required
@roles_required("admin")
def employee_delete(employee_id):
...
@app.route("/trainings/<int:training_id>/history", methods=["GET", "POST"])
@login_required
@roles_required("staff", "admin")
def training_history(training_id):
...
@app.route("/chat/send", methods=["POST"])
@login_required
def chat_send():
...
@app.route("/my-page")
@login_required
def employee_portal():
employee_id = session.get("employee_id")
if not employee_id:
return redirect(url_for("index"))
...
画面設計 → templates/のHTMLファイル
templates/
├── base.html ← 共通レイアウト・ナビゲーション
├── login.html ← ログイン画面
├── index.html ← ダッシュボード
├── employee_portal.html ← 社員専用マイページ
├── employee_list.html ← 社員一覧・検索
├── employee_form.html ← 社員登録・編集
├── training_list.html ← 研修一覧
├── training_form.html ← 研修登録
├── training_history.html ← 研修履歴入力
├── evaluation.html ← 評価一覧・AIコメント生成
├── import.html ← Excel・CSV取込
├── report.html ← 日報登録・一覧
├── search.html ← 社内文書検索
└── chat.html ← FAQチャット
現在の設計で今後改善できる点
今回のコードは、ローカルで業務システム開発を学ぶためのサンプルです。実務システムへ発展させる場合は、次の改善が考えられます。
| 改善項目 | 現在の実装 | 改善案 |
|---|---|---|
| 部署管理 | 社員テーブルに部署名を文字列で保存 |
department_idを追加して部署マスタと外部キーで接続する |
| 受講者管理 | 研修履歴画面に有効な社員をすべて表示 |
training_enrollmentを利用して研修ごとの受講者を管理する |
| 出席管理 | 出席率を数値で直接入力 | 日別の出欠データを保存し、出席率を自動計算する |
| ユーザーと社員の関連付け |
usernameとemployee_noを認証時に照合 |
employee_idを外部キーとして保持し、明示的に関連付ける |
| 日報の権限制御 | 社員連携済みユーザーは本人分のみ、その他のアカウントは全社員分を扱う | 担当者の部署・担当範囲に応じた閲覧制御を追加する |
| RAGの参照元 | Difyの回答本文のみを表示 | 文書名、章、ページ、スコアなどを表示する |
| FAQの応答方式 |
blockingモード |
ストリーミング応答に対応する |
おわりに
第3回では、ER図と画面設計を通じて、実装の土台を整理しました。
ポイントを振り返ります。
- テーブル設計：8テーブルのカラム、制約、役割を整理した
- ER図：実装済みの外部キー関係と、独立しているテーブルを確認した
- 画面一覧：画面、URL、HTTPメソッド、ロール別権限を整理した
- 画面遷移図：通常ユーザーと社員番号アカウントで異なるログイン後の遷移を整理した
- ワイヤーフレーム：主要画面のレイアウトを確認した
- 操作中のUI：AIコメント生成中の表示と二重送信防止を確認した
-
実装との対応：
database.py、app.py、templates/との関係を確認した
次回は**「Flaskログイン機能編」**として、ログイン・ログアウト、セッション管理、パスワードのハッシュ化、ロール別アクセス制御を解説します。
次回：Python × Dify × RAGで学ぶ業務システム開発入門【第4回 Flaskログイン機能編】
本連載について
本連載の各記事で実装する内容は次のとおりです。
| 回 | タイトル | 対応画面 | 主な内容 |
|---|---|---|---|
| 第1回 | 業務システム全体設計編 | ― | システム概要・技術選定・アーキテクチャ |
| 第2回 | 要求定義・要件定義編 | ― | 業務分析・機能要件・非機能要件 |
| 第3回 | ER図・画面設計編 | ― | テーブル設計・画面遷移図・ワイヤーフレーム |
| 第4回 | Flaskログイン機能編 | 🔐 ログイン | セッション・ハッシュ・ロール認可・社員番号ログイン |
| 第5回 | 社員管理CRUD編 | 👤 社員管理 | 一覧・検索・登録・更新・論理削除・入力制御 |
| 第6回 | 研修管理編 | 📚 研修管理 | 研修登録・LEFT JOIN・履歴一括入力・UPSERT |
| 第7回 | Excel業務自動化編 | 📥 取込 / 📤 出力 | pandas取込・openpyxl出力 |
| 第8回 | Dify API連携編 | 📊 評価管理 | API・フォールバック・生成中UI・二重送信防止 |
| 第9回 | RAG構築編 | 🔍 文書検索 | Dify Chat API・質問送信・回答表示・エラー処理 |
| 第10回 | FAQチャットボット編 | 💬 FAQチャット | 会話ID・Fetch API・生成中表示・二重送信防止 |
| 公開編 | Ubuntu VPS・Docker公開編 | 🌐 サービス公開 | Ubuntu VPS・Docker・Nginxを使った本番環境構築とWebアプリケーション公開 |