論理削除と物理削除の続き

論理削除と物理削除の切り分けを具体例を示して思考実験する

論理削除と物理削除の切り分けを具体例を示して思考実験する

前記事の続きです。既にブーム？は去っているのですが（苦笑）、昔の原理原則にとらわれ過ぎると思考停止に陥ってしまうので、最近のフレームワークも含めて再考しましょう、ってのが主旨です（もちろん、この記事自体も 10 年も経てば古くなるわけですが）。

残りのモデルパターンを考えていきます。

回転寿司の受注システム
税務関係の社内経理システム、経費処理
社内のナレッジ管理、退職者の管理

これらのテーブルを眺めた後で、実際に SQL や ORM を使ってどのようになるのかを推測してみればよいわけです。

回転寿司の受注システム

これは新人研修用に作ったので、これをベースにします。

-- テーブル席
CREATE TABLE seats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_number INT NOT NULL,
    status ENUM('available', 'occupied') DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- テーブル注文
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    seat_id INT NOT NULL,
    order_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_seat_id (seat_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 注文履歴
CREATE TABLE order_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    seat_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_order_id (order_id),
    INDEX idx_seat_id (seat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 商品マスタ
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT,
    image_url VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- カテゴリ
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 商品カテゴリ
CREATE TABLE product_categories (
    product_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (product_id, category_id),
    INDEX idx_product_id (product_id),
    INDEX idx_category_id (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 商品在庫
CREATE TABLE product_stock (
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (product_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ちょっと新人研修用に作ったので deleted_at が入っていないのですが。これも BOM 表と同じパターンで、deleted_at を入れるパターンが考えられます。

商品マスター（寿司）の値段が変わった時には、products.price を更新することになるのですが、過去の注文履歴を残すために deleted_at カラムを入れるか、過去データのテーブルを用意するかしなくてはいけません。このときに、リアルタイムに価格の変更がかかるのか、それとも日次や週次の単位で価格の変更がかかるのか？によって、設計が違っています。
当たり前のようですが、1日の商売のうちに店内では価格の変更がされることはないでしょう。その場合には、店内の客が終わった後に精算処理を行うバッチで、過去の商品データに移したり、お客の履歴検索を調節したりすることが可能です。
しかし、WEB サイト予約のような（寿司の場合はあるとは思えないけど）、24時間運用で、リアルタイムに価格が切り替わる場合はどうでしょうか？　この場合には deleted_at カラムのほうがよさそうな気がします。タイミング的に、注文 → 精算の間に時間差があるので、注文時の価格を保存しておくか、精算時に価格がずれていた（該当する商品IDがマッチしなかった）ときを考慮する必要がでてきます。

いわゆる、時間差の有無、更新→参照のタイミングで差がでてくる場合、を考慮するか否か、がポイントになります。まあ、寿司注文の場合は、あまりリアルタイムに価格が変わることはないと思いますが。

税務関係の社内経理システム、経費処理

経費処理の場合はもう少し長いスパンで決済のタイミングが異なります。たとえば、同じ PC を買ったとしても購入時期により価格が変わったり消費税が変わったりすることはよくあります。
この経費精算を後から修正するパターンを考えてみましょう。社員が経費精算をするときに入力した値はなんらかの形で保持しておいて、後から経理部門や税理士が修正するパターンです。

物品購入テーブル
経費精算申請
経費精算承認フロー
経費精算の修正
会計仕分けテーブル
経費項目マスタ
消費税マスタ

-- 物品購入テーブル
CREATE TABLE purchases (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    purchase_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    tax_rate DECIMAL(5, 2) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_employee_id (employee_id),
    INDEX idx_purchase_date (purchase_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 経費精算申請
CREATE TABLE expense_claims (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    purchase_id INT NOT NULL,
    claimed_amount DECIMAL(10, 2) NOT NULL,
    expense_category_id INT NOT NULL,
    claim_date DATE NOT NULL,
    status ENUM('draft', 'submitted', 'approved', 'rejected') DEFAULT 'draft',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_employee_id (employee_id),
    INDEX idx_purchase_id (purchase_id),
    INDEX idx_status (status),
    FOREIGN KEY (purchase_id) REFERENCES purchases(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 経費精算承認フロー
CREATE TABLE expense_approvals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    expense_claim_id INT NOT NULL,
    approver_id INT NOT NULL,
    approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    comment TEXT,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_expense_claim_id (expense_claim_id),
    INDEX idx_approver_id (approver_id),
    FOREIGN KEY (expense_claim_id) REFERENCES expense_claims(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 経費精算の修正履歴
CREATE TABLE expense_revisions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    expense_claim_id INT NOT NULL,
    original_amount DECIMAL(10, 2) NOT NULL,
    revised_amount DECIMAL(10, 2) NOT NULL,
    reason TEXT,
    revised_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_expense_claim_id (expense_claim_id),
    FOREIGN KEY (expense_claim_id) REFERENCES expense_claims(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 会計仕分けテーブル
CREATE TABLE journal_entries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    expense_claim_id INT NOT NULL,
    account_code VARCHAR(20) NOT NULL,
    debit DECIMAL(10, 2) DEFAULT 0,
    credit DECIMAL(10, 2) DEFAULT 0,
    entry_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_expense_claim_id (expense_claim_id),
    INDEX idx_account_code (account_code),
    FOREIGN KEY (expense_claim_id) REFERENCES expense_claims(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 経費項目マスタ
CREATE TABLE expense_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    account_code VARCHAR(20),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 消費税マスタ
CREATE TABLE tax_rates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rate DECIMAL(5, 2) NOT NULL,
    effective_date DATE NOT NULL,
    expired_date DATE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

これも AI に作って貰ったパターンなので、 deleted_at カラムをチェックします。経費精算申請テーブルや経費項目マスタに入っていますね。

経費精算申請（expense_claims）は、申請した状態を status カラムで保持しているので、deleted_at カラムの意味はなんらかの形で “完全に削除する” という意味になります。申請自体は approved で完了となるので、approved の後に社内経理で削除にするのか、それとも draft の段階なのでデータ的に削除するのかという意味でしょう。
ここは経理システムの履歴に関するものなので、

厳格な会計システムとして履歴を重視したいときは、DELETE を禁止して、deleted_at で対応する
ある程度、柔軟に運用したいときは、deleted_at カラムを使わずに DELETE してしまう
操作履歴を残すために、トリガーを使って履歴テーブルに INSERT する

などの手段が考えられます。これは設計上で揺れるところかなと。

経費項目マスタ（expense_categories）の場合は、過去の id を参照する必要があるのでテーブル内に残しておいたほうが無難です。deleted_at カラムを意識する場合は、UI から選択するパターンなど限定的になるので間違いは起こりにくいかなと思われます。

社内のナレッジ管理、退職者の管理

典型的な社内SNSの運用を考えてみましょう。社内ナレッジでは掲示板を運営していて、投稿ログが表示されます。しかし、投稿者自身は退職する可能性があるので、退職者の情報（部署、権限など）をどうするのか？　という問題を考えます。もともと、部署は変わる可能性があるので、投稿権限や閲覧権限は時間を経ると変わっていきます。ただし、投稿記事や投稿ログ自体は、当時の記録として残しておきたいパターンです。

投稿記事テーブル
投稿者ログ
投稿記事のカテゴリ
投稿記事の閲覧権限
社員マスタ
部署マスタ

※ 投稿記事には、投稿した社員の当時の部署/投稿名が表示される
※ 社員の部署は変わる可能性がある。
※ 社員が退職する場合も考慮する。

-- 社員マスタ
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 部署マスタ
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 社員部署履歴
CREATE TABLE employee_departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    department_id INT NOT NULL,
    assigned_at DATE NOT NULL,
    unassigned_at DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_employee_id (employee_id),
    INDEX idx_department_id (department_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 投稿記事カテゴリ
CREATE TABLE post_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 投稿記事
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    category_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    posted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_employee_id (employee_id),
    INDEX idx_category_id (category_id),
    INDEX idx_posted_at (posted_at),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (category_id) REFERENCES post_categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 投稿ログ（投稿当時の社員情報スナップショット）
CREATE TABLE post_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    employee_id INT NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    department_name VARCHAR(100),
    posted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_post_id (post_id),
    INDEX idx_employee_id (employee_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 投稿記事の閲覧権限
CREATE TABLE post_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    department_id INT,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_post_id (post_id),
    INDEX idx_department_id (department_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

多分、この手の DB は定番だと思うのですが AI が作成すると deleted_at カラムが結構入っています。おそらく、React や Vue などを使って、自動生成するパターンが多いからでしょう。

興味深いのは投稿記事（posts）テーブルには deleted_at が入っていますが、投稿ログ（post_logs）テーブルには deleted_at が入っていません。基本的にログは消すことがない、むしろ消してはいけないので deleted_at はつけません。改変できないようにするためです。
投稿記事の閲覧権限（post_permissions）テーブルにも deleted_at は入っていません。このような補助テーブルは主となるテーブル（posts）に依存しているので、主テーブルが削除されれば（この場合は is_deleted フラグで論理削除ですが）、補助テーブルのほうは検索で自動的に除外されるためです。なので、補助テーブルには deleted_at は不要です。

このあたりから考えて、論理削除（deleted_at）と物理削除（DELETE）の切り分けには一定のルールがあります。ただし、投稿記事（posts）テーブルをどう扱うか、部署や権限などが変更する場合はどちらを採用するのか？　に関しては、利用する場面をよく考えたほうがよい、という指針になります。

これでひと通り、DDL と ER 図を示すことができたので、次回は具体的に SQL や ORM を使ったときの具体例を考えていきましょう。

日	月	火	水	木	金	土
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

回転寿司の受注システム

税務関係の社内経理システム、経費処理

社内のナレッジ管理、退職者の管理

最近の投稿

最近のコメント

menu

アーカイブ

カテゴリー

Blogroll

リファレンス

メタ情報