ブログトップ 記事一覧 ログイン 無料ブログ開設

Strategic Choice

2016-06-29

[]ポリモーフィック・アソシエーション

ポリモーフィック・アソシエーション(Polymorphic Associations, ポリモーフィック関連)

どういうこと?

【したいコト】

複数の親テーブルを参照します。しかし、各レコードが、どちらの親を参照しているか、わかりません。

f:id:asakichy:20160614145610p:image


【やらかしたコト】

「ポリモーフィック関連*1」というテクニックを利用します。ポリモーフィック関連は、レコードが参照する親テーブル名を格納する列を追加します。

f:id:asakichy:20160614145608p:image

どうしてヤル?

親が複数あるような構造でも、親テーブル名を識別として使って、親子を結合することが出来ます。

どうしてダメ?

  • 参照整合性が使用できない
    • 複数の親テーブルを参照する外部キーを宣言することはできません。
  • 両方の親テーブルを結合しにくい
    • 外部結合を使用すれば可能だが、ところどころNULLになる。

どうすれば?

「ポリモーフィック関連の反転」を行います。

ポリモーフィック関連では、本来あるべき関連が「逆さま」になっています。そこで、参照を逆に考えるのです。具体的には、交差テーブルを、それぞれの親子の間に挟むことで実現します。

f:id:asakichy:20160614150542p:image

これで問題は一気に解決します。

  • 参照整合性が使用できます。
  • 親とシンプルに結合できます。
  • 両方の親とも比較的シンプルに結合できます。

また、「共通親」を持つことでも、同様の効果を得られます。

f:id:asakichy:20160614150541p:image

名前の由来

通常のリレーションシップを超え、多数のテーブルと関連を持とうとすることから、「ポリモーフィック関連」と名付けられました。

教訓

テーブル間の関連(リレーションシップ)には、参照元テーブルと参照先テーブルが常にそれぞれ1つしかないことを忘れないようにしましょう。

*1:「プロミスキャス・アソシエーション(Promiscuous Association:無差別な関連)」と呼ばれることもあります。

2016-06-28

[]エンティティ・アトリビュート・バリュー

エンティティ・アトリビュート・バリュー(Entity-Attribute-Value, EAV, 汎用的な属性テーブル)

※「オープンスキーマ」「スキーマレス」「名前/値ペア」と呼ばれることもあります。

どういうこと?

【したいコト】

可変属性のデータをテーブルに格納します。可変属性とは、オブジェクト指向で継承を使用しているようなデータ構造のことです。以下のようなクラス構造の場合、親クラスの属性は共通ですが、子クラスの属性はそれぞれ異なっています。


f:id:asakichy:20160614140107p:image


これを素直に一つのテーブルに入れようとすると、データ格納効率が悪い設計となり、これを避けたいと考えています。

f:id:asakichy:20160614140106p:image

また、このようなデータ構造においては、属性が増減するような変更が発生しがちです。この変更の度に、テーブル設計を変更したくないと考えています。


【やらかしたコト】

汎用的な属性テーブルを別途追加します。これは、属性(列)を「行」に格納することです。

f:id:asakichy:20160614140118p:image

どうしてヤル?

汎用的な属性テーブルを用意することで、以下のメリットがあると期待します。

  • 両方のテーブルの列数を減らせます。
  • 属性が増えていっても、列数を増やす必要がありません。
  • NULLだらけのテーブルになることを防げます。

一見すると、設計が改善されたように思えます。

どうしてダメ?

  • 属性が取得しにくい
    • 元の一つの表の時のように、「シンプル」かつ「わかりやすい」クエリで属性を取得できなくなります。
  • 整合性を保ちにくい
    • 必須属性を設定できません。
    • 値は文字型となり、データ型を使用できません。
    • 参照整合性を使用できません。
    • 同じ属性が重複して登録される可能性があります。
  • 元のテーブルの形に再構築しにくい
    • 属性1つに付き1外部結合が必要になります。
    • 属性が多くなると長いSQL文になり、多くの結合をもつ実行効率の悪いSQLになります。

EAVは、いわば「スキーマレス」です。真にスキーマレスなデータを扱いたいのであれば、非リレーショナルな技術を使用するべきです。

どうすれば?

以下いずれかの設計を選択します。

  • シングルテーブル継承(Single Table Inheritance, STI)
  • 具象テーブル継承(Concrete Table Inheritance)
  • クラステーブル継承(Class Table Inheritance)
  • 半構造化データ

【シングルテーブル継承】

すべてのタイプの属性を個別の列に格納して、関連するすべてのサブタイプを1つのテーブルに格納します。加えて1つの属性列を、その行がどのサブタイプであるかを定義するために用います。シンプルで扱いやすい構造です。

f:id:asakichy:20160614144428p:image

