🙆

なぜステータスが混在するテーブル設計が生まれるのか

に公開
MySQL
SQL
設計
db設計
アンチパターン
tech

はじめに

業務システムのDBを読んでいると、一つのテーブルに複数のステータスカラムが同居しているケースをよく見かけます。

CREATE TABLE orders (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       INT UNSIGNED NOT NULL,
  order_status  TINYINT NOT NULL,   -- 1:受付 2:審査中 3:処理中 4:完了 5:停止
  payment_status TINYINT NOT NULL DEFAULT 1, -- 1:未請求 2:請求済 3:収納済 4:失敗
  external_status TINYINT,          -- 外部システムのステータス（1〜4, 9）
  external_code VARCHAR(2),         -- 外部機関ごとにコードが異なる
  processed_at  VARCHAR(8),         -- 処理日（YYYYMMDD）
  processed_time VARCHAR(6),        -- 処理時刻（HHMMSS）
  bank_code     VARCHAR(4),
  account_number VARCHAR(10),
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

これを最初に見たとき、「なぜこうなったのか」を考えることが大事です。
設計者が手抜きをしたわけでも、知識がなかったわけでもないことが多いです。
設計の順番と考え方の枠組みに問題があります。

この記事では、なぜこのような設計が生まれるのかを整理し、どうすれば防げるかを考えます。

なぜ生まれるのか

原因1：イベントとリソースを区別せずに設計している

最も根本的な原因は、「DBに保存するものはすべて物（リソース）だ」という暗黙の前提で設計を始めることです。

リソースとは、現在の状態を表す「物」です。
イベントとは、「何かが起きた」という事実の記録です。

受注を例に取ると、次のように分かれます。

分類 具体例
リソース 注文の基本情報、ユーザー情報、配送先
イベント 審査依頼を出した、外部システムへ送信した、決済が完了した、失敗した

ステータスとは、イベントの移り変わりを現在の視点から表したものです。
order_status が「受付→審査中→処理中→完了」と変化するのは、裏側で何らかのイベントが起きているからです。

この認識がないまま設計すると、「ステータスが変わる」という事象をUPDATEで上書きする問題として捉えます。
そしてステータスを「管理したいカラム」として一つのテーブルに追加し続けた結果、複数のステータスが混在します。

原因2：カラム定義から設計を始めている

「何を保存したいか」から設計を始めると、保存したいものをすべてカラムとして並べることになります。

  • 注文の状態を管理したい → order_status カラムを追加
  • 外部システムの状態も管理したい → external_status カラムを追加
  • 外部機関の処理日時も保存したい → processed_at / processed_time カラムを追加

この積み重ねがステータス混在テーブルを生み出します。

本来の順番は逆です。
「このシステムでどんなイベントが起きるか」を先に列挙し、その後に「イベントの結果として何がリソースとして残るか」を決めます。

原因3：外部システムの仕様をそのままDBに持ち込んでいる

外部システム（決済代行・銀行API・基幹システムなど）と連携するとき、外部が返すコードや状態をそのまま自社DBに保存することがあります。

外部システムの仕様から受け取るコードは次のような特徴を持ちます。

  • 数値の飛び番（1・2・3・4・9 で5〜8が欠番）
  • 機関ごとにコードが異なる
  • 外部仕様の都合を反映した値（「ステータス4の場合は0を入れる」など）

これらを内部のステータスカラムと同じテーブルに混在させると、次の状態になります。

  • 自社の業務ステータスと外部コードの関係が定義されない
  • 外部仕様が変わったとき、自社DBのどこを変えるべきか判断できなくなる
  • コードレビューで「この値は何を意味するのか」が即座にわからない

原因4：連携元システムの設計を無批判に踏襲している

外部システムのDBをそのままクローンして使う場面があります。
クローン元が区分値テーブルで数値の意味を管理していた場合、その区分値テーブルを参照する仕組みなしに数値カラムだけを持ち込むと、値の意味が自社のコードベースのどこにも存在しない状態になります。

この状態でスキーマを継ぎ足していくと、新しいカラムも同じパターンで作られ続けます。

どうすればよかったか

ステップ1：先にイベントを列挙する

設計の最初にやるべきことは、テーブルやカラムを考えることではなく、「このシステムで何が起きるか」を列挙することです。

受注処理を例にすると次のようになります。

  • 注文が作成される
  • 審査依頼が送信される
  • 審査結果が返ってくる（承認 / 否認）
  • 外部システムへの処理依頼が送信される
  • 外部システムから処理結果が返ってくる（成功 / 失敗 / エラー）
  • 結果が自社システムに反映される

これを先に書くことで、テーブルの責務が見えてきます。

ステップ2：リソースとイベント記録を分離する

イベントを列挙したら、テーブルを次の2種類に分けて設計します。

  • リソーステーブル：現在の状態を表す。UPDATEが発生する。
  • イベントテーブル：発生した事実を記録する。基本的にINSERTのみ。

先ほどの混在テーブルを分離すると次のようになります。

分離のポイントは次のとおりです。

  • 外部コードは外部イベントテーブルに「生データとして」保持する
  • 自社のビジネス判断を表す内部ステータスは別カラムで管理する
  • 銀行口座情報はリソースとして独立させ、複数テーブルから参照する

ステップ3：ステータスの遷移を先に定義する

リソーステーブルにステータスカラムを置く場合は、カラムを定義する前に遷移図を作ります。

遷移図を先に作ると次のことが自然に決まります。

  • どの遷移で何のカラムを更新するか
  • どの遷移は許可されないか
  • どの遷移にイベント記録が必要か

遷移図なしにステータスカラムを作ると、後から「このステータスはどこから来るのか」が追えなくなります。

ステップ4：外部コードと内部ステータスを分ける

外部システムと連携するときは、外部コードと自社の内部ステータスを明確に分けます。

分類 管理場所 型 変換タイミング
外部コード（生データ） イベントテーブル VARCHAR / 元の型 変換しない
内部ステータス リソーステーブル TINYINT + Enum定数 受信時に変換

外部コードを受け取った時点でアプリ側が内部ステータスに変換し、DBには内部ステータスだけを書き込みます。
外部コードは元の形で保持しておき、照合・デバッグ時に参照できるようにします。

この設計にすると、外部仕様が変わってもマッピング処理だけを変えればよくなります。

ステップ5：外部システムの区分値を持ち込むときは意味を一緒に持ち込む

外部システムのDBをクローンして使う場合、数値カラムだけでなく、その値の意味を自社に持ち込む手段も合わせて確保します。

方法は次のどちらかです。

  • 区分値テーブルもクローンし、自社アプリから参照できるようにする
  • クローン時点で自社の定数・Enumにマッピングして変換する

どちらも取らないまま数値だけを引き継ぐと、値の意味が自社のシステム内のどこにも存在しなくなります。

まとめ

ステータス混在テーブルが生まれる原因は次のとおりです。

  • イベントとリソースを区別せず、すべてをカラムとして追加している
  • 「何を保存するか」から設計を始め、「何が起きるか」を考えていない
  • 外部システムのコード体系をそのまま内部ステータスに持ち込んでいる
  • 連携元の区分値テーブルなしに数値カラムだけを引き継いでいる

防ぐための設計の順番は次のとおりです。

  1. イベントを列挙する
  2. リソースとイベント記録を分離する
  3. ステータスの遷移を先に定義する
  4. 外部コードと内部ステータスを分ける
  5. 外部の区分値を持ち込むときは意味も一緒に持ち込む

設計の問題は、コードを書き始めてから気づくことが多いです。
しかし根本原因はほぼ常に、設計の最初にイベントとリソースを区別できていなかったことにあります。

Discussion