DB設計について考えてみた。ナチュラルキーとサロゲートキーはどちらが良いのか？

こんにちは。都築です。
 
DB設計について調べていた際に、サロゲートキーとナチュラルキーはどちらが良いのか、複合主キーはPKとすべきではない、などデータベースのキーに関する論争をよく見かけます。
 
今回はそんなサロゲートキーとナチュラルキーについて考えてみました。

ナチュラルキー(自然キー)とは

キーそのものに意味が含まれているキーで、業務的にそのテーブルをユニークにするキーをナチュラルキーと言います。要は入力データ自体をPKとした場合、PKはナチュラルキーとなります。
 
例えば、以下のようなテーブル構成の場合のユーザーテーブルのユーザーコードのように、それだけで意味の分かるキーがPKとなっている場合、ナチュラルキーと言います。

サロゲートキー(代理キー)とは

ナチュラルキーに対して、業務上は意味を持つ値ではないが、システム的に一意な値をとるようオートインクリメントなどで連番を振り、PKとしているテーブルのPKのことをサロゲートキー（代理キー）と呼びます。
 
例えば前述のユーザーテーブルの場合でも、下記の様にそれだけでは意味を持たないユーザーNoを、システムで別途採番してPKとしている場合、サロゲートキーと言います。

ナチュラルキーとサロゲートキーどちらを使うべきか

ナチュラルキーとサロゲートキーはどちらが優れているというものでは無いと思います。サロゲートキーは、アプリケーション開発において必要とされる考え方であって、業務的には必ずしも必要ではありません。
 
しかし、ナチュラルキーだけではシステム開発をする上で困難な事が多々ありますので、それぞれのメリット・デメリットを理解し、適切な設計をすることが大事です。

サロゲートキーの特徴

1. テーブル間の依存関係が薄くなる
2. アプリケーションの画面間引き継ぎ情報や実装などを統一できる
3. 複合主キーのテーブルに比べSQLが簡潔になる
4. 業務上は意味のないキーを持つので、容量を余分に使う

テーブル間の依存関係が薄くなる

サロゲートキーの場合、業務の変更により主キーの体系が変化した場合などの影響が少なくなります。
 
例えば、商品種別がキーの商品テーブルに新たに型番というキーを増やしたい時、ナチュラルキーをPKとしている場合は、商品を参照する様々なエンティティに影響が生じます。
 
もちろんサロゲートキーの場合も影響が生じる場合はありますが、PKだけでなくFK、アプリケーションの改修を考えると、サロゲートキーの方が影響は少なくなります。
 
ナチュラルキーの場合

サロゲートキーの場合

アプリケーションの画面間引き継ぎ情報や実装などを統一できる

全てのテーブルでサロゲートキーとしてIDを持った場合、削除や更新など全ての処理でその行に対する処理をIDで行うことが可能です。
 
例えば、ナチュラルキーの場合、特定の商品を削除するのに商品種別と型番が必要となりますが、異なるテーブルでは一意と特定する為に必要なキーが異なります。
 
全てのテーブルでサロゲートキーとしてNoを持つなどすると、必要なキーはNoのみで統一され、画面間で引き継ぐ情報や実装を統一することが可能です。
 
もちろんそれが一概に良いとは言えませんが、コードを自動生成するツールを作る場合など、実装を容易にするという面では良いのではないでしょうか。
 

複合主キーのテーブルに比べSQLが簡潔になる

ナチュラルキーを複合主キーとして使った場合、「主キーに対するあらゆる操作」が複雑になります。SQLのIN句で同じテーブルを副問い合わせで条件にする際も、サロゲートキーで済む場合が多々あると思います。
 
「１項目でレコードを一意に特定することが出来るカラムがある」という点でSQLを簡潔に記載することが出来ます。
 

業務上は意味のないキーを持つので、容量を余分に使う

上述でも記載しましたが、サロゲートキーは業務上必要なものではありません。ですので、データベースの容量などを考えると余分な容量を使うことになります。データベースの容量によるコストやパフォーマンスなどを考慮すると、デメリットとなります。

まとめ

今回、主にサロゲートキーの特徴をご説明いたしましたが、サロゲートキーが良いというわけではありません。ナチュラルキーは業務上必要な項目なので必ずテーブルに持つことになるかと思います。
 
しかし、業務上必要ではない項目でも、サロゲートキーのようにシステム開発を行う上で、持った方が良い場合があるという事を考慮することで、より良いDB設計を行なっていきたいと思います。