RDBMSの関係

この記事は私が今度受ける大学の試験に向けたアウトプットになります。勉強したことをNotionにまとめていたところ、noteでまとめておけば万が一間違えてる時に誰かが指摘してくれるかもしれないという淡い期待を込めて書いています。なので参考にならない可能性もありますが、RDBMSについて理解を深めたいよという方はぜひ読んでいただけると嬉しいです。

RDBMSに関してはこれまでについても基本的なことはまとめてきたので割愛しますが、簡単にいうとRDBMSはテーブル同士の関係で成り立っています。

この記事ではDB設計には欠かせない知識としてテーブル同士がどう関係してるのかを見ていきたいと思います。

One-to-many relationships (1:M)

一番多い関係として1対多（One-to-many relationship）が挙げられます。
例えば、商品と商品カテゴリのテーブルがあり、1つのカテゴリに複数の商品が含まれているとします。各カテゴリは多くの製品を含むことができ、各製品は1つのカテゴリに分類されます。

CategoryテーブルとProductテーブル

上記は、CategoryテーブルのidがProductテーブルの外部キーとして紐づいています。このテーブルから一つのカテゴリーに対して複数の商品が紐づいているのがわかります。そのため1対多の関係であると言えます。

Many-to-many relationships (M:M)

多対多の関係を知るには、学校のデータベースを設計する例を挙げて説明していきたいと思います。前提として生徒は複数の科目を履修でき、一つの科目では複数の生徒が存在します。そうなると以下のようなパターンのテーブルが想定されます。

パターン①Studentテーブルに複合キーとして追加する

パターン①Studentテーブルに複合キーとして追加する

StudentテーブルにはClassテーブルのIDと組み合わせて複合キーにしています。複合キーとは複数の列から構成される主キーのことで、この場合、Studentテーブルのidだけだと一人の生徒に複数の科目が履修できるため単一の列として認識されません。そのためclass_idと組み合わせて複合キーにしています。しかしこれは一般的に好まれないパターンになります。例えばこの同じ生徒の名前のどれか一つだけが入力間違えでタイポが発生していたらそれは同じ生徒とみなされません。また、生徒の名前を修正・削除する度に大量のデータを更新する必要も出てくるため効率的ではなさそうです。そもそもStudentテーブルなのに一人の生徒から複数のレコードが返ってくるのは違和感を覚えます。

パターン②Studentテーブルに複数の外部キーを一つのフィールドにまとめてしまう

パターン②Studentテーブルに複数の外部キーを一つのフィールドにまとめてしまう

上記はパターン①を改善して一つのフィールドに複数の外部キーを当てはめています。これでタイポが発生しても大丈夫そうな気がしますが、これはRDBMSのルールに反しています。一つのフィールドには必ず一つの値しか格納することができません。そのため同じ名前だからと複数の外部キーを一つのフィールドには含められません。じゃあどうしたらいいんだという時に登場するのが中間テーブルになります。

パターン③中間テーブルを使用する

ClassテーブルとStudentテーブルにEnrollmentテーブルを中間テーブルとして設置

新たにEnrollmentという中間テーブルを作成し、Class,Studentそれぞれのテーブルからidを外部キーとして取得しています。これで一人の生徒が複数の授業を履修していることや、一つのクラスで複数の生徒がいることもわかりますし、タイポが発生しても外部キーだけを紐付けているので修正しても中間テーブルに影響しません。
そのため、多対多の関係は中間テーブルを用いて管理するのが一般的です。

One-to-one relationship (1:1)

1対1の関係は2つのテーブル間で、1つのレコードがもう1つのテーブルの1つのレコードにのみ関連付けられるものです。このタイプのリレーションシップは、データベースでは比較的まれな気がしますが（1対1ならもう一つのテーブルにすれば良くないかと）、よくある例だとユーザー情報周りかなと思います。

UserテーブルとUser Settingsテーブル

上記は、Users テーブルと UserSettings テーブルがあり、各ユーザに必ず 1 つの設定だけが関連付けられます。Userテーブルが基本的なユーザー情報を持ち、User Settingsでは通知の有無やモードの設定など、オプション的な情報が含まれます。
Users テーブルの UserID が UserSettings テーブルの外部キーとして使用され、各ユーザが一意のプロファイルを持つようにします。このように二つのテーブルを一つにするのではなく、分けることのメリットとしてセキュリティ強化が挙げられるのかなと思います。

感想

DB設計むずい。自分は理解するのにとっても時間が掛かるタイプなのでスッと理解できる人が羨ましいなと思う日々です。次は正規化です。