ただ、欠点もあります。

  • データ格納効率が悪い
    • 属性は、共通のものがありますが、多くの属性はサブタイプ固有のものです。
    • そのサブタイプに必要のない属性値はNULLとなり、値がバラバラに点在します。
  • サブタイプを追加しにくい
    • 新しいタイプの属性が既存のテーブルに存在しない場合、新属性列を追加しなければなりません。
  • メタデータがない
    • どの属性がどのサブタイプに所属するかを定義するメタデータがありません。
    • 属性とサブタイプの対応関係をアプリケーションが管理しなければなりません。

シングルテーブル継承は、サブタイプの数とサブタイプ固有の属性の数が少なく、アクティブレコードのような単一のテーブルに対するデータベースアクセスパターンを使う場合に適しています。


【具象テーブル継承】

サブタイプごとにテーブルを作成します。すべてのテーブルは、メインタイプに共通する属性と、それぞれのサブタイプに固有の属性を含んでいます。

サブタイプに存在しない属性列を格納する必要がありません。また、テーブルと具象オブジェクトが1:1で対応しているので、相互変換しやすくなります。

f:id:asakichy:20160614144427p:image

ただ、欠点もあります。

  • 共通属性を追加しにくい
    • メインタイプに属性を追加すると、すべてのテーブルを変更することになります。
  • メタデータがない
    • ある属性が、メインタイプとサブタイプのどちらに属しているかを示すメタデータはありません。
  • サブタイプ不問の検索がしにくい
    • すべてのレコードを、どのサブタイプであるかにかかわらず取得したい場合、各サブタイプが別々のテーブルに格納されているために、処理は複雑になります。

具象テーブル継承は、すべてのサブタイプをまたいだ検索を実行する頻度が低い場合に適切です。


【クラステーブル継承】

すべてのサブタイプに共通する属性を含むメインタイプのテーブルを1つ作ります。次に、サブタイプごとに1つずつ追加のテーブルを作成し、メインタイプのテーブルに対する外部キーの役割を持つ主キーを設定します。つまり、オブジェクト指向の継承を摸倣するという方法です。

メインタイプの属性のみを参照する限り、すべてのサブタイプにまたがる検索を効率良く行えます。メインタイプに属性を追加するのも、対応するテーブルがあるので簡単です。

f:id:asakichy:20160614144426p:image

ただ、欠点もあります。

  • 相互変換しにくい
    • 1つのオブジェクトが複数のテーブルに対応するので、相互変換が面倒です。
  • サブタイプ属性の検索がしにくい
    • いったんすべて外部結合する必要があるので、処理は複雑になります。

クラステーブル継承は、すべてのサブタイプに共通する列を参照するクエリが頻繁に実行されるときに適しています。


【半構造化データ】

LOB列を追加し、XMLやJSONなどの形式で属性名と値の組を格納します。このパターンは「シリアライズLOB」とも言います。

この設計は、拡張性が極めて高くなります。新しい属性を、いつでもLOB列に格納できます。

f:id:asakichy:20160614144648p:image

ただ、欠点もあります。

  • SQLが使用できない
    • LOB列では、行の絞り込み、集約計算、ソートなどの処理のためにLOB内の個別の属性を選択することできません。
    • これをするには、まずLOB全体を1つの値として取得し、属性に分解して解釈するアプリケーションコードを書かなくてはいけません。

この設計は、サブタイプの数を制限できない場合や、新しい属性を随時定義するための高い柔軟性が必要な場合に適しています。

名前の由来

可変属性のデータをテーブルに格納したいがために、リレーショナル設計を放棄し、「汎用的」な属性専用のテーブルを設計してしまうことから、「エンティティ・アトリビュート・バリュー」と名付けられました。

教訓

メタデータは、メタデータのために用いましょう。

関連

2016-06-27

[]キーレスエントリ

キーレスエントリ(Keyless Entry, 外部キー嫌い)

どういうこと?

【したいコト】

データベース設計を極力シンプルにします。

【やらかしたコト】

外部キー制約を使用しません。

f:id:asakichy:20160613131058p:image

どうしてヤル?

外部キーによる参照整合性制約を使わないことを推奨する考え方があります。理由としては、以下が挙げられます。

  • データの更新が、参照整合性制約と衝突する。
  • データベース設計の柔軟性が極めて高いので、参照整合性制約をサポートできない。
  • データベースが外部キーのために作成するインデックスが、パフォーマンスに影響する。
  • 外部キーをサポートしないデータベース製品を使っている。
  • 外部キーを宣言する構文を調べなければならない。

