サロゲートキーによるDB設計について

物理設計の段階で主キーが複合キーとなってしまう場合には、どのような対応が考えられるでしょうか？

私の周りでは、複合主キーを用いた物理設計が何の気なしに氾濫しているようです。確かに、業務上でユニークなキーを使って論理設計をしている場合、そのまま実装することが簡単で誰が見てもわかりやすい実装になるかもしれません。

しかし、複合主キーが多用されると、SQLが複雑になり、それがバグの原因となったり、業務変更が起こった場合に複雑なSQLを解析する必要があったり、DBの大幅なデザイン変更が必要になったりと、メンテナンスに多大な影響を及ぼすことがあります。

そこで、複合主キーではなく、サロゲートキーを使用することが考えられます。サロゲートキーは、テーブルに新たにカラムを追加してユニークな値を設定することで、主キーとして使用します。このようにすることでSQLも単純化されます。また、業務変更が起こった場合も、主キーとしてのサロゲートキーが変更されることはないので、メンテナンスも容易になります。

開発現場ではサロゲートキーが有利な場面が多い

実際の開発現場では、サロゲートキーを使った設計が有利な場面が多いとされています。サロゲートキーとは、データベース上で一意に識別するために、単純に自動採番されるキーのことです。

複合主キーを使った場合、SQLが複雑になることや、後々のメンテナンスに影響を与えることがあるため、サロゲートキーを使った設計の方が好まれます。また、近年のフレームワークでも、サロゲートキーを前提として開発されているものが多く、知識としても必要とされることがあります。

サロゲートキーを使った設計手順としては、まずテーブルにidという自動採番されるキーを追加し、そのキーを主キーとして設定します。そして、複合主キーを使っていた場合には、そのキーを個別のカラムに変更して、それぞれにインデックスを設定します。

サロゲートキーを使った設計のメリットとしては、SQLのシンプル化や、後々のメンテナンスがしやすくなることが挙げられます。また、サロゲートキーを使うことで、データの移行や結合がしやすくなるという利点もあります。

ただし、サロゲートキーを使った設計にもデメリットがあります。例えば、データベースにアクセスするために必要な情報が複数になってしまうことや、余分な領域が必要になることがあげられます。また、idが連番であることを利用した攻撃（ID Guessing攻撃）に対して、対策を考慮する必要があります。

結論としては、どちらの設計手法を使うべきかは、そのシステムやアプリケーションの仕様や要件によって異なります。しかし、一般的には、サロゲートキーを使った設計が有利な場面が多いため、検討する価値があると言えます。

サロゲートキーを用いた設計手順

さて、実際のサロゲートキーを用いた設計方法について手順を追って説明したいと思います。
まずはモデリングを行います。まずは論理モデルとしてサロゲートキーを意識せずにモデリングをしてください。(これは説明するまでもありませんね。)

論理モデルが出来上がりましたら、次は物理モデルとしてサロゲートキーを検討していきます。複合主キーとなるテーブルに、id という項目を新規に追加して、これを主キーとします。

そして、もともと主キー候補だった項目にはユニーク制約インデックスを作成します。次に依存関係にあった子テーブルからはそれらのキー項目を削除して、代わりに　親テーブル名＋”_id”という名称の項目を追加します。この項目は親テーブルのidと同じ属性です。

論理設計の例：

物理設計の例：

ポイント
（１）外部キーとなる項目は親テーブル名＋”_id”というルールにします。

（２）サロゲートキーの採番は、自動インクリメントでもよいが、個人的にはUUIDを推奨します。これは他のテーブルのキーと比較した場合でも一意を保証されるからです。

（３）クラスター化インデックスは、サロゲートキーではなく、業務上で塊になっていてほしい項目を用いたインデックスにした方が良いかもしれません。

（４）業務上でユニークとなる項目を使ったユニーク制約インデックスを作成します。

複合主キーとサロゲートキーの実装例

以下に簡単な例を挙げてみます。

複合主キーのSELECT文の例：

このとき、データ一覧を出力する際に、このようなSQLなどになるでしょう。

