論理削除と物理削除の続き　部品受注システムを Rust + SQL 文で解いてみる

投稿日: 2025/12/29 作成者: masuda

前回の 論理削除と物理削除の続き（工場の部品受注システムの例）https://www.moonmile.net/blog/archives/11888 では、論理削除と物理削除の違いであまり差が出て来なかったので、もう少し踏み込んでみます。いわゆる SQL 文を直接書いて編集する例を見ていきましょう。

結論から言えば、SQL 文を直接書くときには deleted_at カラムを使った論理削除は面倒くさいし結構不利です。「論理削除がアンチパターン」と称される所以がここにあります。ただし、現在においては ORM を使って deleted_at カラムをうまく隠蔽する方法があるので、ORM を使っている限りはあまり不便を感じません。そういう意味では「論理削除はアンチパターン」と断言してしまうのは、ちょっと過去の話かもしれません。

論理削除パターン

-- 部品マスター
CREATE TABLE parts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    part_number VARCHAR(64) UNIQUE NOT NULL,
    part_name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    lead_time_days INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);
 
-- 部品補助情報
CREATE TABLE part_details (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    part_id BIGINT NOT NULL,
    weight DECIMAL(10, 4) NULL,
    dimensions VARCHAR(255) NULL,
    material VARCHAR(128) NULL,
    storage_location VARCHAR(128) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (part_id) REFERENCES parts(id)
);
 
-- 部品の調達先
CREATE TABLE part_suppliers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    part_id BIGINT NOT NULL,
    supplier_id BIGINT NOT NULL,
    supplier_part_number VARCHAR(64) NULL,
    supplier_price DECIMAL(10, 2) NOT NULL,
    lead_time_days INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (part_id) REFERENCES parts(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);
 
-- 受注情報
CREATE TABLE purchase_orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    po_number VARCHAR(64) UNIQUE NOT NULL,
    supplier_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    expected_delivery_date DATE NOT NULL,
    actual_delivery_date DATE NULL,
    status ENUM('pending', 'ordered', 'received', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);
 
-- 部品生産管理
CREATE TABLE part_production (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    part_id BIGINT NOT NULL,
    production_date DATE NOT NULL,
    quantity_produced INT NOT NULL,
    quality_status ENUM('pass', 'fail', 'rework') DEFAULT 'pass',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (part_id) REFERENCES parts(id)
);
 
-- 部品の組み合わせテーブル（BOM）
CREATE TABLE bom_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    bom_id BIGINT NOT NULL,
    part_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    sequence INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (bom_id) REFERENCES bom(id),
    FOREIGN KEY (part_id) REFERENCES parts(id),
    UNIQUE KEY unique_bom_part (bom_id, part_id)
);
 
-- BOM マスター
CREATE TABLE bom (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_number VARCHAR(64) UNIQUE NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    version INT DEFAULT 1,
    effective_date DATE NOT NULL,
    status ENUM('active', 'obsolete') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);
 
-- 受注組み合わせ番号管理
CREATE TABLE customer_orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(64) UNIQUE NOT NULL,
    customer_id BIGINT NOT NULL,
    bom_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    delivery_date DATE NOT NULL,
    quantity INT NOT NULL,
    status ENUM('pending', 'in_production', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (bom_id) REFERENCES bom(id)
);
 
-- 顧客発注履歴
CREATE TABLE order_history (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_order_id BIGINT NOT NULL,
    status_changed_to VARCHAR(64) NOT NULL,
    changed_at TIMESTAMP NOT NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_order_id) REFERENCES customer_orders(id)
);
 
-- 顧客マスター
CREATE TABLE customers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_code VARCHAR(64) UNIQUE NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(128) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 
-- サプライヤーマスター
CREATE TABLE suppliers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    supplier_code VARCHAR(64) UNIQUE NOT NULL,
    supplier_name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(128) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

物理削除パターン

-- 部品マスター（物理削除）
CREATE TABLE parts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    part_number VARCHAR(64) UNIQUE NOT NULL,
    part_name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    lead_time_days INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 部品マスター履歴