どうしてダメ?

  • アプリケーションが整合性を保つのは困難
    • 追加・削除・更新それぞれについて、参照先のテーブルを調べ、時には同期をとり、時にはエラーにしなければなりません。
    • これをミスなく完璧に行うというのは、あまり現実的でない前提条件です。
  • アプリケーションのミスを要調査
    • アプリケーションが完璧でなかった場合、それをフォローするための調査ツールが別途必要になります。
    • このツールの使用頻度やパフォーマンス、ツールのシステムへの負荷、不整合が見つかった時の復旧など、課題は山積みです。
  • アプリケーションのメンテナンスが困難
    • 仮に整合性を保つコードができても、保守では必ず変更が入ります。
    • 複数個所に散らばった整合性のためのコードを、漏れなくミスなく修正するのは至難の業です。

どうすれば?

外部キー制約を宣言します。

f:id:asakichy:20160613131057p:image

外部キーは、参照整合性制約によって整合性を強制し、うっかりミスを防止できます。データ不整合を検出してから修正するのではなく、データベースへの登録時点でこれらのミスを阻止できます。

外部キーは、不要なアプリケーションコードを書く手間を省きます。データベースを変更した場合でも、すべてのコードが同じように適切に動作することを保証します。コードの作成だけでなく、修正や保守の時間も大幅に削減してくれます。

外部キーは、カスケード更新と呼ばれる機能を持ちます。カスケード更新を使うと、親の行の更新や削除が可能になり、さらにその行を参照しているあらゆる子の行もデータベースが適切に処理してくれるようになります。

外部キーは、制約によって多少のオーバーヘッドが生じるのは事実です。しかし、他の選択肢と比べると、外部キーの方が圧倒的に効率的です。たとえば、「挿入」「更新」「削除」の前に、チェックのためにクエリを実行する必要がありません。また、複数テーブルの変更時の不整合を防ぐために、テーブルをロックする必要がありません。さらに、「孤児」が生じてしまうことがないので、整合性を再調整するスクリプトを作成する必要がありません。

名前の由来

キーレスエントリとは、自動車などに使われている、カギを鍵穴に差さないで、外部からリモコンによって施錠・開錠する仕組みのことです。キーを使用しないで車に入るように、外部キーを使用しないでデータを入れるというところから「キーレスエントリ(外部キー嫌い)」と名付けられました。

教訓

データベースでのミスの発生を未然に防ぐために、外部キー制約を用いましょう。

2016-06-24

[]IDリクワイアド

IDリクワイアド(ID Required, とりあえずID)

どういうこと?

【したいコト】

すべてのテーブルに主キー(Primary Key, PK)を設けます。

【やらかしたコト】

すべてのテーブルに「ID」列を追加し、それを主キーとします。この列は、いわゆる「シュード(疑似)キー」と呼ばれるもので、シーケンスを使って実装します。

f:id:asakichy:20160613130546p:image

どうしてヤル?

書籍や雑誌記事の啓蒙、フレームワークの規約などの影響で、すべてのテーブルに「ID」という主キー列が存在しなくてはならないという考えが普及しています。これが慣習となり、盲目的に従っているケースが多くあります。

どうしてダメ?

  • 冗長なキーが作成されてしまう
    • 別の列がナチュラルキーとして使えるなら、ID列は冗長です。
  • 重複行を許可してしまう
    • 複合主キーのテーブルを、無理やりID列主キーにりリプレイスすると、元の複合キーの組み合わせが常に一意であることを保証しなくなってしまいます。
  • キーの意味がわかりにくくなる
    • SQLに「ID」が頻出して混乱します。
  • 結合時「USING」が使えない
    • 従属テーブル側の外部キー列には、参照する主キーと同じ名前は使えません。

どうすれば?

主キーとは「ID列」である、と決めつけないで、状況に応じて適切に設計します。

良い設計のためには、規約に縛られて柔軟性を欠いてしまわないようにすることが大切です。確かに、ORMフレームワークの多くは、「ID」という名前の「シュード(疑似)キー」が使われることを規約としています。しかし、技術的には、この規約を上書きして、別の名前を宣言することも可能です。主キーは、都度最適なものを検討して、無理に規約に縛られないようにします。

主キーは、わかりやすい名前を付けるようにします。

主キーの名前は、主キーが識別する対象のテーブルを表現するべきです。さらに、主キーの名前は、他のテーブルを含めた全体で一意となるべきです。片方の主キーがもう片方の主キーを参照する外部キーではない限り、複数のテーブルで主キーに同じ名前を使うべきではありません。たとえば、社員テーブルと部署テーブルがあり、両方にID列があった場合、両方の名前を「ID」とするのではなく、「社員ID」「部署ID」と命名します。

名前の由来

「規約で決まっているから」「みんなやっているから」などの理由で、必達(Required)事項だと思い、「ま、とりあえずid」となってしまうことから「IDリクワイアド(とりあえずID)」と名付けられました。

教訓

