こんにちわ、みけです。
しょぼちむにデータモデル設計について教えてくださいの会 #syoboben - connpass
この勉強会に出られないので、データベース設計のことについて色々と言いたい放題を書くエントリーの2回目です。
本日はn対nのリレーションを作るときに絶対にやることを書きます。
なお、別に大した話ではありません。
n対nのリレーションにするなら、リレーションそのものをエンティティにする
前回のエントリーでテーブルにはマスター(リソース)とイベントがある的な話をしたわけですが、実はマスターについて何も語っていませんでした。
で、マスター(リソース)について書きたいところではあるのですが、リソースについて書こうとすると絶対にn対nのエンティティ間の関連が出てきてしまうので、先にn対nの話をしておきたいと思った次第です。
n対nの関係の例
ここでは例として、ある個人スポーツのアスリートと所属の関連の話をします。
一般的に個人スポーツのアスリートは大抵所属先を持ちます。
所属先との関連はオラクルの黒本(?)によく出てくるemp表とかでの例でよく出てきます。
その例に基づいてアスリートのテーブルは次のように作られたりします。
アスリート
| カラム名 | データ型 |
|---|---|
| アスリートID | 数値 |
| アスリート氏名 | 文字列 |
| 所属先ID | 数値 |
| 登録日 | 日付 |
| 所属先登録年度 | 数値 |
一方、所属先のテーブルは次のようになります。
所属先
| カラム名 | データ型 |
|---|---|
| 所属先ID | 数値 |
| 所属先名 | 文字列 |
| 登録日 | 日付 |
では、これらの二つのエンティティの関連はどうなっているでしょうか？
- アスリートから見て所属先は、所属先を転々とすることもあるから複数ありうる。またどこにも所属しないアスリートもいるので0の場合もある。
- 所属先から見てアスリートは、多くのアスリートを抱えることがあるから複数ありうる
ということから、アスリートと所属先の関係はn対nになります。
何が問題か？
ここも業務要件により問題が発生することがあります。例えば、
- ある年度の所属先も後で参照したい(川内優輝の学習院大学時代の10000mの記録を参照したい)
- ある年度のある所属先に所属していたアスリートの現在(もしくは最後)の所属先一覧がほしい(2007年の実業団女子駅伝で豊田自動織機が優勝した時の所属アスリートの現在(もしくは最後)の所属先一覧がほしい)
といった要件があったとします。するとアスリートは一人なのに、アスリートのレコードは複数件存在することになることが想定されます。また、所属先 + 登録年度でアスリートを検索するときなどのクエリーが複雑になったりします。
したがって、n対nの関係をもつエンティティをそのままにしておくのは、アプリケーションの複雑化を招いて、そのコードが腐っていきます。実際に僕が過去に参加したプロジェクトでもそういう腐っていくコードの中で理屈をつけてクエリーを作ってうまくいかないと言って23時まで頑張る人を何度も見てきました。
どうするべきか
エンティティ間にn対nの関係がある場合は、間に関連エンティティを入れて、1対n-n対1のように分離するのが得策です。
上記の例ではアスリート - アスリート所属 - 所属先という風にエンティティを分割します。
ER図で書くとこんな感じになります。
(エンティティの横に書いてある「E」「R」はそれぞれイベント、リレーションの略です)
なんか、このER図がTの字の形をしているから、T字型ER手法と呼ばれているとかなんとか(よく知らない)
このように、エンティティ間の関連そのものもエンティティとして保存するように僕は設計します。
(先ほどの頑張る人たちのプロジェクト、関連そのものをエンティティにするという考えがデータベース設計者の頭のなかになかったので、まあ大変そうでした)
(関連そのものをエンティティにするって、集合そのものを集合の要素にするというラッセルだか、なんとかさんの言う対角線論法につながりそうなものがありそうですが、まあ論理学も集合も詳しくないのでこれ以上考えない)
この設計によって、先ほどの要件なども比較的単純なクエリーにまとめられます。
- ある年度の所属先も後で参照したい
- 「アスリート所属」をアスリートIDと登録年度で検索して取得
- ある年度のある所属先に所属していたアスリートの現在(もしくは最後)の所属先一覧がほしい
- 「アスリート所属」を所属先IDと登録年度で検索してアスリートの一覧を取得
- 「アスリート所属」を先ほど取得したアスリートID、最も登録年度の大きい値で検索して所属先を取得
あ、サロゲートキーを「アスリート所属」につけたくなりましたね。なお、僕はわりと普通にサロゲートキーつけます。
以上、適当に書いた。理論はよく知らないし、集合概念にも詳しくないので、これが正しい(?)考え方かどうかは知らん。