CREATE TABLE parts_history (
    history_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    part_id BIGINT NOT NULL,
    part_number VARCHAR(64) NOT NULL,
    part_name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    lead_time_days INT DEFAULT 0,
    version INT NOT NULL,
    effective_from TIMESTAMP NOT NULL,
    effective_to TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (part_id) REFERENCES parts(id)
);

-- BOM マスター（物理削除）
CREATE TABLE bom (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_number VARCHAR(64) UNIQUE NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    version INT DEFAULT 1,
    effective_date DATE NOT NULL,
    status ENUM('active', 'obsolete') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- BOM マスター履歴
CREATE TABLE bom_history (
    history_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    bom_id BIGINT NOT NULL,
    product_number VARCHAR(64) NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    version INT NOT NULL,
    effective_date DATE NOT NULL,
    status ENUM('active', 'obsolete') DEFAULT 'active',
    effective_from TIMESTAMP NOT NULL,
    effective_to TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bom_id) REFERENCES bom(id)
);

-- 部品補助情報（物理削除）
CREATE TABLE part_details (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    part_id BIGINT NOT NULL,
    weight DECIMAL(10, 4) NULL,
    dimensions VARCHAR(255) NULL,
    material VARCHAR(128) NULL,
    storage_location VARCHAR(128) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (part_id) REFERENCES parts(id)
);

-- 部品の調達先（物理削除）
CREATE TABLE part_suppliers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    part_id BIGINT NOT NULL,
    supplier_id BIGINT NOT NULL,
    supplier_part_number VARCHAR(64) NULL,
    supplier_price DECIMAL(10, 2) NOT NULL,
    lead_time_days INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (part_id) REFERENCES parts(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

-- 受注情報（物理削除）
CREATE TABLE purchase_orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    po_number VARCHAR(64) UNIQUE NOT NULL,
    supplier_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    expected_delivery_date DATE NOT NULL,
    actual_delivery_date DATE NULL,
    status ENUM('pending', 'ordered', 'received', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

-- 部品生産管理（物理削除）
CREATE TABLE part_production (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    part_id BIGINT NOT NULL,
    production_date DATE NOT NULL,
    quantity_produced INT NOT NULL,
    quality_status ENUM('pass', 'fail', 'rework') DEFAULT 'pass',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (part_id) REFERENCES parts(id)
);

-- 部品の組み合わせテーブル（BOM）（物理削除）
CREATE TABLE bom_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    bom_id BIGINT NOT NULL,
    part_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    sequence INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (bom_id) REFERENCES bom(id),
    FOREIGN KEY (part_id) REFERENCES parts(id),
    UNIQUE KEY unique_bom_part (bom_id, part_id)
);

-- 受注組み合わせ番号管理（物理削除）
CREATE TABLE customer_orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(64) UNIQUE NOT NULL,
    customer_id BIGINT NOT NULL,
    bom_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    delivery_date DATE NOT NULL,
    quantity INT NOT NULL,
    status ENUM('pending', 'in_production', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (bom_id) REFERENCES bom(id)
);

-- 顧客発注履歴（物理削除）
CREATE TABLE order_history (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_order_id BIGINT NOT NULL,
    status_changed_to VARCHAR(64) NOT NULL,
    changed_at TIMESTAMP NOT NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_order_id) REFERENCES customer_orders(id)
);

-- 顧客マスター（物理削除）
CREATE TABLE customers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_code VARCHAR(64) UNIQUE NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(128) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- サプライヤーマスター（物理削除）
CREATE TABLE suppliers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    supplier_code VARCHAR(64) UNIQUE NOT NULL,
    supplier_name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(128) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

これをベースにして、Rust を使って SQL 文を直接書くコードを生成してみましょう。幸い？にして Rust では softdelete 系のライブラリが存在しないので、この手のコードは SQL 文を直接書くしかありません。まあ、探せば ORM があるんでしょうが、ここでは悪手のために犠牲になってもらいましょう。

ケース1

顧客から古い発注書に基づいて部品の発注を受けた。残念ながら過去の発注のために、発注書の中にある、部品番号は現在扱っていない。この部品番号を新しくして見積もり書を作成したい。

このときに、過去の発注書と合計金額を照合して、差分を計算しておきたい。

論理削除パターン

use sqlx::{MySql, Pool, Row};
use sqlx::mysql::MySqlPoolOptions;
use decimal::Decimal;

