SQLアタマアカデミー
第3回 テーブル設計のグレーゾーン~毒と薬は紙一重 (4)サロゲートキーVSナチュラルキー
サロゲートキーVSナチュラルキー
DBエンジニアの方なら,
サロゲートキーは,
- ① そもそも入力データに主キーにできる項目がなく,
データが重複している場合 - ② 主キーの値が使いまわされる場合
- ③ 主キーの体系が変化する場合
①のケースは,
もっとよくあるケースが②と③です。たとえば,
図10 サロゲートキーなしの場合
item_ | item_ |
---|---|
001 | ガムテープ |
002 | 糊 |
003 | カッターナイフ |
004 | 付箋 |
005 | ノート |
item_ | item_ |
---|---|
001 | 消しゴム |
002 | 糊 |
003 | カッターナイフ |
004 | 付箋 |
005 | ノート |
こういう場合,
そこで,
もっとも,
図11 サロゲートキーの場合
srg_ | item_ | item_ | start_ | end_ |
---|---|---|---|---|
1 | 001 | ガムテープ | 2003 | 9999 |
2 | 002 | 糊 | 2003 | 9999 |
3 | 003 | カッターナイフ | 2003 | 9999 |
4 | 004 | 付箋 | 2003 | 9999 |
5 | 005 | ノート | 2003 | 9999 |
商品ID
srg_ | item_ | item_ | start_ | end_ |
---|---|---|---|---|
1 | 001 | ガムテープ | 2003 | 9999 |
2 | 002 | 糊 | 2003 | 9999 |
3 | 003 | カッターナイフ | 2003 | 9999 |
4 | 004 | 付箋 | 2003 | 9999 |
5 | 005 | ノート | 2003 | 9999 |
6 | 001 | 消しゴム | 2003 | 9999 |
- 注5)
- これに対する別解として,
OTLT のところで見たように, ナチュラルキーに 「開始年月」 「終了年月」 を組み合わせて管理するという方法もありえます。こちらのほうが, 論理的に不要な列を使わないという点では明快な設計です。ただし, クエリはサロゲートキーよりも複雑になるので, その分パフォーマンスは劣るでしょう。
おわりに
今回は,
冒頭でも述べたように,
それでは最後に,
- 演習1.OTLTテーブルに,
以下の2つの要件を満たす制約を付けてください。 要件1:都道府県コードは
「01」~ 「47」 の範囲であること 要件2:性別は
「0」 「1」 「2」 「9」 のいずれかの値であること - 演習2.サロゲートキーを実現する簡単な方法は,
一意な連番を行に振ることです。これを実現する方法を, お使いのDB上で考えてください (本文中にも書きましたが, 連番機能のサポート状況はかなりバラバラです)。また, 実装に依存しない方法も考えてみてください (特に, 複数ユーザが同時に採番する状況に気をつけて)。
参考資料
- Joe Celko 著
『Joe Celko's SQL Programming Style』 (Morgan Kaufmann Pub, 2005, ISBN978-0-12-088797-2) 単一参照テーブルについては
「5. 3.4. 1 One True Lookup Table」 を参照。セルコはこのテーブルが大嫌いで, 使うべきでない理由を8つも挙げて罵倒しています。セルコのOTLTに対する批判はWeb上でも読めます。 また,
サロゲートキーとナチュラルキーの対比については, 「3. 13 Every Table Must Have a Key to Bea Table」 の解説がたいへんわかりやすく, かつ詳細です。もちろんセルコは, サロゲートキーも原則的には勧めていません。 - C.
J.デイト著 『データベース実践講義』 (オライリー・ ジャパン, 2005, ISBN978-4-87311-275-6) 正規化をはじめとする設計理論の基本的な重要性を確認するには,
「第7章 データベース設計理論」 を参照。本稿冒頭の引用は本書p. 149より。 - ミック著
『達人に学ぶ SQL徹底指南書』 (翔泳社, 2008, ISBN978-4-7981-1516-0) 不幸にも列持ちテーブルで,
複数の列に対して条件を記述するクエリを書かねばならなくなった方は, 本書の 「1-8 EXISTS述語の使い方」 の 「列に対する量化:オール1の行を探せ」 が役に立つでしょう。
バックナンバー
SQLアタマアカデミー
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (5)集合指向と手続き型
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (4)OLAP関数と集約関数を組み合わせる
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (3)OLAP専用関数
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (2)OLAP関数の基本構文
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (1)OLAP関数とは何か
- 第10回 結合大全 (5)非等値結合
- 第10回 結合大全 (4)自己結合
- 第10回 結合大全 (3)外部結合
- 第10回 結合大全 (2)内部結合
- 第10回 結合大全 (1)クロス結合