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

サロゲートキーによるDB設計について。データベースを物理設計する段階で、主キーが複合キーとなってしまう場合に、どうするかを検討したことがあるだろうか？

私の周りでは、何の気なしに複合主キーを用いた設計が氾濫している。
たしかに、業務上でユニークなキーを使って論理設計をしているものだから、そのまま実装するのが余計な時間もかからないし、誰が見ても（？）わかりやすい実装になるかも知れない。

しかし、複合主キーが氾濫していると、SQLが複雑になり、それがバグの温床になったり、更には、ひとたび業務変更がおきると、対応のために複雑なSQLを解析したり、DBに大幅なデザイン変更が発生したり、と後々のメンテナンスには目に見えない大きな影響を及ぼしたりします。

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

「サロゲートキーを使うのは議論のあるところ」みたいなのをよく目にしますが、実際の開発現場ではサロゲートキー（代替キー）を用いた設計の方が圧倒的に有利な場面が多いのは間違いないでしょう。

この記事では、サロゲートキーを用いた設計手順と、複合主キーとの比較例を合わせて説明しています。そもそもサロゲートキーを使ったことが無いという方は一度チャレンジしてみていただきたいと思います。使ってみてはじめて、メリデメが理解できると思います。
なお、近年のフレームワークではそもそもサロゲートキーとしてid項目を実装するのを前提として開発されているものが多く、知識としても知っておきたい事項であることも事実です。

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

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

論理モデルが出来上がりましたら、次は物理モデルとしてサロゲートキーを検討していきます。複合主キーとなるテーブルに、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の変更が少なくて済みます。

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

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

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

【広告】