AMG Solution

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

DB設計について調べていた際に、サロゲートキーとナチュラルキーはどちらが良いのか、
複合主キーはPKとすべきではない、などDBのキーに関する論争をよく見かけます。

今回はそんなサロゲートキーとナチュラルキーについて考えてみました。

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

キーそのものに意味が含まれているキーで、業務的にそのテーブルをユニークにするキーをナチュラルキーと言います。
要は入力データ自体をPKとした場合、PKはナチュラルキーとなります。

例えば、以下のようなテーブル構成の場合のユーザーテーブルのユーザーコードのように、それだけで意味の分かるキーがPKとなっている場合、ナチュラルキーと言います。
surrogatekey1

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

ナチュラルキーに対して、業務上は意味を持つ値ではないが、システム的に一意な値をとるようオートインクリメントなどで連番を振り、PKとしているテーブルのPKのことをサロゲートキー(代理キー)と呼びます。

例えば前述のユーザーテーブルの場合でも、下記の様にそれだけでは意味を持たないユーザーNoを、システムで別途採番してPKとしている場合、サロゲートキーと言います。
surrogatekey2

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

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

サロゲートキーの特徴

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

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

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

ナチュラルキーの場合
surrogatekey3

サロゲートキーの場合
surrogatekey4

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

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

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

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

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

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

まとめ

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

TSUDUKI'S BLOG

都築健二の記事

都築健二の記事の最新情報をお届けいたします。

SAME CATEGORY BLOG

この記事と同様のカテゴリー記事

LOADING