#[derive(Debug, Clone)]
struct OrderItem {
    part_number: String,
    part_name: String,
    quantity: i32,
    unit_price: Decimal,
    total_price: Decimal,
}

#[derive(Debug)]
struct OrderComparison {
    old_items: Vec<OrderItem>,
    new_items: Vec<OrderItem>,
    old_total: Decimal,
    new_total: Decimal,
    difference: Decimal,
}

async fn compare_orders_with_replacement(
    pool: &Pool<MySql>,
    old_bom_id: i64,
    old_part_id_b: i64,
    new_part_id_d: i64,
) -> Result<OrderComparison, sqlx::Error> {
    // 古い発注書から部品情報を取得
    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(
        "SELECT p.part_number, p.part_name, bi.quantity, p.unit_price
         FROM bom_items bi
         JOIN parts p ON bi.part_id = p.id
         WHERE bi.bom_id = ? 
         ORDER BY bi.sequence"
    )
    .bind(old_bom_id)
    .fetch_all(pool)
    .await?
    .into_iter()
    .map(|(part_number, part_name, quantity, unit_price)| {
        let total_price = unit_price * Decimal::from(quantity);
        OrderItem {
            part_number,
            part_name,
            quantity,
            unit_price,
            total_price,
        }
    })
    .collect();

    // 新しい発注書用に部品Bを部品Dに置き換え
    let mut new_items = old_items.clone();
    for item in &mut new_items {
        if item.part_number == "B" {
            // 部品Dの情報を取得
            let new_part: (String, String, Decimal) = sqlx::query_as(
                "SELECT part_number, part_name, unit_price FROM parts WHERE id = ? AND deleted_at IS NULL"
            )
            .bind(new_part_id_d)
            .fetch_one(pool)
            .await?;

            item.part_number = new_part.0;
            item.part_name = new_part.1;
            item.unit_price = new_part.2;
            item.total_price = new_part.2 * Decimal::from(item.quantity);
        }
    }

    let old_total: Decimal = old_items.iter().map(|i| i.total_price).sum();
    let new_total: Decimal = new_items.iter().map(|i| i.total_price).sum();
    let difference = new_total - old_total;

    Ok(OrderComparison {
        old_items,
        new_items,
        old_total,
        new_total,
        difference,
    })
}

※ 2025/12/30 追記
最初に AI に作成して貰ったときにここのSQL文には deleted_at IS NULL が付いていました。この場合、”古い部品調達まで調べる” ことが必要なので deleted_at の状態に関係なく＝新旧関係なくデータを検索しなければいけません。この部分 AI でも間違えるので注意が必要です。

物理削除パターン

use sqlx::{MySql, Pool, Row};
use sqlx::mysql::MySqlPoolOptions;
use decimal::Decimal;

#[derive(Debug, Clone)]
struct OrderItem {
    part_number: String,
    part_name: String,
    quantity: i32,
    unit_price: Decimal,
    total_price: Decimal,
}

#[derive(Debug)]
struct OrderComparison {
    old_items: Vec<OrderItem>,
    new_items: Vec<OrderItem>,
    old_total: Decimal,
    new_total: Decimal,
    difference: Decimal,
}