規約は、役立つと思える場合のみ従いましょう。

2016-06-23

[]ナイーブツリー

ナイーブツリー(Naive Trees, 素朴な木)

どういうこと?

【したいコト】

階層(ツリー状)構造をテーブルに格納します。階層構造の代表例には、組織図や掲示板のスレッドなどがあります。

f:id:asakichy:20160613114620p:image


【やらかしたコト】

自分の親を格納する列を用意します。この列は、自分のテーブルに外部キーを設定します。このような設計を「隣接リスト(Adjacency List)」と呼びます。

f:id:asakichy:20160613114750p:image


どうしてヤル?

隣接リストは、階層的なデータの格納に用いられる、最も一般的な設計です。

どうしてダメ?

  • 階層の検索がやりにくい
    • 階層の深さに制限のないSQLが書けません。
    • アプリケーション側に任せるしかありませんが、パフォーマンスに問題が発生します。
  • 階層のメンテナンスがやりにくい
    • サブツリー全体を削除する場合、すべての子孫を特定するために複数回クエリを実行し、最下層から順番に子孫を削除する必要があります。この順番を守らないと参照整合性制約違反になります。
    • 中間の階層を削除した場合、削除対象を消す前に、削除対象の子供達の親を、削除対象の親に変更する必要があります。この順番を守らないと参照整合性制約違反になります。

どうすれば?

隣接リストの代わりに、以下いずれかのモデルを選択します。

  • 経路列挙(Path Enumeration, Materialized Path)
  • 入れ子集合(Nested Set)
  • 閉包テーブル(Closure Table)

最もエレガントでお勧めなのが「閉包テーブル」です。


【経路列挙】

先祖の系譜を表す文字列を属性として格納します。ファイルシステムでいうところの絶対パスにあたります。

f:id:asakichy:20160613122115p:image

  • 階層の検索がやりやすい
    • LIKEによるパターン検索を行えば、先祖や子孫を取得するのが簡単になります。
    • たとえば、企画部に所属する課を調べたければ、「LIKE '海外事業部/企画部/%'」とします。
  • 階層のメンテナンスがやりにくい
    • ほぼ、アプリケーションに頼ることになります。
  • 値の検証がやりにくい
    • どんな値でも、文字列として格納することになります。
  • 列長の設計がやりにくい
    • 階層数を予想するのは困難です。

【入れ子集合】

直近の親ではなく、子孫の集合に関する情報を各行に格納します。

f:id:asakichy:20160613122224p:image

この情報は、各行に「左」「右」と呼ばれる数値で表されます。各行にはルールに基づいて「左」「右」の値が割り当てられます。具体的には、左値には、下の階層にあるすべての要素が持つ値より小さな値が、右値には、下の階層にあるすべての要素が持つ値より大きな値が与えられます。

「左」「右」の値は、「深さ優先探索」を用いることで、簡単に割り当てられます。すなわち、根から下に向かって降りていきながら順番に「左」値を割当て、葉から上に向かって「右」値を割り当てます。

f:id:asakichy:20160613122225p:image

  • 階層の検索がやりやすい
    • 範囲検索を行えば、先祖や子孫を取得するのが簡単になります。
    • たとえば、企画部に所属する課を調べたければ、企画部の左値(2)と右値(7)に含まれる左値(3と5がヒット)を範囲検索します。
  • 階層の削除がやりやすい
    • 中間層が削除されても、(番号の連続性は維持されませんが)仕組上親子関係が維持されます。
  • 階層の挿入や移動がやりにくい
    • 左右値の再計算が必要になります。

【閉包テーブル】

閉包テーブルを別途設けて、直接の親子関係だけではなく、階層全体の親子関係を格納します。

f:id:asakichy:20160613122353p:image

閉包テーブルの各行には、先祖/子孫関係を共有する要素の組み合わせを格納します。これは、ツリー上の離れた位置にある要素も含めた、すべてのノードが対象になります。また、自分自身を参照する行も追加します。

f:id:asakichy:20160613122352p:image

  • 階層の検索がやりやすい
    • 閉包テーブルを検索すれば、先祖や子孫を取得するのが非常に簡単になります。
    • たとえば、企画部に所属する課を調べたければ、先祖列が企画部であるという条件で検索するだけです。
  • 階層のメンテナンスが比較的やりやすい
    • 閉包テーブルだけを相手にすればよくなります。

名前の由来

ツリー構造を表現するのに、隣接リストは、確かにシンプルなモデルです。しかし、思慮が浅い、素朴(ナイーブ)な解決策であるとも言えるので、「ナイーブツリー」と名付けられました。

教訓

階層構造はエントリと関連(リレーションシップ)から成り立ちます。

これを念頭に、行う作業に合わせて最適な設計手法を選択しましょう。