プログラマの思索

DOAをもう一度勉強し始めたので、候補キーと2次識別子に関する概念を整理してみる。
ラフなメモ書き。
間違っていたら後で直す。

【元ネタ】
代理キー - Wikipedia

主キー - Wikipedia

外部キー - Wikipedia

連関エンティティと関連クラス: プログラマの思索

データモデル表記読み替えハンドブック: プログラマの思索

2次識別子を使ったデータモデリング: プログラマの思索

【1】主キー、候補キー、2次識別子(代替キー)

スーパーキーは、関係の属性を一意に決めることができるキーの組み合わせ。
候補キーは、スーパーキーから冗長な属性を排除したキーの組み合わせで、主キーの候補になるキー。
主キー(primary key)は関係の属性を一意に決めるテーブル上の唯一の識別子。
2次識別子(代替キー：alternate key)は、候補キーのうち主キー以外のキー。

スーパーキーに冗長性がないものが候補キー。
主キーは候補キーの一つ。
候補キーには、主キー以外に、2次識別子(代替キー)も含む。

スーパーキー　＞　候補キー　＞　主キー、代替キー　のイメージ。

主キーは分かりやすい。
代替キーが存在すれば、候補キーは2個以上になる。

代替キーの例としては、社会保障番号やJANコードなどがあげられるだろう。
日本国民なら必ず年金をもらえる資格があるから20歳になれば社会保障番号が必ず付与されて、一意に決まる。
しかし、20歳未満の日本人には付与されないから、NULLもありうる。
つまり、代替キーの制約を実装するには、unique制約だけであり、NOT NULL制約が付与されるとは限らない。
渡辺さんの本「業務別データベース設計のためのデータモデリング入門」の46ページに易しく説明してくれている。

代替キーが混じると普通は、BCNFではない。
主キーが非キー属性に依存してしまうからだ。
だから、第3正規形に無理に分解すると、関係従属性が失われるので、第3正規形まででいいとよく言われる。
でも、渡辺さんの本「生産管理・原価管理システムのためのデータモデリング」によれば、その説明は間違いで、代替キーをうまく使えば、BCNF正規形にできると説明しており、「生産管理・原価管理システムのためのデータモデリング」の44ページに詳しく説明されている。

渡辺式ER図では、データモデル上で代替キーによる制約を課すことで、業務ルールを表現することを重視している。
特に製造業における入庫・出庫や在庫などの受払という概念では、2次識別子が重要な役割を果たす。
この辺りのモデリングのテクニックはもう一度整理する。

【2】自然キー、人工キー、代理キー

自然キー(natural key)は、システム外部から格納すべきものとして与えられる情報を格納する属性からなる主キー。
つまり、画面や帳票など、人が認識した現象の中で、一意に決める番号を指す。
例えば、人が決めた商品コード、顧客コードの体系、帳票に出力される製造指図書番号などが相当するだろう。
自然キーは、T字形ERのidentifierの概念を連想させる。

人工キー(artificial key)は、一意性を確保するためだけにシステム内部で生成され、利用者が関知しない情報を格納する属性からなる主キー。
例えば、Oracleシーケンスなどが相当するだろう。
つまり、DBMSが勝手に振った一意な連番を指す。

最近なら、RESTなWebアプリを作るために、全てのテーブルは代理キー(surrogate key)というシステムが付与した勝手な連番を主キーにする時も多い。
特にRailsが流行してから、Webアプリのデータモデリングはその傾向が強い。
代理キーを導入した方が、URLが簡単に書けるし読みやすくなる。
だから、Railsでは複合キーを代替キー(alternate key)かつ外部キーにしてしまい、代理キー(surrogate key)を主キーにする。

この手法は、複合キー(composite key)に含まれるキーの個数が多い場合にも使われる。
複合キーが4個や5個以上のキーから成る場合、代理キー(surrogate key)を主キーにした方が読みやすいし、プログラミングも楽になるからだ。
その場合も複合キーは代替キー(2次識別子)としてunique制約を課す。

例えば、入庫や出庫では、入庫番号や出庫番号が主キーで、倉庫コード・棚番号・商品番号が代替キーになる時が多い。本来は、倉庫コード・棚番号・商品番号が複合キーとして主キーになってもよいのだが、入庫番号や出庫番号が指図書に一意に表示されているので、そちらが主キーになる。

あるいは、小売業の在庫管理や販売管理では、商品の色やサイズなどで一意に識別できるSKU （stock keeping unit）というコードが振られる。SKUは在庫の単位でもある。
この場合、赤いLサイズのセーターのような商品は、商品マスタではSKUコードで一意に識別するため、SKUコードが主キーになる。
但し、SKUコードとは別に、商品コード、色コード、サイズコードなどの組合せでも一意になるので、これらが代替キーかつ外部キーとして設定される。

【3】外部キー、複合キー、リレーションシップ

テーブル同士の関連(リレーションシップ)は、外部キー又は複合キーとして表現される。
DOAを勉強して思ったのは、OOAは重複度を重視するが、DOAではリレーションシップが外部キーで表現されるのか、複合キーとして表現されるのか、という点を重視する点が面白い。

2個のテーブルの関連が複合キーとして表現される場合、連関エンティティが現れる。
これは、OOAの関連クラスに相当する。
「～ごとに」「～単位」で表現される業務ロジックは、連関エンティティで表現できる時が多い。

普通は、二つのテーブルの関連は、外部キーとして表現される場合が多い。
つまり、業務の制約は、外部キーとして表現されるケースが非常に多い。
例えば、社員は一つの部に属するが、部には複数の社員がいる場合などは、社員テーブルに部コードが外部キーとして張られるのが普通。

業務ルールをどのように外部キーとして張ればいいのか、という問題に対しては、T字形ERが鮮やかに解決している。

自分流モデリング探しの旅(2)～T字形ER データベース設計技法 - papandaDiary - Be just and fear not.

(前略)
* resourceとresourceをつなぐ場合：対照表を生成する。対照表はeventである。
　（例）従業員と営業所
　　　　　従業員-営業所の対照表を作成し、リレーションシップを保全する。
* resourceとeventをつなぐ場合：event側に参照キーを持たせる。
　（例）顧客と注文
　　　　　注文entityに顧客コードを持たせる。
* eventとeventをつなぐ場合
** 「１：１」「１：ｍ」：時系列の遅い方に持たせる。
　（例）受注と請求（一つの受注に対し請求は一つ）
　　　　　請求に受注番号を持たせる。
** 「ｍ：１」「ｍ：ｍ」：対応表を生成する。
　（例）受注と請求（複数の受注に対し請求は一つ）
　　　　　受注-請求の対応表を生成し、リレーションシップを保全する。
(後略)

例えば、顧客というリソースと受注というイベントにリレーションシップを持たせたい場合、イベントにリソースの主キーを外部キーとして追加すればいい。
あるいは、受注や出荷などのようなイベント系テーブルにリレーションシップを持たせたい場合、先行イベントの主キーを後続イベントに外部キーとして追加すればいい。