ステキな一日

IT技術やトレンディな話題を中心に個人的なメモを書き綴っていきます。

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

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

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

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

【広告】

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

「サロゲートキーを使うのは議論のあるところ」みたいなのをよく目にしますが、実際の開発現場ではサロゲートキー(代替キー)を用いた設計の方が圧倒的に有利な場面が多いのは間違いないでしょう。
以降にサロゲートキーを用いた設計手順と、複合主キーとの比較例を合わせて説明します。そもそもサロゲートキーを使ったことが無いという方は一度チャレンジしてみていただきたいと思います。なお、近年のフレームワークではそもそもサロゲートキーとしてid項目を実装するのを前提として開発されているものが多く、知識としても知っておきたい事項であることも事実です。

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

さて、実際のサロゲートキーを用いた設計方法について手順を追って説明したいと思います。
まずはモデリングを行います。まずは論理モデルとしてサロゲートキーを意識せずにモデリングをしてください。(これは説明するまでもありませんね。)
論理モデルが出来上がりましたら、次は物理モデルとしてサロゲートキーを検討していきます。複合主キーとなるテーブルの親テーブルに、id という項目を新規に追加して、これを主キーとします。もともと、主キー候補だった項目にはユニーク制約インデックスを作成します。次に複合主キーとなっていた子テーブルからはそれらのキー項目を削除して、代わりに 親テーブル名+"_id"という名称の項目を追加します。この項目は親テーブルのidと同じ属性です。

論理設計の例:

物理設計の例:

ポイント
(1)外部キーとなる項目は親テーブル名+"id"というルールにする。厳密にする必要はない。たとえば、ユーザーid を用いる場合にテーブルに複数のユーザー_idを持つ場合などは困るから。

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

(3)クラスター化インデックスは、サロゲートキーではなく、業務上で塊になっていてほしい項目を用いたインデックスにした方が良い。

(4)業務上でユニークとなる項目を使ったユニーク制約インデックスを作成する。

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

以下に簡単な例を挙げてみよう。

複合主キーのSELECT文の例:

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

SELECT 製品名称,親部品名称 ,部品名称,必要数量  
from 製品マスタ  
<span style="color: #000000;">inner join 構成マスタ  on 製品マスタ.製品番号 = 構成マスタ.製品番号 and 製品マスタ .製品型式 = 構成マスタ.製品型式  </span>
inner join 部品マスタ  on 構成マスタ.部品番号 = 部品マスタ.部品番号  
where 製品マスタ.製品番号= 'NNN-NNNNN' and 製品マスタ.製品型式 = 'X';  

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

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

SELECT 製品名称,親部品名称 ,部品名称,必要数量  
from 製品マスタ  
<span style="color: #000000;">inner join 構成マスタ  on 製品マスタ.id = 構成マスタ.製品id</span>  
inner join 部品マスタ  on 構成マスタ.部品id = 部品マスタ.id  
where 製品マスタ.製品番号= 'NNN-NNNNN' and 製品マスタ.製品型式 = 'X';  

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

UPDATEの比較

に構成マスタの必要数量に1をセットする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設計について」でした。

【広告】