SELECT 製品名称,親部品名称,部品名称,必要数量
from 製品マスタ
inner join 構成マスタ on 製品マスタ.製品番号 = 構成マスタ.製品番号 
and 製品マスタ .製品型式 = 構成マスタ.製品型式  
inner join 部品マスタ on 構成マスタ.部品番号 = 部品マスタ.部品番号
where 製品マスタ.製品番号= 'NNN-NNNNN' 
and 製品マスタ.製品型式 =  'X'

では、サロゲートキーに置換した例も見てみます。

サロゲートキーに置換したSELECT文の例：

SELECT 製品名称,親部品名称,部品名称,必要数量
from 製品マスタ
inner join 構成マスタ on 製品マスタ.id = 構成マスタ.製品id
inner join 部品マスタ on 構成マスタ.部品id = 部品マスタ.id
where 製品マスタ.製品番号 = 'NNN-NNNNN'
and 製品マスタ.製品型式 = 'X';

JOINのキーがすべてテーブルのIDで結合しているのがわかるでしょうか。

UPDATEの比較

次に構成マスタの必要数量に１をセットするUPDATEを比較してみましょう。それぞれ主キーでレコード選択をします。

複合主キーの例：

UPDATE 構成マスタ
 SET    必要数量 = 1
 WHERE
    製品番号= 'NNN-NNNNN'
    and 製品型式 = 'X'
    and 部品番号 = 'YYYYYYYY'

サロゲートキーの例：

UPDATE 構成マスタ
 SET    必要数量 = 1
 WHERE
   id = '550e8400-e29b-41d4-a716-446655440000' --UUIDを使った場合の例

業務変更時の比較

それでは最後に、業務変更があって、部品マスタに輸入と国産の区別が追加された場合を考えてみます。

複合主キー：

SELECT 製品名称,親部品名称 ,部品名称,部品マスタ.国産輸入区分,必要個数
from 製品マスタ
inner join 構成マスタ  on 製品マスタ.製品番号 = 構成マスタ.製品番号 
  and 製品マスタ .製品型式 = 構成マスタ.製品型式
inner join 部品マスタ  on 構成マスタ.部品番号 = 部品マスタ.部品番号
  and 構成マスタ.国産輸入区分 = 部品マスタ.国産輸入区分
where 製品マスタ.製品番号= 'NNN-NNNNN' 
 and 製品マスタ.製品型式 = 'X'

サロゲートキー：

SELECT 製品名称,親部品名称 ,部品名称,国産輸入区分,必要個数
from 製品マスタ
inner join 構成マスタ  on 製品マスタ.id = 構成マスタ.製品id
inner join 部品マスタ  on 構成マスタ.部品id = 部品マスタ.id
where 製品マスタ.製品番号= 'NNN-NNNNN' and 製品マスタ.製品型式 = 'X'

いかがでしょうか？このように業務に変更があった場合でもテーブルやSQLの変更が少なくて済みます。

まとめ：メリットとデメリットを比較

簡単ですが、メリットとデメリットを比較してみます。

	メリット	デメリット
複合主キー	・テーブルをそのまま見たときに、業務的なデータが見られる。 ・何がキーなのかわかりやすい。	・SQLの結合がバグの温床になる。 ・主キー検索で複数対象の値指定ができない。 例:pk in (‘a’, ‘b’)のような使い方ができない。 ・アプリケーション上でユニーク値の保持処理が煩雑になる。 ・コード値の構成変更などの業務変更に弱い。
サロゲートキーを使った単一キー	・テーブル間の依存関係を弱めることができるので、仕様変更に強くなる。 ・テーブル結合SQLが簡単になることで、バグが減り、コードを速く書ける。 ・フレームワークでよく使われている。	・論理設計上に必要ない項目を設計する必要があり、扱い方を理解するのに少し時間がかかる（？）。 ・外部参照している場合は、結合しないと関連が分からない。 ・余計な項目が増えてディスク容量を圧迫する。(ホントかいな？実際にやって見るとわかるけど、複合主キーをサロゲートキーに変換すると各エンティティで保持する項目が少なくなる事が多い。)

以上、「サロゲートキーによるDB設計について」でした。