データモデリングについてお勉強する
データベースをどうやって設計していくかについてお勉強していきます
本日の参考資料はこちら
データベース設計入門#1 リレーションとER図【11分でマスター！DB設計】 - YouTube
データベース設計入門#2 正規化｜無駄のないテーブル設計とは？【日本一わかりやすくDB正規化を解説します】- YouTube
イミュータブルデータモデル(入門編) ／ イミュータブルデータモデル(世代編
「リレーション」とは
「データベースとデータベース同士の関連性」をリレーションと呼びます。
リレーションの種類
1：1 名前 - 成績 みたいな、お互いに他の情報とは紐付かない組み合わせ
1：ｎ（1対多） 学校名 - 名前 みたいな、こちらのテーブルのひとつの情報に対して、別テーブルの複数の情報がくっつく組み合わせ
ｎ：ｎ（多対多）名前 - 部活（兼部可） みたいなやつ。詳細は後述。
多対多のデータをどう取り扱うか
生徒名簿がこちらにあって、部活リストがそっちにあるとします。
こちらの生徒名簿側から見てみましょう。一人の生徒が複数の部活に入る可能性があります。
そちらの部活リスト側から見てみましょう。一つの部活にはもちろん複数の生徒が加入します。
このような、複数：複数が絡むのが多対多のリレーションです。
では、これを名簿上でどのように管理すれば良いでしょうか。
紙の名簿だったら、生徒名簿に「部活1」「部活2」欄を作っておき、部活リストには「生徒名」欄を30個くらい作っておく、という形になるでしょうか。
ところで高校時代、友人に、私が知っているだけでも6個の部活を兼部している人がいました。部活欄が2つだけではとても足りません。まあ大変。
一方、維持ギリギリの5人とかしかいない部活だと、生徒名欄の25個が無駄ですね。でも吹奏楽部は60人くらいいるんですけどどうしましょう。
（実際紙でこの辺を管理するとしたら「部活リスト」の方はルーズリーフとか使って、1ページにつき部活1つ、紙が足りなくなったら足す、とかやるんでしょうけど、今はデータベースの話をしたいので、意地でも「表」の形式で管理することを考えます）
中間テーブルを使って多対多のデータを管理する
多対多のデータを管理する為に活躍するのが「中間テーブル」です。
生徒名簿には、生徒番号と名前だけ。
部活リストの方には、ひたすら部活名と部活コード（作る）だけ。
そして、「生徒番号-部活コード」をひたすら並べておく「中間テーブル」を作ります。
沢山所属している生徒なら、中間テーブルに5個も6個も自分の生徒番号と所属先のデータが入りますし、部活に入っていない生徒なら中間テーブルには名前がないだけです。すっきり～
これが紙の名簿だったら、毎回「えーっと生徒番号◎番さんは部活コード●●と…………あっとまだ下の方にあった…………で、えーとこの番号は誰だ……」ってやらないといけないのですっげー不便ですが、なんと、データベースはそれをプログラム一つで勝手に紐付けて表示してくれるので、プログラムで扱いやすいようにしておく方が便利ですね！！
データベースでは、表を「横方向」に拡張するのは大変ですが、「縦方向」には無限に増やせるので、横に増やす設計にはしないようにしましょう。
あと、中間テーブルには余計なデータを持たせないようにしましょう。この例えで言えば、部活の活動日とか、成績とか。一つ一つのデータベースはできるだけ小さく小さく。
「正規化」を行ってデータベースを使いやすく保つ
同じデータが何度も繰り返されていると、データベースが長々としてしまうので不便です。そこで、同じ項目や、同じ情報が繰り返し登録されないよう「正規化」と言われる手順を踏んで、データベースをスッキリさせます。
・横方向の繰り返しをなくす
例えば
学籍番号| 名前 | クラス | 出席番号 | 部活 | 部活
-------|----------|-------|---------|--------|------
100210 | 田中太郎 | 1－B | 10番 | 美術部 | 剣道部のように、同じ情報が横に並ぶのをやめ、せめて
100210 | 田中太郎 | 1－B | 10番 | 美術部
100210 | 田中太郎 | 1-B | 10番 | 剣道部と2行に分けて管理するようにします。これで横方向の正規化完了。
・縦方向の繰り返しもなくす
ところで上記の例だと「田中太郎 | 1－B | 10番」が縦方向に2回繰り返されることになります。しかも、1－Bと1-Bの表記揺れまで紛れ込んでいます。
（表記揺れについては登録時に紛れ込ませない工夫が必要なのは言うまでもありませんが、データベースの構造でも対策しておくに越したことはありません）
そこで、田中太郎くんの個人情報は別のテーブルに切り出してみます
# 部活動テーブル
学籍番号 | 部活
--------|------
010210 | 美術部
010210 | 剣道部
# 生徒情報テーブル
学籍番号 | 名前 | クラス | 出席番号
--------|---------|-------|---------
100210 | 田中太郎 | 1－B | 10番 これで縦方向にも同じ情報は繰り返されなくなりました。（部活動テーブルに、田中君に関する情報が2行あること自体は必要なことなので、そこで生徒番号が重複すること自体は問題ない。部活動テーブル側で、「100210-田中太郎-1－B-10番」の、同一の組み合わせが繰り返されていることを排除したい）
使う時は、学籍番号をキーにして二つのテーブルを繋げてデータを取り出せばいいわけです。
これで縦方向の繰り返しも排除完了。
こうやって、縦横の繰り返しを排除することが出来れば、ひとまず正規化は完了と思ってよさそうです。
そっから先の論理的な話は、資格が取りたくなったら触ってみるといいんじゃないかな……（ちょっと見に行ったら沼が見えた）
イミュータブルデータモデルになるように頑張る
「拡張に対しては開いていて、修正に対しては閉じている」堅牢なデータモデルを完璧に設計するのは専門家じゃないと難しいですが、素人なりにより理想に近づける為に押さえておくべきポイントをお勉強します。
UPDATEはしたくない
データをUPDATE（更新）できるように設計しようとすると、しこたま大変になります。
慣れないと、えっ、更新するだけじゃん？ と思いがちですが、リレーションしているデータベースで、とあるデータを更新しようとすると、そのテーブル・データを参照している全てのテーブルに影響が及びます。その全てで整合性がとれていることを確認してからじゃないと、怖くて更新できません。
さらに、更新作業がバッティングしてしまったら？？更新作業中に別のアプリがそのデータベースを参照して処理を始めてしまったら？？等々考え出すと泥沼です。
なので、UPDATEする処理は極力できるだけ可能な限り全力で最低限に押さえ込んで行きたいところです。完全に「ナシ」が理想ですが、現実的には難しい。
ところで、上半分をまとめているときに「覚えなくていいよ～」と言われた「エンティティ」という単語が、今この辺りを学習するのに使っているスライドに早速登場しました。大人はうそつきです。
と言うわけでスライドの方では「エンティティ」と呼んでいる部分について、ここではちょっと知能指数を下げて、わかりやすく「テーブル」と呼ぼうと思います。多少意味範囲が変わる部分もあるかも知れませんので、資格の勉強などをしている人は読まないで下さい。
UPDATEを最低限にするテーブルの設計
1・必要なテーブル（エンティティ）を洗い出す
必要な要件の中から、名詞と動詞を探して洗い出すと見つけやすい。
2・洗い出したテーブルを「リソース」と「イベント」に分類する
分類の基準は、データ内に「起こった日付」が必要かどうか。（例えば、部活リストや生徒一覧は日付に関係が無いので「リソース」。「入部」や「入学」は起こった日付が重要なので「イベント」。
3・「イベント」テーブルには1つの日時しか持たせない
入部-退部の日付を両方持たせるとかはせず、「入部」テーブルに入部の日付、「退部」のテーブルには退部の日付だけ集める。
両方の日付が必要なテーブル設計だと、必ず「未来の出来事」のカラムが発生してしまって、そこにNullが入り込む。良くない。
4・イベントテーブルは更新しない
「退部したの、無かったことに☆」……は、まあ、許されるかもしれませんが、とか「留年しちゃったの恥ずかしいから、入学したのを一年遅かったことに☆」とか「退職したの、無かったことに☆」とは世の中行きません。退職したの、無かったことに☆ は、人間関係的にはOKな場合でも、税金やら何やら周りの処理的にはそうは問屋が卸しませんし、さらには「商品売れたの、無かったことに☆」とかし始めたら脱税です。だめです。
というわけで、イベントテーブルはUPDATEしちゃだめです。
5・リソーステーブルに「更新日時」が要るかどうかをよく考える
リソーステーブルには本来日時要件は必要無いはずです。
どうしても「更新日時」が必要な業務要件に当たったら、それは「変更履歴テーブル」としてイベントテーブル化するべきではないでしょうか。
「ちょっとぉ～これ変更したの誰～！？」というのはトレースしたくなるときがあるものですが、そう言う場合は最終日付しか分からない「更新日時」カラム一つじゃなくて、全ての履歴を追いかけられる更新履歴テーブルが必要なはずです。
6・Nullをゆるさない
ここでもちょっとまとめましたが、とにかくNullの侵入を許さないことが大切です。
Nullが入って来そうな気配を察知したら、その背後に別のイベントが隠れていないか検討しましょう。
例えばこういう設計のテーブルだと、
学籍番号| 名前 | クラス | 出席番号 | 部活
-------|----------|-------|---------|--------
100210 | 田中太郎 | 1－B | 10番 | Null 部活にはまだ入っていないのでNullです。そして、入部したらこのテーブルを更新する必要が出てきてしまいます。
あれ、今わたし、「入部したら」って言いましたね。ここにイベント「入部」が隠れていました。
ということは、
# 生徒テーブル（リソース）
学籍番号| 名前 | クラス | 出席番号
-------|----------|-------|---------
100210 | 田中太郎 | 1－B | 10番
# 入部テーブル（イベント）
日付 | 学籍番号 | 部活
-----------|----------|-----
2020-00-00 | 1000210 | 美術部 という設計の方が安心です。
また、イベントテーブル間で同様のことが起こる場合、間に対応表を用意することで解決する場合があります。
例えば複数件の受注に対してまとめて請求を行う場合など、受注側のテーブルに請求番号を持たせようとしても、請求する時まで分からない訳ですから、「将来のためのカラム」になってしまいます。
ここは間に「受注番号－請求番号」の対応表を作ることで解消できます。「将来のためのカラム」は作らないようにします。
あれ、これって結局中間テーブルじゃない？となりますが、中間テーブルは多対多の複雑さを吸収するためだけでなく、時系列による複雑さを吸収し、Nullを排除するためにも使うことが出来ます。交差エンティティとも言うそうです。
そうは言っても日付を持たせたいデータもあるじゃない
例えば、
このお店では、おいしいお菓子を販売しています。普段は3000円ですが、実はこのロットはあと3日で賞味期限の残りが一ヶ月を切ってしまうので、「見切り品」として1500円で販売します。
みたいなとき。
未来のこと、とはいえ「2021年3月12日0時00分を過ぎたら1500円」と決まっているので、Nullにはなりません。でも、まだ起きていないことなので、「イベント」や「リソース」そのままで扱うのもおかしくなりそうです。
そんなときは、【起こったこと（イベント）】と【これから起こること】を分けてテーブルを作りましょう。
そして、「予定価格（これから起こること）」テーブルに登録しておいた内容を、登録予定日が来た時点で「価格変更（起こったこと）」テーブルに反映させれば良いのです。
そして、「商品」テーブルはこの「価格変更」テーブルを参照して、現在の価格を取得するようにします。
もうちょっと事態が複雑になってきて、
部活のデータについて、顧問も年度で替わるし部長も年度途中で替わるし、部によって交代日違うし、活動場所もたまに変わるし、なんなら部活の名称も、より活動実体に即した名前に変わる可能性がありますが、連続した一つの部活動としてデータを管理したいのです
なんてことになってきたとします。
そんなときも、それぞれのデータについて、変更履歴と変更予定のテーブルを作り、常に変更履歴から最新のデータを取得して管理するように設計すれば、UPDATEを使わなくて良くなります。
変更履歴をどうやって管理すればいいのか
ところで、そんなことをしていたら全部のテーブルの全部の項目の変更履歴テーブルが必要になるのでわ？ という心配が出てきます。サーバースペースなどのリソースには大抵限りがあります。
そんなときはどう対応したら良いでしょうか。
1・イベントテーブルの場合
→存在そのものが変更履歴の集合体なので、別途の履歴管理は不要
1－2・「イベント」の例外、発生から終了が一瞬じゃないケース
→「入試」と言っても、「出願」「試験」「合格発表」「入学手続き」と複数のステップがあるような、一瞬で終わらないイベントの場合は、それぞれの小さなイベントを詳細イベントとして記録しつつ、大きなイベントはステータス管理を行う必要がある
2-1・リソーステーブルで、子テーブルがめっちゃいっぱいあるケース
→例えば生徒情報として「学籍番号、名前、住所、クラス、部活、委員会、成績...部活には入退部履歴...成績には各教科の各試験の成績が...」みたいに、たくさんの子テーブルがぶら下がっている場合、全てのテーブルの全ての変更履歴をテーブルに持つのは、あんまり実用的じゃないかもしれません。
常に履歴の全てを参照しなければいけないものでは無い場合、ドキュメントDBや時系列DB等の、NoSQLと言われるような、大量のデータをどーんと入れておくのに向いているデータベースに入れるのが良さそうです。
2-2・リソーステーブルで、履歴もチョコチョコ参照が必要なケース
→「価格は常に変動しているけど、この注文番号で買った時の価格を知りたい」など、過去の情報もちょこちょこ参照が必要な場合は、「履歴」というよりは「世代」として管理が必要です。
「世代」管理をするためのデザインパターン
・有効世代ビューパターン
バックエンドでは世代管理が必要だけど、フロントエンドでは最新情報だけ取れれば良い、という場合に。
実体テーブルとして変更履歴を持ち、最新情報だけビューとして別に用意する。ビューはバッチで更新する。（ビューの更新はUPDATEっていうよりDELETE-INSERT）
・世代バージョンタグ付けパターン
古い世代のデータも、最新情報と同程度に取り扱う必要があるときにはこっち。
Gitのバージョン管理みたいなイメージ。各バージョンにIDを振っておいて、中間テーブル作って管理する。
今回のまとめ
と言うわけで、リレーショナルデータベースを堅牢に設計する方法についてお勉強しました。
・Nullをゆるさない
・正規化をちゃんとやる
・イミュータブルに作る
・リソースとイベントを分ける
辺りが特に重要そうです。
この記事が気に入ったらサポートをしてみませんか？