async fn compare_orders_with_replacement(
    pool: &Pool<MySql>,
    old_bom_id: i64,
    old_part_number_b: &str,
    new_part_id_d: i64,
) -> Result<OrderComparison, sqlx::Error> {
    // 古い発注書から部品情報を取得。parts に存在しない場合は履歴テーブルの最新版を使用する
    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(
        r#"
        WITH latest_history AS (
            SELECT ph.*, ROW_NUMBER() OVER (PARTITION BY ph.part_id ORDER BY ph.effective_from DESC) AS rn
            FROM parts_history ph
        )
        SELECT
            COALESCE(p.part_number, lh.part_number) AS part_number,
            COALESCE(p.part_name, lh.part_name) AS part_name,
            bi.quantity,
            COALESCE(p.unit_price, lh.unit_price) AS unit_price
        FROM bom_items bi
        LEFT JOIN parts p ON p.id = bi.part_id
        LEFT JOIN latest_history lh ON lh.part_id = bi.part_id AND lh.rn = 1
        WHERE bi.bom_id = ?
        ORDER BY bi.sequence
        "#
    )
    .bind(old_bom_id)
    .fetch_all(pool)
    .await?
    .into_iter()
    .map(|(part_number, part_name, quantity, unit_price)| {
        let total_price = unit_price * Decimal::from(quantity);
        OrderItem {
            part_number,
            part_name,
            quantity,
            unit_price,
            total_price,
        }
    })
    .collect();

    // 新しい発注書用に部品Bを部品Dに置き換え
    let mut new_items = old_items.clone();
    for item in &mut new_items {
        if item.part_number == old_part_number_b {
            let new_part: (String, String, Decimal) = sqlx::query_as(
                "SELECT part_number, part_name, unit_price FROM parts WHERE id = ?"
            )
            .bind(new_part_id_d)
            .fetch_one(pool)
            .await?;

            item.part_number = new_part.0;
            item.part_name = new_part.1;
            item.unit_price = new_part.2;
            item.total_price = new_part.2 * Decimal::from(item.quantity);
        }
    }

    let old_total: Decimal = old_items.iter().map(|i| i.total_price).sum();
    let new_total: Decimal = new_items.iter().map(|i| i.total_price).sum();
    let difference = new_total - old_total;

    Ok(OrderComparison {
        old_items,
        new_items,
        old_total,
        new_total,
        difference,
    })
}

さて、2つの SQL でどちらが複雑でしょうか？

    // 古い発注書から部品情報を取得
    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(
        "SELECT p.part_number, p.part_name, bi.quantity, p.unit_price
         FROM bom_items bi
         JOIN parts p ON bi.part_id = p.id
         WHERE bi.bom_id = ? 
         ORDER BY bi.sequence"
    )


    // 古い発注書から部品情報を取得。parts に存在しない場合は履歴テーブルの最新版を使用する
    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(
        r#"
        WITH latest_history AS (
            SELECT ph.*, ROW_NUMBER() OVER (PARTITION BY ph.part_id ORDER BY ph.effective_from DESC) AS rn
            FROM parts_history ph
        )
        SELECT
            COALESCE(p.part_number, lh.part_number) AS part_number,
            COALESCE(p.part_name, lh.part_name) AS part_name,
            bi.quantity,
            COALESCE(p.unit_price, lh.unit_price) AS unit_price
        FROM bom_items bi
        LEFT JOIN parts p ON p.id = bi.part_id
        LEFT JOIN latest_history lh ON lh.part_id = bi.part_id AND lh.rn = 1
        WHERE bi.bom_id = ?
        ORDER BY bi.sequence
        "#
    )

見てわかる通り、物理削除パターンのほうが複雑です。まあ、これはあまりフェアではなくて、古いデータを取得しようとしたときには物理削除パターンのほうが手がかかるのは当たり前のことです。

これを通常の現状あるパーツから検索する SQL 文に置き換えると

論理削除パターン

    // 通常の発注書から部品情報を取得
    let items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(
        "SELECT p.part_number, p.part_name, bi.quantity, p.unit_price
         FROM bom_items bi
         JOIN parts p ON bi.part_id = p.id
         WHERE bi.bom_id = ? AND p.deleted_at IS NULL
         ORDER BY bi.sequence"
    )

物理削除パターン

    // 通常発注書から部品情報を取得
    let old_items: Vec<OrderItem> = sqlx::query_as::<_, (String, String, i32, Decimal)>(
        "SELECT p.part_number, p.part_name, bi.quantity, p.unit_price
         FROM bom_items bi
         JOIN parts p ON bi.part_id = p.id
         WHERE bi.bom_id = ?
         ORDER BY bi.sequence"
    )

となるので、論理削除の場合は必ず deleted_at IS NULL の条件を付ける必要があります。これは論理削除の場合は弊害ですよね。
deleted_at IS NULL を付けるのを忘れると大変なことになります。逆に言えば、AI エージェントなどを使った場合には AI は忘れないので問題が出ないし、ORM を使う場合も問題が出ません。

たまに、データ量が多くなると deleted_at IS NULL の条件がパフォーマンスに影響を与えることがあるという記事がありますが、それはそもそもテーブル設計が悪いです。その場合は、逆にログやジャーナル用のテーブルから検索用のテーブルと現在用のテーブルに分けるという設計にすべきでしょう。
このあたり、架空の議論に陥りがちなので、このように具体例を示しています。

