2系統のIDを1つのカラムで共存させるオフセット方式
独立したAUTO_INCREMENT主キーを持つ2つのテーブルのIDを、下流の1カラムで取り違えなく共存させる。スキーマ不変・最小侵襲を優先して選んだオフセット方式の設計と落とし穴。
同じ「5番」が2つある
決裁経路、つまり承認ルートの定義を、うちのシステムでは2つのテーブルで別々に管理している。仮に経路テーブルA(文書管理系)と経路テーブルB(共通系)と呼ぶ。どちらも INT AUTO_INCREMENT の主キーを持ち、それぞれが 1, 2, 3, ... と独立に採番していく。
ここで問題が起きる。テーブルAにもテーブルBにも id = 5 が存在しうる。ところが下流の処理は、どちらの経路を使うかを「経路ID」というたった1つのカラムに書き込んで持ち回りたい。5 とだけ書かれた値を後から見ても、それがAの5番なのかBの5番なのか判別できない。1カラムに2系統のID空間を押し込む以上、値域が衝突するのは避けられない。
素直に考えれば「種別カラムを1本増やせばいい」で終わる話だ。だが今回は、既存テーブルの構成を変えたくないという強い制約があった。長年動いている経路定義のスキーマに手を入れれば、参照している画面・帳票・バッチすべてを検証し直すことになる。そこで採ったのが、古典的なオフセット方式だった。
値域をずらして意味を持たせる
やることは単純だ。一方の系統(ここではテーブルB)のIDを保存するときだけ、一定のオフセットを足す。例えば +100000。
- テーブルAの5番 → そのまま
5 - テーブルBの5番 →
100005
読み取るときは、値が 100000 以上かどうかを見るだけでいい。100000 以上ならテーブルBの 値 − 100000 番、未満ならテーブルAの 値 番。これで1カラムのまま2系統を取り違えなく復元できる。
この方式が今回とりわけ都合が良かったのは、過去データの移行が要らない点だ。オフセットを導入する前に保存されていた経路IDは、すべて小さな値(オフセット未満)である。判定ロジックは「オフセット未満は旧来どおりテーブルA」と自然に解釈するので、既存データは一切書き換えずに従来の挙動を保てる。マイグレーション用のUPDATE文を1本も流さずに新方式へ移れたのは大きい。
encode / decode に閉じ込める
オフセット方式の実装で唯一かつ最大のポイントは、変換を単一の関数に閉じ込めることに尽きる。生SQLの中で + 100000 や - 100000 を直接書いてはいけない。散らばった瞬間、必ずどこかでencodeかdecodeを片方だけ忘れて事故る。しかもこの手のバグは、オフセット未満の小さいIDでは「たまたま」正しく動いてしまうため、テーブルBを使う特定の経路だけで初めて表面化する。最悪の再現性の悪さだ。
だから、次の3つだけを窓口にする。
// 保存時: どちらの系統かを決めてから符号化する
function encodeRouteId(int $rawId, string $source): int {
return $source === 'B' ? $rawId + ROUTE_ID_OFFSET : $rawId;
}
// 読取時: 元テーブルと元IDへ復元する
function decodeRouteId(int $routeId): array {
return $routeId >= ROUTE_ID_OFFSET
? ['source' => 'B', 'id' => $routeId - ROUTE_ID_OFFSET]
: ['source' => 'A', 'id' => $routeId];
}
呼び出し側は「どちらのテーブルか」を decode の戻り値で受け取り、その系統に対してだけクエリを投げる。オフセットの幅(ROUTE_ID_OFFSET)を定数として一箇所に置き、SQL側にも同じ定数を共有できるようにしておけば、マジックナンバーの二重管理も防げる。
なぜオフセットにしたか — 代替案との比較
もちろん他の選択肢もあった。
- 種別カラムを追加: 一番素直で、意味も明快。だがスキーマ変更になり、既存の参照箇所すべてに影響が及ぶ。今回避けたかったのはまさにこれ。
- 複合キーでUNION: 2テーブルを
(種別, id)の組で扱う。設計としては正しいが、下流が持ち回るのは「1カラム」という前提を崩せなかった。カラムを1本に保ちたいという要件と噛み合わない。 - UUID化: 値域衝突は根本的に消えるが、既存の
INT AUTO_INCREMENTを捨てる全面改修になる。侵襲が大きすぎる。
結局のところ判断軸は「既存スキーマ不変・最小侵襲」だった。オフセット方式は、テーブルにもカラム構成にも一切触れず、変換ロジックというアプリ層の薄皮一枚だけで2系統を分離できる。移行コストがほぼゼロで、旧データもそのまま使える。要件との適合という一点で、これが最も安く済んだ。
AIとの協働作業での学び
オフセット方式は「値域を分割して意味を持たせる」という、それこそ物理メモリのセグメントの時代からある古典的な手だ。派手さはないが、既存構造を壊さずに新しい区別を導入したいときには驚くほど効く。
ただし気をつけるべき点が2つある。1つは境界の見積もり。オフセット幅は、将来テーブルAのIDが増え続けてもオフセットを超えないよう、十分な余裕をもって取る。ここを甘く見ると、いつか下位系統のIDが上位系統の値域に食い込み、静かにデータが化ける。桁の設計はケチらないほうがいい。
もう1つは、繰り返しになるが変換を必ず単一の関数に閉じ込めること。オフセット方式の安全性は、encode/decodeがコード中に散らばっていないという規律の上にしか成り立たない。うまい設計とは、賢いトリックそのものより、そのトリックを一箇所に封じ込める退屈な仕組みのほうにある。