論理削除以外の解決方法

Question

環境情報

MySQL 5.7系
アプリケーションはRuby on Railsを使用

DDL

自社システムのDDL（架空のものです）を以下に記載します

-- マスタ系テーブル
--------------------------------------------------------------------------------

-- プラン情報
CREATE TABLE `plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

-- 宿泊施設の部屋
CREATE TABLE `rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

-- 予約情報
CREATE TABLE `reservations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

-- 中間テーブル
--------------------------------------------------------------------------------

-- プラン情報-宿泊施設の部屋 中間テーブル
CREATE TABLE `plan_rooms` (
  `plan_id` int(11) NOT NULL,
  `room_id` int(11) NOT NULL,
  UNIQUE KEY `uidx_plan_rooms` (`plan_id`,`room_id`),
  CONSTRAINT `fk_02` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_03` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE
);

-- 予約-部屋中間テーブル
CREATE TABLE `reservation_rooms` (
  `reservation_id` int(11) NOT NULL,
  `room_id` int(11) NOT NULL,
  UNIQUE KEY `uidx_plan_rooms` (`reservation_id`,`room_id`),
  CONSTRAINT `fk_04` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_05` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE
);

前提条件

※架空の設定なので、〇〇ならばという仮定は不可能

登場人物

• 自社 販売業者から部屋・予約情報の提供を受け、プランを販売業者へ提供する
• 販売業者 プランを消費者へ販売して、宿泊施設の部屋・予約情報を自社へ提供する 数十社存在する

データソース

• プラン系テーブル(plans, plan_rooms)のデータは自社のシステムで作成・変更が行われる
• 部屋/予約系テーブル(rooms, reservations, reservation_rooms)のデータは販売業者からAPI経由で取り込まれる
  • 実際には取り込み先ごとにaaa_rooms, bbb_rooms, ccc_roomsと数十種類に分かれている

アプリケーション仕様

• 部屋のデータは販売業者システム上からある時点で取り込めなくなる
• 販売業者システム上から取り込めなくなった部屋は自社システム上では表示してはならない
• 自社システム上ではプラン-部屋テーブルをもとに、プラン詳細画面で部屋情報を表示する必要がある(自社システム上では部屋テーブルは物理削除できない)

質問

部屋が販売業者システム上で物理削除された場合に、roomsテーブル（またはここに書かれていない新規のテーブルでも可）でどのように表現するべきか。

roomsはplan_rooms, reservation_roomsからの外部キーを持つため物理削除はできません。
そのため単純に考えるなら、rooms.is_deletedを設けてWHERE文で除外するという案があると思います。

しかし参考文献に記載した例では「論理削除」を避けるべきという意見をよく見かけました。
このパターンの場合に「論理削除（販売業者から取り込めなくなったフラグ）」を使用しない対応方法を教えていただけませんでしょうか。

参考文献

• 論理削除が云々について
• 論理削除はなぜ「筋が悪い」か
• DELETE_FLAG を付ける前に確認したいこと。
• SQLアンチパターン 幻の第26章「とりあえず削除フラグ」

Comments

[yambejp]

テーブル構造だけでなくサンプルデータも提示して具体的な挙動を特定して下さい

[退会済みユーザー]

実際は宿泊に関するシステムではなく、架空のテーブルを設けましたが余計にややこしくなってしまったようです。
以下に質問内容を簡潔にまとめます

- 他システムからデータをAPIで取り込んで、自社システムのDBに保存している
- 保存されたデータは他のテーブルへの外部キーも持ち、JOINして使用されてる（= 自社システムではそのデータを業務仕様上削除できない）
- 他システムではそのデータは物理削除され、APIに含まれなくなることがある
- また削除されたデータは自社システムでは表示してはいけない、そのため削除されたか否かを判別できるようにしなければならない
- 判別できるようにするために `is_deleted` のようなフラグを持つ以外の方法はあるのか

Answer 1

「論理削除（販売業者から取り込めなくなったフラグ）」を使用しない対応方法

自分の感覚では、「論理削除フラグ」と「販売業者から取り込めなくなったフラグ」は別物だと考えています。

上に挙がっている記事の中にありますが、

DELETE_FLAG 自体の是非よりもその手前の話として、論理削除 == DELETE_FLAG というパターンの一つ覚えで設計する多くの人は、単純にちゃんと考えられてない場合が多いので、少し掘り下げて聞いてみると良いと思っています。

ということで、自分が設計を行うなら「状態」として「有効な部屋」「一時的に無効な部屋（改装中、休業中など）」「もう復活しない部屋（取り壊されたなど）」というフィールドをもたせる形にすると思います。

「すべての変化を記録するようなジャーナル型」は、理論的にはきれいなのかもしれませんが、RDBMSの普通の使い方とは少し違ってきますので、

• 検索性能が出ない
• RailsのActtiveRecordに合わないので、自分でモデル部に大幅な手を入れる必要がある

など、相当な手間を要することが予想されます。

Comments

[退会済みユーザー]

maisumakun様、ご回答ありがとうございます。

以下にご回答いただいた内容に関する私の理解を記載いたします。

> 自分の感覚では、「論理削除フラグ」と「販売業者から取り込めなくなったフラグ」は別物だと考えています。
> ということで、自分が設計を行うなら「状態」として「有効な部屋」「一時的に無効な部屋（改装中、休業中など）」「もう復活しない部屋（取り壊されたなど）」というフィールドをもたせる形にすると思います。

"販売業者から取り込めなくなった = 販売業者側システムで物理削除された"としてもそれはあくまで別システムの仕様であり、こちらのシステムではそれを論理削除ではなく状態として表現できる。

また「テーブルに状態を持たせない」ほうが良いという意見も参考文献の中には見当たるが

> 「すべての変化を記録するようなジャーナル型」は、理論的にはきれいなのかもしれませんが、RDBMSの普通の使い方とは少し違ってきますので、
> 検索性能が出ない
> RailsのActtiveRecordに合わないので、自分でモデル部に大幅な手を入れる必要がある

検索性能とアプリケーションの仕様的にコストが高いので、このケースでは許容してもよいのでは。

というようなご指摘と受け取ったのですが、正しく理解できていますでしょうか。

Answer 2

参照制約をON DELETE SET NULLで定義するのはどうでしょう

13.1.17.2 外部キー制約の使用

ただし、NOT NULL制約は同時に指定できなくなります
またこれにより、ユニーク制約もNULLを含むものは重複が許容されるようになります

Comments

[退会済みユーザー]

SVC34様、ご回答ありがとうございます。

roomsへの参照制約をON DELETE SET NULLにすることで、roomsを物理削除できるようにするというご提案だと理解しました。

> 自社システム上ではプラン-部屋テーブルをもとに、プラン詳細画面で部屋情報を表示する必要がある(自社システム上では部屋テーブルは物理削除できない)

roomsの情報がなくなってしまうと上記要件を満たせなくなるため、この案は難しそうです。