ケース2

もうひとつケースを考えてみましょう。

客先から発注書を受けたが、発注書に記載されている部品番号をシステムで検索すると出て来ない。どうやら、以前の見積もりを出した時には、調達可能だったもので組み合わせ番号を作成していたのだが、その後に廃盤になってしまったらしい。組み合わせ番号の整備が追い付かず、部品の組み合わせのほうが有効になっているが、部品番号を調べると画面に出て来ないという状況になってしまっている。

発注書に組み合わせ番号が書いてあったときに、その組み合わせに含まれる部品番号が有効であるかどうかのチェックを行いたい。1回目のチェックでは、現状調達できる部品だけでチェックをするが、2回目のチェックでは、履歴テーブルも参照して、過去に存在した部品番号も含めてチェックを行いたい。
1回目と2回目のチェックのメソッドを作成して欲しい。
入力は customer_orders.order_number としたい。

論理削除パターン

use sqlx::{mysql::MySqlPoolOptions, MySql, Pool, Row};

#[derive(Debug)]
struct PartCheck {
    part_number: String,
    part_name: Option<String>,
    quantity: i32,
    status: String, // "available" | "deleted" | "missing"
}

// 現在調達可能な部品だけを見る
async fn check_order_parts_current_logical(
    pool: &Pool<MySql>,
    order_number: &str,
) -> Result<Vec<PartCheck>, sqlx::Error> {
    let rows = sqlx::query(
        r#"
        SELECT bi.part_id,
               p.part_number,
               p.part_name,
               bi.quantity
        FROM customer_orders co
        JOIN bom_items bi ON bi.bom_id = co.bom_id
        JOIN parts p ON p.id = bi.part_id
        WHERE co.order_number = ?
          AND p.deleted_at IS NULL
        ORDER BY bi.sequence
        "#,
    )
    .bind(order_number)
    .fetch_all(pool)
    .await?;

    Ok(rows
        .into_iter()
        .map(|row| PartCheck {
            part_number: row.get("part_number"),
            part_name: Some(row.get("part_name")),
            quantity: row.get("quantity"),
            status: "available".to_string(),
        })
        .collect())
}

// 削除済みも含めて有効性を判定（論理削除なので履歴は無く、現行テーブルのdeleted_atで判断）
async fn check_order_parts_with_deleted_logical(
    pool: &Pool<MySql>,
    order_number: &str,
) -> Result<Vec<PartCheck>, sqlx::Error> {
    let rows = sqlx::query(
        r#"
        SELECT bi.part_id,
               p.part_number,
               p.part_name,
               bi.quantity,
               p.deleted_at IS NULL AS is_available,
               p.deleted_at IS NOT NULL AS is_deleted
        FROM customer_orders co
        JOIN bom_items bi ON bi.bom_id = co.bom_id
        LEFT JOIN parts p ON p.id = bi.part_id
        WHERE co.order_number = ?
        ORDER BY bi.sequence
        "#,
    )
    .bind(order_number)
    .fetch_all(pool)
    .await?;

    Ok(rows
        .into_iter()
        .map(|row| {
            let part_number: Option<String> = row.try_get("part_number").ok();
            let status = if part_number.is_none() {
                "missing"
            } else if row.get::<bool, _>("is_available") {
                "available"
            } else if row.get::<bool, _>("is_deleted") {
                "deleted"
            } else {
                "missing"
            };
            PartCheck {
                part_number: part_number.unwrap_or_else(|| "(none)".to_string()),
                part_name: row.try_get("part_name").ok(),
                quantity: row.get("quantity"),
                status: status.to_string(),
            }
        })
        .collect())
}

物理削除パターン

use sqlx::{mysql::MySqlPoolOptions, MySql, Pool, Row};

#[derive(Debug)]
struct PartCheck {
    part_number: String,
    part_name: Option<String>,
    quantity: i32,
    status: String, // "available" | "historical" | "missing"
}

