2次識別子を使ったデータモデリング

渡辺式ER図を使ったデータモデリングでは、2次識別子が重要な役割を意味する。
「生産管理・原価管理システムのためのデータモデリング」を読んで腑に落ちなかった点が下記のBlogを読んで分かったのでメモ。
ラフなメモ書き。

【元ネタ】
2006-07-19 - 極北データモデリング　ボイス・コッド正規形への分解は、結局どうすればいいのか(2)

ボイス・コッド正規形への分解は、結局どうすればいいのか(1) - 極北データモデリング

データ値の制約SQL -TECHSCORE-

＠IT：Oracle管理者のためのSQLリファレンス

生産管理・原価管理システムのためのデータモデリング　review - Ynishi Bussiness Logs

【1】2次識別子とは

2次識別子とは、主キーでないが一意になるキー。
但し、2次識別子となるキーは、値が更新されてもいいし、NULL値も許される。
例えば、商品マスタのJANコードや、製品マスタの仕様コードなどがあるだろう。

2次識別子は主キーにはなりえないが、ビジネスルール上は一意だが、更新タイミングによってはNull値の場合がある。
2次識別子は候補キーでもある。
DOAを駆使しないならば、アプリケーションでロジックを実装しているだろう。

「生産管理・原価管理システムのためのデータモデリング」では2次識別子を実際のデータモデリングで駆使する。
その理由がよく分からなかったけれど、上記のBlogでようやく理解できた。

【2】ボイスコッド正規形へ分解する時に何故2次識別子が必要になるのか？

2006-07-19 - 極北データモデリング　ボイス・コッド正規形への分解は、結局どうすればいいのか(2)とボイス・コッド正規形への分解は、結局どうすればいいのか(1) - 極北データモデリングの例をあげておく。
＃詳細は上記Blogを参照。

受講:学生,科目,教官

というテーブルがあったとする。
主キーは、学生と科目の複合キー。
但し、下記2この関数従属があるとする。

{ 学生, 科目 } -> { 教官 }
{ 教官 } -> { 科目 }

「1科目を教える教官は複数いてよい」ビジネスルールが{ 学生, 科目 } -> { 教官 } 、「1教官は1つの科目しか担当しない」ビジネスルールが{ 教官 } -> { 科目 }の関数従属に相当する。

受講テーブルは第3正規形だが、{ 教官 } -> { 科目 }の関数従属があるので、ボイスコッド正規形ではない。
受講テーブルの弱点は、学生と科目が決まらないと教官を含めたレコードを登録できないので、履修登録前の講座(教官と科目のペア)を受講テーブルに登録できなかったり、公開中の講座から学生が全員履修取り消しで0人になったら講座のレコードそのものが消えてしまう。
つまり、更新時異常の弱点がある。

受講テーブルに他の参照制約(no action, cascade, check)を追加しても解決できない。

普通の教科書では、下記へ分解するため、{ 学生, 科目 } -> { 教官 }の関数従属が消えてしまう。

講座:教官,科目　(教官が主キー)
受講:学生,科目　(学生、科目が複合キー)

つまり、{ 学生, 科目 } -> { 教官 }の関数従属を満たさないレコードが受講Tblに挿入可能になってしまう。
だから、普通の教科書では、ボイスコッド正規形を分解するとよくない、と言われて終わる。

しかし、T字形ER図や渡辺式ER図では別の解決策を取ろうとする。
つまり、上記Blogに書いているように、全てのビジネスルールを表す構造(テーブル、リレーションシップ)を新たに追加すればいいという発想で解決しようとする。

渡辺式ER図では、下記のようなER図になる。
＃()が主キー。

科目 = { (科目コード), 科目名 }
教官 = { (教官コード), 教官名, 科目コード }
学生 = { (学生コード), 学生名 }
受講 = { (学生コード, 教官コード), (学生コード, 科目コード) }

受講テーブルは、(学生コード, 教官コード)が主キーであり、 (学生コード, 科目コード)を2次識別子として、教官テーブルへ参照制約を追加すればよい。
そうすれば、(学生コード, 科目コード)は教官テーブルに存在するデータのみしか登録できなくなるので、受講テーブルに更新時異常が起きることはない。

但し、実際の受講テーブルは{ (学生コード, 教官コード), 科目コード}であり、実際の2次識別子の実装方法はunique制約を使えば良い。

create table class_attending (
teacher_id integer not null references teacher(teacher_id),
course_id integer not null references course(course_id),
student_id integer not null references student(student_id),
-- 主キーの設定
constraint class_attending_pk_key primary key (teacher_id, student_id),
-- "{ 教官 } -> { 科目 }" の実装(参照制約)
constraint class_key foreign key (teacher_id, course_id) references class(teacher_id, course_id),
-- "{ 学生, 科目 } -> { 教官 }" の実装(2次識別子)
constraint class_attending_unique_key unique (course_id, student_id)
);

unique制約を持つキーは、テーブル上で一意の制約があるが、複数個作れるし、NULL値も許される。

「生産管理・原価管理システムのためのデータモデリング」の72ページでは、MS Accessで2次識別子を実装するには、インデックスプロパティで固有インデックス＝はい、NULL無視＝はい、を選択する方法を紹介されている。

生産管理・原価管理システムのためのデータモデリングでは、二次識別子の実装を諦めたと書いてあるが、unique制約を付ければ問題なく実装可能のはずだと思う。

実際の2次識別子となるキーは、固有属性でなく継承属性で初めて現れるキーもあるため、注意しないと分かりにくいので要注意。

【3】渡辺式ER図の独創性

渡辺式ER図で2次識別子を使ったデータモデリングを駆使する理由は、ボイスコッド正規形も使って最終的には第5正規化まで正規化し尽くすためにあるからだ。

「販売管理システムで学ぶモデリング講座 (DB Magazine SELECTION)」にも書かれているが、業務システムの複雑さそのものは、いくらサーバーの性能がアップしても、プログラミング言語の生産性が上がっても変わらないことを「業務システムの複雑さの保存則」と呼んでいる。
その保存則が正しいと言う仮定のもとで、機能モデルは簡単にして、データモデリングと業務モデルを複雑化する方向がよいと「販売管理システムで学ぶモデリング講座 (DB Magazine SELECTION)」は主張している。

正規化するほどテーブル数も増えて、整合性の制約も増えていき、データベース設計が非常に複雑になるけれど、ビジネスルールがデータベースの制約で実装されているため、アプリケーション層で実装すべきビジネスルールが減る利点がある。

昨今のWeb開発は、Railsのような優れたフレームワークがあるおかげで、データベース設計さえしっかりしていれば、CRUD機能などは簡単に実装できるし、複雑なロジックもRubyでサクサク実装すればいい。

「エンタープライズ Rails ―企業ユーザのためのWebアプリケーション設計術」では、Railsのスケールアップのためにデータモデリングをあらかじめ考慮すべきと主張し、RailsでのDOAの重要性を主張している唯一の本でもある。参考になる。

又「データベース・リファクタリング」では、プログラムのリファクタリングと同様にデータベース設計のリファクタリング方法を説明している唯一の本。とても役立つ。

データモデリングの技法は、実は最近どんどん重要性を増しているという隠れた事実があるように思う。