はじめに
今回は外部キーを張るときに最低限意識したいことについて書きました。
何か間違えがあったり、もっとこういうところも意識してますという人がいたらコメントお願いします。
この記事で伝えたいこと
①リレーションシップ先のデータを消したときに同時にリレーションシップ元のデータが消えても自然な状態を作る
ON DELETE CASCADEをうまく利用できる状態を作る
つまり親子関係を正確に表現する。
リレーションシップ先は親テーブル、リレーションシップ元は子テーブルを意味しています。
②データを作成するときのことを考えてデータの生成順序がおかしくならないように外部キーを張る
③関連を表現するときに中間テーブルを利用したほうが良い場面がある
注意
下記【例を交えながら説明】の説明に出てくるテーブル設計に関しては、上記の【この記事で伝えたいこと】の①と②と③の項目に対して想像しやすいように、理解しやすいようにできるだけシンプルに、身近な例で自分で考えたものです。
ですので前提、例ででてくるテーブル構造は説明に特化したものだとお考えください。
あえて削ぎ落としている細かい部分もあります。
「契約のテーブル分ける必要ないのでは？」
「こんな簡単に物理削除していいの？」
「普通論理削除じゃない？」
「そもそも前提クラスに担任いない状況ないよね？」
「退職とかで先生データ消しちゃだめじゃない？」
「保管期間的にデータは残しておかないといけないよね？」
とうとう例にでてくるテーブル設計に関してご意見はあると思いますが、
あくまで説明用なのでご容赦願います。
自分は何もこの記事で契約管理システムのDB設計、スクール管理システムのDB設計についてのことを伝えたいわけではありませんので。。。
下記の説明では【この記事で伝えたいこと】の①と②と③の考え方の理解だけしていただければ幸いです。
例を交えながら説明
1個目の例の登場テーブル
- 契約テーブル（企業とユーザーの契約の共通の項目を管理）
- 企業契約テーブル（企業側の項目を管理）
- ユーザー契約テーブル（ユーザー側の項目を管理）
契約と企業契約で1対1、契約とユーザー契約で1対1というふうに外部キーを張りたい。
契約は削除される可能性があるという前提。
■契約テーブルが企業側契約IDとユーザー側契約IDを持っているパターン
このテーブル設計ではちゃんと1対1が表現されていてうまくいきそうです。
しかし、良くない点が２つあります。
1. 企業契約データをDELETEしたときに、契約データが消えてしまい、ユーザー契約だけが残るというような構成になっています。
逆もまたしかりです。
2. このテーブル構成でデータを作成するには企業契約データ、ユーザー契約データを作成し、そのIDを用いて契約データを作成するという順序になってしまいます。
本当は共通の契約データを作成してから詳細のデータ（企業、ユーザー）を作成するという順番のほうが自然です。
■企業契約テーブル、ユーザー契約テーブルが契約IDを持っているパターン
このテーブル設計だと契約テーブルで企業契約ID、ユーザー契約IDを持っているときの良くない部分が改善されています。
契約データを削除すれば企業契約データ、ユーザー契約データも削除されるような設計になっています。
それとデータを入れる順番も自然になっています。
テーブルにデータを入れるときは契約作成→企業契約、ユーザー契約作成 となるので自然な順番になっています。
正しい親子関係をリレーションシップで表現できています。
2個目の例の登場テーブル
・先生テーブル
・クラステーブル（学校の組の情報を管理）
先生とクラス間で1対1の外部キーを張りたい。
■クラステーブルが先生IDを持つパターン
このテーブル設計でもちゃんと1対1が表現されてはいるのですが、良くない点があります。
1. 先生データを削除した瞬間に参照元のクラスデータも消えてしまいます。
先生が退職したらクラスがなくなってしまうのは不自然です。
2. 先に先生データを作成することを強制されます。
要件で必ず先生のデータから作成される前提であればあまり問題にはならないのですが、
先にクラスだけ登録しておきたいという要件があったときに先生IDフィールドをnull許容にしなければならず、避けたいところです。
■先生テーブルがクラスIDを持つパターン
このテーブル設計も上記の設計と同じような良くない点をはらんでいます。
1. クラスデータを削除した瞬間に参照元の先生データも消えてしまいます。
クラスがなくなったら先生がいなくなるのは不自然です。
2. 先にクラスデータを作成することを強制されます。
要件で必ずクラスのデータから作成される前提であればあまり問題にはならないのですが、
先に先生だけ登録しておきたいという要件があったときにクラスIDフィールドをnull許容にしなければならず、避けたいところです。
■担任テーブル（中間テーブル）を新たに作るパターン
このテーブルでは、今まで紹介してきたデメリットは解消されています。
1. クラスデータを消しても先生データは消えず、先生データが消えてもクラスデータが消えないという関係になり、解消されています。
2. クラスデータでも先生データでもどちらでも順番問わずデータを作成することができます。
今回は【担任】という関係を中間テーブルを使って表現しました。
中間テーブルを使うことで、変にデータが消えてしまう状況が改善できました。
それと中間テーブルを使うことで外部キーを張るタイミングをコントロールすることができます。
ここまでで例を交えた説明は終わりです。
以降は
- なぜON DELETE CASCADEにこだわるのか
- 中間テーブルは【多体多の関係を表現するもの】として解釈をしていると視野が狭まる
について説明したいと思います。
なぜON DELETE CASCADEにこだわるのか
理由は２つあって、
１つ目はデータを削除するときに楽できるからです。
先程の契約の例では、ある契約の情報を消したいときは外部キーを張っていなければ3回DELETEを実行しなければなりません（契約テーブル、企業契約テーブル、ユーザー契約テーブル）。
ただ、ON DELETE CASCADEをうまく利用できていれば契約テーブルのデータ（親データ、リレーションシップ先データ）を消すだけで、リレーションシップ元（子データ）である企業契約テーブル、ユーザー契約テーブルのデータは同時に消えます。
親のデータを消すだけで子供のデータも消えるというのは楽なので積極的に使っていきたいです。
2つ目は重いデータベースにしないためです。
ON DELETE RESTRICT中心に設計をすると、親テーブルのデータが消しにくくなってしまい、データが削除しづらいシステムが出来上がってしまいます。
結果データが増え続けてしまい、パフォーマンスが落ちます。
ただし注意したいのは親を消してしまってはいけないときにON DELETE CASCADEを使ってしまうことです。
基本的にはON DELETE CASCADEで外部キーを張る方針で、リレーションシップ先のデータが消えてはいけない場合はON DELETE RESTRICTで外部キーを張るというような方針が良いのではないかと思っています。
中間テーブルは【多体多の関係を表現するもの】として解釈をしていると視野が狭まる
よく中間テーブルは【多対多】の関係を表現するときに利用すると本や記事で書いてありますが
（例えば記事とタグのような関係性を表現するときなどに利用するというように。。。）
実は1対1のときでも【所属】などの関係を表現するために使うと良いことがあります。
その良いこととは、関係を持たせるタイミングを操作できるということです。
関係を持たせるタイミングを遅延させたい、あるタイミングで関係をもたせたいというときに、
中間テーブルを利用すれば実現することができます。
最後に
テーブル設計はしっかりと業務を理解することが前提必要で、難しい分野で僕自身もまだまだ未熟ですが、
今回紹介したことを意識すれば最低限、ひどい外部キーを張らずに済むのではないかなとおもいます。
※■2023年1月29日修正
- リレーション→リレーションシップと修正しました。
ご指摘ありがとうございました。
（タイトルはもうかなり広まったのでそのままにしておきます） - 例ででてくるテーブル構成をそのまま利用されたりすると危ないと感じましたので注意を追加しました。