こんにちは。
アイスタイル Advent Calendar 2018 25日目の担当はDBAのsuzukitoです。
今年はテーブル設計の話を書こうと思います。
正規化を進めていくとテーブルが分割されていきますが、みなさん、どのようにして結びつけていますか？
テーブル同士を結びつけるには、次の二つの方法が考えられます。
- 一方の主キーを、もう一方に埋め込む、直接的・密結合な方法
- お互いの主キーをマッピングするテーブルを別途用意する、間接的・疎結合な方法
方法の違いによって、ビジネスルールがどのような影響を受けるのか。
モデルケースで見てみましょう。
モデルケース
架空の芸能事務所の所属タレント管理システムを例に考えてみます。
この事務所の所属タレントは、基本的にグループを結成しデビューする事とし、グループに所属するタレントはメンバーと呼ばれます。
- グループを管理するテーブル
- メンバーを管理するテーブル
これらを使って2つの方法を比較してみます。
二つのテーブルの結びつけ方
一方の主キーを、もう一方に埋め込む方法
グループの主キーをメンバーに埋め込んで関係を作ります。
この直接的・密結合な関係作りを選ぶと、データの発生順序に制約が生まれます。
メンバー登録時のグループ
メンバー登録時に、必ず所属グループを決める必要があるため、グループを先に登録する必要があります。
テーブル間で先行・後続の制約が生まれます。
デビュー前なのにグループは決められないよって事であれば、デビュー前のタレント全員が所属するグループ、例えば「ジュニア」という名前のグループを作成しておき、メンバーの新規登録時は全員「ジュニア」に所属する形をとるのが良いでしょう。
メンバーテーブルのグループIDにNULLを許容すると、この制約を回避できます。この場合、グループIDがNULLのメンバーは、グループ無所属のメンバーになります。
しかし、NULLを値として扱い意味を持たせると、本当にNULLを使いたい時に使えなくなるので、この方法はオススメしません。
メンバーの所属できるグループ数
メンバーは一つのグループにのみ所属できます。
グループID列は一つしか無いからです。
複数のグループに所属できるようにするため、メンバーテーブルに、グループ1、グループ2…なんて列を作るのは止めましょう。もし、そうする必要があるならば、このテーブル設計は、この芸能事務所のビジネスルールと合っていません。
メンバーのグループからの脱退
グループから脱退させたい場合、新しいグループへ加入させる必要があります。
しかしデビュー後なので「ジュニア」に戻すわけに行きません。
この芸能事務所では所属グループなしでのソロ活等を認めないと言うルールを表しています。
グループ脱退と同時に事務所から退所する決まりがありそうです。
グループに所属しないメンバーを認める場合は「無所属」や「ソロ」などの概念的なグループを作る必要があります。
まとめ
この方法は強い制約・束縛が生まれます。
それが必要な場合には向いている設計です。
お互いの主キーをマッピングするテーブルを別途用意する方法
グループとメンバーは独立して登録を行い、必要に応じてお互いの主キーをマッピングしてマッピングテーブルに登録します。
この間接的・疎結合な関係を作りを選ぶと、グループとメンバーの独立性が高くなります。
メンバー登録時のグループ
メンバーテーブルもグループテーブルも独立しているため、必ずグループに参加しなくてはならないと言う制約は無くなります。
制約がなくなった事により、新しく事務所に所属したのに「ジュニア」に登録漏れがあったと言う事態が起こりえます。
メンバーの所属できるグループ数
メンバーは複数のグループに加入ができます。
この芸能事務所では、グループから選抜したメンバーを組み合わせてユニットを作ることがあるとします。その場合、マッピングを使った結びつけであれば、このような事態にも対応出来るようになります。
複数のグループへの加入を制限したい場合は、マッピングテーブルでメンバーIDにユニーク制約を作成すればよいです。
グループIDとメンバーIDでユニーク制約を作成すれば、同じ組合せの重複登録を防ぐことができます。
メンバーのグループからの脱退
グループから脱退する場合は、マッピングテーブルからそのメンバーとグループの組合せを削除するだけです。
ソロ活動も認められ、グループ脱退と事務所の退所がイコールでなくなります。
まとめ
この方法はテーブル同士の独立性が高く自由に結びつけられるため、オールマイティに使える方法です。
同一のテーブルの結びつけ方
結びつけるのはなにも別のテーブル同士だけではありません。
同一のテーブル内の関係を作ることもあります。
仮にこの芸能事務所ではメンター制度があるとします。
メンバーを管理するテーブル内のデータの結びつけ方を、2つの方式で比較してみます。
一方の主キーを、もう一方に埋め込む方法
主キーを同一テーブル内に埋め込んで関係を作ります。
メンバー登録時のメンター
先行・後続の制約があるため、先に登録されているメンバーしかメンターにはできません。
メンバーに登録出来るメンター数
選べるメンターは一人だけです。
メンターの退所
メンターが事務所を退所する場合は、そのメンバーがメンターをしているメンバーのレコードを全て別のメンバーに変更する必要があります。
まとめ
この方法は、リレーショナルモデルでツリー構造を作るよく知られた方法です。ツリーの組み合わせに制限があり、ツリー構造のメンテナンス性に難があります。
お互いの主キーをマッピングするテーブルを用意する
メンバーの主キーをマッピングテーブルに埋め込んで関係を作ります。
メンバー登録時のメンター
メンター登録を必須にする事ができません。
メンバーに登録出来るメンター数
複数のメンターを登録出来ます。
メンターの退所
事務所を退所するメンバーのレコードをマッピングテーブルから削除します。
まとめ
この方法は、閉包テーブルという名前でよく知られた方法です。
関係を外部テーブルに切り出した事で表現力が増しています。
最後に
テーブルの結びつけ方によって、ビジネスルールが影響を受けることを見て取れたのではないでしょうか？
ここで述べた内容は、データベース設計方法「TM2.0」の内容をベースにしています。
チュートリアルが書かれたPDFが配布されているので、興味がわいたら方、ぜひ読んでみて下さい。
Enjoy! Database Life!
