Q&A
"利用"テーブルおよび"利用実績"テーブルについて以下の情報を質問欄に追記していただくと、より具体的な議論が可能になると思います。
・レコードが更新/削除されることはあるか？
・プライマリキー、単一/複合 ユニークインデックスのリスト
・"利用実績"テーブルの"年月"カラムの意味
###解決したい疑問
とあるデータベースにて、簡略化すると下記のような構造を目にしました。
今回、解決（確認）したいことは、下図の「利用実績」テーブルの妥当性です。
「利用」テーブルはいわゆる中間テーブルになりますが、その子テーブルをつくるということに違和感を覚えました。
単純に、「利用実績」テーブルは「利用ID」ではなく「事業所ID」「利用者ID」を持つほうが自然に思えますが、理由を論理的に説明できていません。
設計者の意図としては、利用実績は利用（の登録）が前提になる（＝利用の登録なしに利用実績はあってはならない）のだから、ということでした。
論理設計に詳しい方、「問題なし」あるいは「問題あり」の論理的な理由をお伺いできましたら、助かります。
どうぞよろしくお願いいたします。
回答3件
0
ベストアンサー
少し観点を変えて回答してみます。
「利用」テーブル、「利用実績」テーブル以外にも目を通してみると、
全てのテーブルが「ID列」を保有しています。
このことから提示されたテーブル設計の根底には、
先ずサロゲートキー(代替キー)を利用するという設計思想が読み取れます。
#サロゲートキーの導入の狙い
サロゲートキーとは、
データ構造上一意に定まるものば別にもあるけど、
シンプルな代替キー(主キー)を用意してやろうという考え方、
または利用しているフレームワークの恩恵を受けるために、
その全てのテーブルにサロゲートキーを振るルールに統一するという大まかにはこの2パターンで適用されることが多い設計方針です。
さて今回質問者さんは、
利用実績には事業所ID、利用者IDの２つ持つ方が自然でらと考えておられるようですが、
この考え方自体は間違いではありません。
今回のようなサロゲートキーを導入しないテーブル設計では、
質問者さんの言うような設計も考えられるでしょう。
ただ今回は前提としてサロゲートキーが導入された設計となっているので、
逆に利用実績でも事業所ID、利用者IDを持つことは事実を冗長に管理することとなってしまいます。
それはサロゲートキーが、
テーブル内の一意を保証するデータ構造を代わりに提供しているからです。
なので他のテーブル(「利用実績」など)から参照する際は、
サロゲートキー(ID列)が利用情報を取得するためのキー(外部キー的な役割)を担うことになるのです。
(逆にサロゲートキーを用意しているのにそれを参照キーにしないのは、完全な無駄と言えそう・・・)
データもID列ベースで全て辿れるので、設計上の致命的なミスもありませんしね。
#サロゲートキー導入のメリット
サロゲートキー導入の一番のメリットは、
参照するためのキー(外部キー)を単純に出来る所です。
例えば6項目でようやくデータ上一意に定まる複合主キーを考えてみてください。
このテーブルに対して他のテーブルが参照キーを張る場合、
仮にサロゲートキーが導入されていなければ、
参照する側のテーブルも漏れなく6項目を持つ必要がありますよね？
１つのテーブルだけならいいですが、
万一複数テーブルを参照するテーブルで、
それぞれ複合主キーとなっている場合は、漏れなくその項目を参照側テーブルに用意しないとデータ整合性が保証できないため、
そのテーブル独自で用意するカラム以外のカラムが多くなり、
ぱっと見わかりにくいテーブルになってしまいますよね。
(流石に極端な例かとは思いますが)
#サロゲートキー導入のデメリット
もちろんいいことばかりでなく、
デメリットも抱えています。
それはサロゲートキーを用意することで、
本来の自然キーを表現できなくなる可能性を秘めていることです。
例えば利用テーブルが、
本来は事業所ID、利用者IDで一意であるとします。
この2つの項目に一意制約が張られていないと、
漏れなく重複データが出来上がってしまいます。
ならば一意制約を付ければ良いと考えると思いますが、これでも主キーの機能を果たすにはまだ足りません。
それは一意制約だけではNULLが許容されてしまうためです。
なので主キーと同等の働きをさせるには、一意制約、非NULL制約をつけてあげなければなりません。
これが１つ目のデメリットです。
もう１つのデメリットは、
何も考えずにサロゲートキーを適用すると機能がダブり完全な無駄なカラムとなる可能性を秘めていることです。
例えば顧客コードで完全に一意に定まるデータに対して、ID列を追加した場合を考えてみてください。
この時にIDが果たす役割に何か恩恵が感じられるでしょうか？
(※データのライフサイクルを管理したいという理由でIDを採番するのはありかと思いますがね)
投稿2016/09/15 10:56
総合スコア1636
サロゲートキーについて、たいへん詳細で分かりやすいご説明をありがとうございました！
きちんと意識してみることが、今までなかったと思います。
今回、関わっているプロジェクトのデータベースは、必ずサロゲートキーを付けるルール（おそらく、設計された方はそれを意識的にルール化しているのではなく、無意識にそういうもの、とされています）になっています。
下記のご教示が、今回質問させていただいたことへの核心であると感じましたので、
ベストアンサーとさせていただきました。
（引用ここから）
ただ今回は前提としてサロゲートキーが導入された設計となっているので、
逆に利用実績でも事業所ID、利用者IDを持つことは事実を冗長に管理することとなってしまいます。
それはサロゲートキーが、
テーブル内の一意を保証するデータ構造を代わりに提供しているからです。
なので他のテーブル(「利用実績」など)から参照する際は、
サロゲートキー(ID列)が利用情報を取得するためのキー(外部キー的な役割)を担うことになるのです。
(逆にサロゲートキーを用意しているのにそれを参照キーにしないのは、完全な無駄と言えそう・・・)
データもID列ベースで全て辿れるので、設計上の致命的なミスもありませんしね。
（引用ここまで）
本当にありがとうございました。サロゲートキーについて、書籍等も参照して理解を深めておきたいと思います。
他の方々のご回答も、勉強になりました。
0
設計者の意図としては、利用実績は利用（の登録）が前提になる（＝利用の登録なしに利用実績はあってはならない）のだから、ということでした。
ということは、ビジネスロジック上「利用登録」という過程が存在するということになります。
であれば、［利用］テーブルは中間テーブルと考えない方が良さそう。利用登録という実体を格納するマスタです。
登録日時ですとか登録作業者、なんていうカラムも今後必要になるかも知れません。
そうすると、［利用］テーブル、いや［利用登録］には複合主キーを持たせる現在の形よりも、独自の主キーを（外部には出さないとしても）振る方が良さそうです。
そうすると利用実績は利用登録の子テーブルであり、利用登録IDへの外部キー参照を持つ、と自然に整理できます。
投稿2016/09/15 01:07
総合スコア5568
ご回答をありがとうございました。
たしかに、利用登録という過程が存在します。図では簡略化していましたが、登録日時などの付随する情報も含まれます。
なるほど、この方向で自然である、と解釈できるのですね。
丁寧にご説明いただき、ありがとうございました！
0
「事業所」テーブルを「店舗」
「利用」テーブルを「会員証」
と考えるとわかりやすいんじゃないでしょうか。
恐らく、簡略化されている「利用」テーブルに、論理削除が可能な項目(削除フラグなり、有効期間なり)があるのではないでしょうか。
ある利用者そのものはそのままで、事業所に紐づいた時にだけ利用を制限したりという状況かと思います。
投稿2016/09/15 01:29
総合スコア5405
丁寧なご回答をありがとうございました！
確かに、「利用」を「会員証」と理解すると分かりやすいですね。
一人の利用者が複数の事業所を利用するケースもあります。
その場合も、会員証ごとに利用実績を取るのは、不自然ではありませんね。
あなたの回答
tips
プレビュー
