SQLアンチパターン@Rails①論理設計

メモ兼ねて。あと、なかなかRailsでは生SQL書かなかったり、例がPHPだったりだったのでもうちょっと実感を掴めたらなあとか。あんまり解説とかはない。
このアンチパターン、ところどころネーミングが西尾というか鎌池くさくて面白かった。
解釈にはあんまり自信がないので、間違いは指摘していただけると嬉しいです。

パターン1:信号無視（ジェイルウォーク）

@ migration file
t.string hoge_ids

@ Model logic
fuga.hoge.ids.split.each do |hoge_id|
  Hoge.find(hoge_id)
end

解決策

普通にhas_manyする

パターン2:素朴な木(ナイーブツリー)

@ migration file
t.integer parent_id

解決策

経路列挙(Path Enumeration)

@ migration file
t.string path(適当な区切り文字で自分親のidを全て保持)

先祖取得が早いが、早くも信号無視している

入れ子集合(Nested Set)

@ migration file
t.integer nsleft
t.integer nsright

@model logic
after_create do |record|
  record.nsleft = min_nsleft_my_children - 1
  record.nsright = max_nsright + 1
end

テクい構造。挿入が辛い。

閉包テーブル

@ tree paths table
t.integer ancestor_id
t.integer decendent_id

直接じゃない子孫のedgeも全てレコード化する。
path_length(感覚的にはdepth)カラムを足すことによって、直接の親子が容易に取得できる。
操作性はかなり良いが、関係テーブルのレコード数が結構すごいことになる。

パターン3:とりあえずID(IDリクワイアド)

ActiveRecord先生の思し召しなので仕方ない。

パターン4:外部キー嫌い（キーレスエントリ）

特に何もしてないと普通にこのアンチパターンになりううると思う。参照整合性を保つように努力が必要。

解決策

http://b.pyar.bz/blog/2014/10/21/foreigner/ にくわしい。
modelのdependent制約を使う。
http://www.ark-web.jp/sandbox/wiki/390.html
foreignerでもっと厳密に管理できるらしい。

汎用的な属性テーブルの使用

@ migration file
t.integer hoge_id
t.string attr_name
t.string attr_value

もちろん普通にやってたらこんなことしない。しかしRDDBのカラムに柔軟性をもたせたいと思うことはあるかもしれない。

解決策

• シングルテーブル継承
  Railsはtypeカラムを使ったSTIをサポートしてるので、それを使うと良い。
  http://ruby-rails.hatenadiary.com/entry/20141206/1417839458
  要するに多少のカラムの違いで概念的に似ているならテーブルは同じにしてしまって各カラムが部分集合になればよいという発想

• 具象テーブル継承
  普通にテーブル分ける。解決というか、諦めっぽい。

• クラステーブル継承
  http://qiita.com/yancya/items/a890ecfce4b29dde5861 とあるが、実際にはあんまりやらないほうがいいと思う。あくまでRailsのORM精神はテーブルとモデルが1:1対応してることが前提なので。

• 半構造化データ(LOB)

@ migration file
t.text json_attributes

などとして、一応決められたスキーマで構造化して柔軟にattributeを持つという話。苦渋っぽい。

パターン5:ポリモーフィック関連

一つに外部キーで複数のテーブルを参照する可能性が出てきたときの話。
RoRはポリモーフィックをサポートしているので、あんまりアンチパターン臭はないかも。

Railsガイド(http://railsguides.jp/association_basics.html)の例より
create_table :pictures do |t|
   t.string  :name
   t.integer :imageable_id
   t.string  :imageable_type
end

class Picture < ActiveRecord::Base
  belongs_to :imageable, polymorphic: true
end

class Employee < ActiveRecord::Base
  has_many :pictures, as: :imageable
end

class Product < ActiveRecord::Base
  has_many :pictures, as: :imageable
end

これにより、picture.imageableとかができる。

解決策

• 交差テーブルの作成

例えば上のpicturesとかの例だと、

create_table :employee_pictures do |t|
   t.integer :employee_id
   t.integer :picture_id
end

create_table :product_pictures do |t|
   t.integer :product_id
   t.integer :picture_id
end

みたいなテーブルを作る。typeカラムのメタ情報をテーブル構造に追い出した形となる。整合性的にはこちらが正しい。
交差テーブルをまたいでもuniqueになるように気を使う必要があるかもしれない。

• 共通の親テーブルの作成

イメージ的には、こんな感じ

create_table :employees_or_products do |t|
   t.integer :id(一応明示的に)
end

create_table :products do |t|
   t.integer employee_or_product_id
end

create_table :employees do |t|
   t.integer :employee_or_product_id
end

create_table :pictures do |t|
   t.integer :employee_or_product_id
end

AR図は三叉路みたいになる。ただしemployee_or_product -> product || employeeの関連を張ろうとすると結局ポリモーフィック使わないといけなくて、そこを諦めるなら良いけど、そうでないなら本末転倒感ある

パターン7:複数列属性(マルチカラムアトリビュート)

RDBは可変長の配列を持たないということに起因する問題。信号無視と似ている。たとえば複数のタグを持つ場合、直感的にはDBに

t.string tag1
t.string tag2
...

とやりたいという気持ちがある。が、列の数はハードコーディングするしかないので、tag"n"まで定義してもn+1個のtagが来たらどうしようもない。
いろいろな制約も利用できないし、よくない。

解決策

• 1(信号無視)と同じ。has_manyする。これ別章に分ける必要あったのだろうか。

パターン8:メタデータ大増殖(メタデータトリブル)

あんまりメタデータを増やすのも良くないという話。列とかテーブルとか。
年数ごとにテーブル分けたり、年カラム足したり、という例がある。

create_table :customers do |t|
   t.string :contact_type
   t.string :business_type
   t.integer :revernue2002
   t.integer :revernue2003
   t.integer :revernue2004
...
end

解決策

• パーティショニングを用いる

パーティショニングとはDBの機能に則ったテーブル分割のこと。水平と垂直の二種類がある。水平は行(つまりレコードレベル)を、垂直は列（つまりカラムレベル）で分割する。ちゃんとそういう機能があるんだから再発明しないでね、という話っぽい。
railsにおいては、このようなエントリがあった
ここにシャーディング(水平パーティショニングの同義語)用のgemやらスライドやらがまとまっている。が、どうにもパフォーマンス要求からのパーティショニングが多いよう。

• 従属テーブルを使う テーブルを分ける。この例なら

create_table :customer_revenues do |t|
   t.string :customer_id
   t.integer :revenue
   t.integer :year
end

で、customer has_many customer_revenuesとかすると良いと思う。

まとめ？

さっきからメタ情報はテーブル構造に出せとか、メタデータを増やしすぎるなとかどっちなんだみたいな感じになってるけど、結局銀の弾丸というものはない、という言葉に尽きると思う。この本はこういう設計をするとそれから先運用していくに従ってこういう問題と付き合っていく必要がある、というただのケースの羅列に過ぎないので、この本の記憶を元に実際の運用時はアプリケーションに最適な構造をちゃんと考えないといけない。

次は第二部の物理設計。