// 現在調達可能な部品だけを見る（partsのみ）
async fn check_order_parts_current_physical(
    pool: &Pool<MySql>,
    order_number: &str,
) -> Result<Vec<PartCheck>, sqlx::Error> {
    let rows = sqlx::query(
        r#"
        SELECT bi.part_id,
               p.part_number,
               p.part_name,
               bi.quantity
        FROM customer_orders co
        JOIN bom_items bi ON bi.bom_id = co.bom_id
        JOIN parts p ON p.id = bi.part_id
        WHERE co.order_number = ?
        ORDER BY bi.sequence
        "#,
    )
    .bind(order_number)
    .fetch_all(pool)
    .await?;

    Ok(rows
        .into_iter()
        .map(|row| PartCheck {
            part_number: row.get("part_number"),
            part_name: Some(row.get("part_name")),
            quantity: row.get("quantity"),
            status: "available".to_string(),
        })
        .collect())
}

// 履歴も参照して有効性を判定（最新履歴を採用）
async fn check_order_parts_with_history_physical(
    pool: &Pool<MySql>,
    order_number: &str,
) -> Result<Vec<PartCheck>, sqlx::Error> {
    let rows = sqlx::query(
        r#"
        WITH latest_history AS (
            SELECT ph.*,
                   ROW_NUMBER() OVER (PARTITION BY ph.part_id ORDER BY ph.effective_from DESC) AS rn
            FROM parts_history ph
        )
        SELECT
            COALESCE(p.part_number, lh.part_number) AS part_number,
            COALESCE(p.part_name, lh.part_name)   AS part_name,
            bi.quantity,
            CASE
                WHEN p.id IS NOT NULL THEN 'available'
                WHEN lh.history_id IS NOT NULL THEN 'historical'
                ELSE 'missing'
            END AS status
        FROM customer_orders co
        JOIN bom_items bi ON bi.bom_id = co.bom_id
        LEFT JOIN parts p ON p.id = bi.part_id
        LEFT JOIN latest_history lh ON lh.part_id = bi.part_id AND lh.rn = 1
        WHERE co.order_number = ?
        ORDER BY bi.sequence
        "#,
    )
    .bind(order_number)
    .fetch_all(pool)
    .await?;

    Ok(rows
        .into_iter()
        .map(|row| PartCheck {
            part_number: row.get("part_number"),
            part_name: row.try_get("part_name").ok(),
            quantity: row.get("quantity"),
            status: row.get("status"),
        })
        .collect())
}

見ての通り、過去のデータを調べる場合は、物理削除パターンのほうが SQL 文が複雑です。特に履歴テーブルを参照する場合には、最新の履歴を取得するためにウィンドウ関数を使う必要があり、SQL 文が大幅に複雑化します。
逆に言えば、過去のデータを調べない限り、物理削除のほうが有利というわけです。
つまりは、要件として「過去のデータを遡って調べることがあるのか？」をチェックしておけば、データ構造を決める上で論理削除にするか物理削除にするかという判断材料になります。
蛇足を言えば、「過去のデータを調べるか否か？」の条件を付けずに、テーブル構造の設計に「論理削除か物理削除か？」を持ち込むこと自体がアンチパターンという訳ですね。

余談ですが、複数テーブルを検索するときに is null チェックが各テーブルに付加されて面倒くさい/間違いが置きやすいという指摘がたびたびありますが、現実的にはそうはならないです。

select *
from A inner join B on A.id = B.a_id and B.deleted_at is null
     B inner join C on B.id = C.b_id and C.deleted_at is null
     C inner join D on C.id = D.c_id and D.deleted_at is null
where A.deleted_at is null;

一見すると、A,B,C,D の全てに is null の条件を付けないような感じがしますが、

select *
from A inner join B on A.id = B.a_id 
     B inner join C on B.id = C.b_id 
     C inner join D on C.id = D.c_id 
where A.deleted_at is null;

運用上は、A.deleted_at is null の条件だけで十分です。

これは SQL の結合の仕組みによるもので、A.deleted_at is null の条件で A とは関係ないレコードを、B,C,D から拾ってくることはないからです。逆に B.deleted_at is not null のレコードを参照していたら、それは運用上のミスか、画面設計でのガード掛かっていないためです。

このあたりも考察していくと、実は論理削除をするにしても、メインとなるテーブルだけに注目すればよいという結論です。

カテゴリー: 開発, データベース パーマリンク