サロゲートキーは後付けでもできる。
業務システムのほとんどはナチュラルキーで構築されていると思います。
しかし、シノニムやトリガーを利用すれば、既存システムを変更することなくサロゲートキーを追加して、それ以降、サロゲートキーによる運用も可能になります。手順は以下の通りです。
元のテーブル
以下の３つのテーブルをサロゲートキーによる運用に変更します。
■ Foods 料理マスタ
| 物理名 | 論理名 | 備考 |
|---|---|---|
| CD | 料理CD | 主キー |
| Name | 名前 | |
| Price | 価格 | |
| …… | ||
■ Ingredients 材料マスタ
| 物理名 | 論理名 | 備考 |
|---|---|---|
| CD | 材料CD | 主キー |
| Name | 名前 | |
| Cost_Price | 材料費 | |
| …… | ||
■ Recipes レシピマスタ
| 物理名 | 論理名 | 備考 |
|---|---|---|
| Food_CD | 料理CD | 主キー（複合） |
| Ingredient_CD | 材料CD | 主キー（複合） |
| Quantity | 使用量 | |
| …… | ||
IDカラムを付加する。
現在のテーブル名にIDを採番したシノニムを作成し、全部のテーブルにIDカラムを追加し、現在の主キーをユニークインデックスに変更する。
IDカラムにDefualt値を設定し、主キーを変更する。
■ (M000_)Foods 料理マスタ ※ M000_Foods というシノニムを作る。
| 物理名 | 論理名 | 備考 |
|---|---|---|
| ID | ID | 自動インクリメント（新主キー） |
| CD | 料理CD | ユニークインデックス |
| Name | 名前 | |
| Price | 価格 | |
| …… | ||
■ (M100_)Ingredients 材料マスタ ※ M100_Ingredients というシノニムを作る。
| 物理名 | 論理名 | 備考 |
|---|---|---|
| ID | ID | 自動インクリメント（新主キー） |
| CD | 材料CD | ユニークインデックス |
| Name | 名前 | |
| Cost_Price | 材料費 | |
| …… | ||
■ (M200_)Recipes レシピマスタ ※ M200_Recipes というシノニムを作る。
| 物理名 | 論理名 | 備考 |
|---|---|---|
| ID | ID | 自動インクリメント（新主キー） |
| Food_CD | 料理CD | ユニークインデックス（複合） |
| Ingredient_CD | 材料CD | ユニークインデックス（複合） |
| Quantity | 使用量 | |
| …… | ||
外部キーを追加し、トリガーを作成する。
元の外部キーに対応するサロゲートキー（テーブルID＋_ID）を追加し、トリガーを作成する。
■ (M200_)Recipes レシピマスタ
| 物理名 | 論理名 | 備考 |
|---|---|---|
| ID | ID | 自動インクリメント（新主キー） |
| M000_ID | 料理ID | 追加 |
| Food_CD | 料理CD | ユニークインデックス（複合） |
| M100_ID | 材料ID | 追加 |
| Ingredient_CD | 材料CD | ユニークインデックス（複合） |
| Quantity | 使用量 | |
| …… | ||
外部キーにあたる、ID（サロゲートキー）CD（ナチュラルキー）のいずれか片方だけに値が設定されているとき、設定されていない方を自動更新するトリガーを作成する。
両方に値がセットされているときは、何もしない（アプリ側で整合性を取る）か、IDを優先するかをプロジェクトで決め、決めたとおりトリガーを作成する。
マスタテーブルのCD（ナチュラルキー、元の主キー）が変更されたとき、対応するカラムのナチュラルキーをすべて変更するトリガーを作る。
トリガー・シノニムは非常に便利
トリガーとシノニムを巧く利用すれば、既存のシステムは変更なしに移行が終わります。つまり、サロゲートキーが後から追加されても、すべてのナチュラルキーが非正規化されている状態になり、整合性がトリガーによって維持されているため、既存のアプリは変更する必要はありません。
もちろん、追加開発をしたり、修正が入る度にサロゲートキーを利用するように促し、トリガーを追加することによる更新のレスポンスの劣化についてのチューニングも行う必要があります。
ここまではナチュラルキーで設計されたDBをサロゲートキーに変更する手順ですが、実は私は、最初の設計からサロゲートキーとして作ったとしても、すべてのナチュラルキーを非正規化してトリガーで整合性を維持することを薦めています。
というのも、サロゲートキーを使うとちょっとした抽出でも
WHERE
T100.XXXX_KBN = 'A'↓
WHERE
EXISTS
（SELECT * FROM M100
WHERE
M100.ID = T100.M100_ID
AND M100.XXXX_KBN = 'A')
といったサブクエリーが必要になります。
これに耐えられない人が実に多い。「それぐらいやってくれよ」って思うこともあるけれど、自分でもめんどくさいと思うこともないこともない。しかし、これまで書いた方法と同じ構造を最初から作れば、整合性を保ったままナチュラルキーが非正規化されます。つまり、結合（FROM句）はサロゲートキーで、抽出（WHERE句）はナチュラルキーを使うというルールで運用することも可能になります。前にも書きましたが、もちろん、トリガーを仕掛けると更新のコストは高まりますが、日々行われている非効率な抽出に比べたら遙かに負担が小さいことが多いです。
もし、更新の負荷が問題になるとしても、問題となるテーブル数は僅かでしょう。その僅かなテーブルに対してプロジェクトの特別ルールを設定してトリガーの処理を減らすようにすれば、ほぼ解決できます。
しかし、「サロゲートキー＆正規化原理主義」的なDBAが担当しているとき、とにかく抽出頻度が高いキーを非正規化するなどという中途半端な非正規化をするためには、非正規化するかどうか話し合ったり、依頼書が必要であったり、会議をしたりする必要が出てきます。しかし、全面的に画一的にやれば、そういった手間は必要ありません。トリガー・シノニムは、テーブル設計書を工夫して自動的に作ることが可能ですから、実際のコーディングはほとんど必要ありません。テーブル設計書のひな形さえ作っておけば単純作業です。更に、先ほどのサブクエリになるかならないかも統一できるため、ルールが決まれば品質の高いプログラムになります。
というわけで、私も理想としては「ナチュラルキーの非正規化」はなしにしたいけれど、主キーはサロゲートキーで、ナチュラルキーは非正規化してトリガーで整合性を保つ、ということをお勧めしています。
ここまでに使ったトリガー・シノニムは、テーブル設計書から自動出力できるようにすれば、元の設計で外部キーが正確に把握できる状態なら、負荷テストぐらいで移行が完了できます。更に、将来、コードの桁数が増えたり、コードの統一化など、コード体系の変更が発生しても、プログラミングレスで自動更新されます。もちろん、実際の変更処理については、処理時間などについての注意は必要ですけれど……。
これらの変更は非常に効果が大きいのですが、費用がなかなかでないので、一部ずつは適用していますが、すべてを一度に適用できたプロジェクトはありません。余程、理解力のあるPM（かそれ以上）が決断しないとできないでしょう。
しかし、本当に効果が出ますので是非チャレンジしてみてください。
