データベース論理設計のアンチパターン

おはこんばんちは。

がんばってブログを書きたいので、ちょうどいま読んでいるSQLアンチパターンという本を、噛み砕いて離乳食くらいの柔らかさにして晒してみます。すでに読んだ人はいますぐそっ閉じ、まだ読んでない人は、こちらも同様にそっ閉じしてお風呂に入ってすぐ寝ましょう。

SQLアンチパターン

SQLアンチパターン

かなり圧倒的に当たり前のことしか書いてないんですけど、そういう本こそが良書だっておじいちゃんの息子の息子が言ってた気がします。

アンチパターンが全部で25個、カッチョイイタイトルとともに紹介されています。「目的」「アンチパターン」「解決策」をそれぞれ示します。本書だと、「アンとパターンを使ってもいい場合」みたいなのも紹介されてるので、気になる人はこんなクソブログを読む前に本を読んでください。

今回はとりあえず、Ⅰ部のデータベース論理設計のアンチパターンのところだけ。25個中の8個です。本だと訳が簡潔すぎたりテーブルの実例がなかったりして、ヤドカリと同程度の知能を持つぼくにはイメージが困難だったので、言葉や実例は勝手に考えたり追加したりしています。わかりにくいとか間違ってるとかイケメンとか感想でもいただけると嬉しいです。

1. Jaywalking

目的

複数の値を持つ属性を格納する

「タグ」とか「ラベル」という概念がわりかし一般的かと思います。「かばお」という対象に対して、「ラベル」という属性があり、「アホ」「バカ」「カス」みたいな値を持たせる場合です。だれがアホバカカスじゃい!!!!

アンチパターン

カンマ区切りのフォーマットのリストを格納する

Actors

name tags
ガッキー 天使,最高,結婚したい,…
星野源 うらやましい,入れ替わりたい,…
select tags from Actors
where name = 'ガッキー'

解決策

交差テーブルを作成する

Actors

actor_id user_name
1 ガッキー
2 星野源

Tags

tag_id tag_name
1 天使
2 最高
3 うらやましい
4 結婚したい

ActorsTags

actor_id tag_id
1 1
1 2
1 4
2 3
select t.name from Tags as t
    inner join ActorsTags as at on t.tag_id = at.tag_id
    inner join Actors as a.actor_id = at.tag_id
where a.name = 'ガッキー'

当たり前ですね。当たり前体操。完全に。それでしかない。

2. Naive Trees

目的

階層構造を格納し、クエリを実行する

ツリー構造をどう表現するか?というお話です。 例えば、俳優のファンサイトみたいなのがあって、それぞれの俳優にコメントがつけられて、さらにそのコメントにもコメントがつけられる、みたいな作り。Facebookとかもそんなかんじですね。

アンチパターン

常に親のみに依存する

最もシンプルに、各コメントが、直近の親のみを知っている構造です。

Comments

commend_id parent_id comment
1 NULL 可愛いです。
2 1 いや、天使だと思います。
3 2 いやいや、女神でしょう。
select * from Comments c1
     left outer join Comments c2
         on c2.parent_id = c1.comment_id

この例だと、2階層までしか取得できません。階層が増えると、SQLjoinがどんどん増えていきます。どんどんドーナツです。また、ノードの削除もしんどいです。あるノードが削除されると、その子たちもupdateする必要があります。ドーナツです。

解決策

代替ツリーモデルを使用する

RDBでツリーを表現するために、いろいろなモデルがあります。

Path Enumeration

各コメントは、祖先までのパスを文字列として持ちます。絶対パス的な。

Comments

commend_id path comment
1 1/ 可愛いです。
2 1/2/ いや、天使だと思います。
3 1/2/3/ いやいや、女神でしょう。

path列に対してパターン比較すれば先祖を取得できます。

select * from Comments as c
where '1/2/3/' like c.path || '%';

引数を逆にすれば子孫も取得できます。
ただし、データベースの制約レベルでパスの正確性を保証できない、長さ制限があるなどの問題もあります。

Nested Set

直近の親ではなく、子孫の集合を各ノードに格納します。

commend_id nsleft nsright comment
1 1 14 可愛いです。
2 2 5 いや、天使だと思います。
3 3 4 いやいや、女神でしょう。

ここで説明するのはとてつもなくめんどくさいので、我らがミック大先生のご説明

SQLで木と階層構造のデータを扱う(1)―― 入れ子集合モデル

を読んでみてください。

Closure Table

先祖と子孫の組み合わせを保持するテーブル(=閉包テーブル)を利用する方法です。

TreePaths

ancestor descendant
1 2
2 3
3 4

みたいな。詳しくは、このへんを読んでみてください。

3. ID Required

目的

主キーの規約を確立する

日本語でおkってかんじですが、要するに、ORMとデータベースで主キーの扱いにずれがあるときに、どっちにどうやってあわせんの!?というお話です。

アンチパターン

ORMの規約に従い、すべてのテーブルに「id」列を用いる

id actor_id name
1 1 ガッキー
2 2 星野源

ORMにはidというフィールドがあるからまあid列は作っといて、実際に使うにはわかりにくいからactor_idを追加した、みたいな。無駄無駄無駄無駄無駄無駄無駄無駄無駄無駄無駄無駄ァ!!!!!!!!!!!

解決策

状況に応じて適切に調整する

普通!!!!せやな!!!!!!

actor_id user_name
1 ガッキー
2 星野源

列名はわかりやすく!ORMの規約に縛られない!
RoRとか上書きできますんで。

class User < ActiveRecord::Base
    self.primary_key = 'actor_id'
end

4. Keyless Entry

目的

データベースのアーキテクチャを単純化する

なぜか抽象的すぎる日本語になっていますが、外部キーを使うの?どうなの!?というお話です。

アンチパターン

外部キー制約を使用しない

うん。

解決策

外部キー制約を宣言する

せやな。

5. Entity-Attribute-Value

目的

継承関係のあるデータを表現する

オブジェクト指向で設計されたアプリケーションにおけるオブジェクトと、データベースをいいかんじに対応づけたいかんじです。

class Person {
    public string actorId;
    public string name;
}

class Actor : Person {
    public string masterpieceTitle;
}

class Engineer : Person {
    public string mainLanguage;
}

アンチパターン

汎用的な属性テーブルを使用する

People

person_id name
1 ガッキー
2 星野源
3 かばお

PersonAttributes

actor_id attribute_name attribute_value
1 代表作 逃げるは恥だが役に立つ
2 代表作 真田丸
3 主要言語 C#

データ整合性を保つ観点で、必須属性や、SQLのデータ型が使えないです。boolとかdateとかで持たせたいところをstringで持たせる必要があります。どんなattributeが設定されるかわかりませんからね。

あと、すべての属性を行の一部分として取り出すためには、各属性のjoinが必要になります。以下のとおり。

select 
    p.person_id,
    p.name,
    pa1.attribute_value as hair_style,
    pa2.attribute_value as instrument
from Actors as a
    left outer join PersonAttributes as aa1
        on p.person_id = pa1.person_id and pa1.attribute_name = '髪型'
    left outer join PersonAttributes as aa2
        on p.person_id = pa2.person_id and pa2.attribute_name = '楽器' 

attributeの種類が増えるたびにjoinが増えます。つらいですね。

解決策

サブタイプのモデリングを行う

シングルテーブル継承

すべてのサブタイプを一つのテーブルに格納します。

Persons

person_id name person_type masterpiece_title main_language
1 ガッキー actor 逃げるは恥だが役に立つ NULL
2 星野源 actor 真田丸 NULL
3 かばお engineer NULL C#

具象テーブル継承

サブタイプごとにテーブルを作成します。

Actors

person_id name masterpiece_title
1 ガッキー 逃げるは恥だが役に立つ
2 星野源 真田丸

Engineers

person_id name main_language
3 かばお C#

クラステーブル継承

オブジェクト指向における継承を模倣します。

Persons

person_id name
1 ガッキー
2 星野源
3 かばお

Actors

person_id masterpiece_title
1 逃げるは恥だが役に立つ
2 真田丸

Engineers

person_id main_language
3 C#

半構造化データ

一般にSerialized LOBと呼ばれるアレです。以下はjsonでもたせる例。

Persons

person_id name person_type attributes
1 ガッキー actor masterpiece_title : 逃げるは恥だが役に立つ
2 星野源 actor masterpiece_title : 真田丸
3 かばお engineer main_language : C#

6. Polymorphic Associations

目的

複数の親テーブルを参照する

アンチパターン

二重目的の外部キーを使用する

Actors

person_id name masterpiece_title
1 ガッキー 逃げるは恥だが役に立つ
2 星野源 真田丸

Engineers

person_id name main_language
3 かばお C#

Comments

comment_id person_type person_id comment
1 actor 1 可愛い
2 actor 1 最高
3 actor 1 天使
4 actor 2 歌がうまい
5 actor 2 かっこいい
6 engineer 3 ポンコツクズ野郎

外部キーはテーブルを一つのみしか指定できないため、Comments.actor_idGakkies.actor_id HoshinoGens.actor_id に依存しているのにもかかわらず、外部キー制約を指定できない。参照整合性のやばみが深いです。

解決策

関連(リレーションシップ)を単純化する

参照を逆にする

まず、依存関係が逆になってますよね、というやつです。本質的に何を問題としているかを考えるべきです。

交差テーブルの作成

Actors

person_id name masterpiece_title
1 ガッキー 逃げるは恥だが役に立つ
2 星野源 真田丸

Engineers

person_id name main_language
3 かばお C#

Comments

comment_id comment
1 可愛い
2 最高
3 天使
4 歌がうまい
5 かっこいい
6 ポンコツクズ野郎

ActorsComments

comment_id person_id
1 1
2 1
3 1
4 2
5 2

EngineersComments

comment_id person_id
6 3

共通の親テーブルを作成

Actors

person_id name masterpiece_title
1 ガッキー 逃げるは恥だが役に立つ
2 星野源 真田丸

Engineers

person_id name main_language
3 かばお C#

Comments

person_id comment_id comment
1 1 可愛い
1 2 最高
1 3 天使
2 4 歌がうまい
2 5 かっこいい
3 6 ポンコツクズ野郎

Persons

person_id
1
2
3

Actors,Engineers,Commentsのperson_idは外部キーとしてPersons.person_idを持てば、参照整合性を保証できます。

シンプルなクエリで特定のコメントが参照している俳優またはエンジニアを取得できます。

select * from Comments as c
    left outer join Actors as a using (person_id)
    left outer join Engineers as e using (person_id)
where c.comment_id = 3

特定の俳優についたコメントもシンプルに取得できます。

select * from Actors as a
    inner join Comments as c using (issue_id)
where a.person_id = 2

7. Multicolumn Attributes

目的

複数の値を持つ属性を格納する

アンチパターン

複数の列を定義する

Persons

person_id name tag1 tag2 tag3
1 ガッキー かわいい 天使 NULL
2 星野源 かっこいい NULL NULL
3 かばお NULL NULL NULL

値の検索がしんどい

特定のタグが付いた人物を取得しようとすると、目的のタグがどの列に入っとるかわからんから、3列ぜんぶ取得せなあかんで。

select * from Persons
where tag1 = 'かわいい'
    or tag2 = 'かわいい'
    or tag3 = 'かわいい'

値の追加と削除がしんどい

ある人物に対してタグを追加するときは update するんだけど、どの列が空いてるかチェックせなあかんで。 NULLIF 使うで。

一意性が保証できない

ガッキーに「かわいい」タグを2つつけたい気持ちはあれど、無意味だから1つに限定したいですよね。でもできません。

増加する値の処理がしんどい

ガッキーには「かわいい」「天使」「女神」以外にも、「癒やし」「結婚したい」など、つけたいタグが3つにとどまりませんよね。こういう場合に、列を拡張していく必要があります。そのたびに、アプリケーションコードの修正が必要です。

解決策

従属テーブルを作成する

Persons

person_id name
1 ガッキー
2 星野源
3 かばお

Tags

person_id tag
1 かわいい
1 天使
2 かっこいい

当たり前〜当たり前〜当たり前体操〜。

8. Metadata Tribbles

目的

スケーラビリティを高める

ガッキーに対するコメントが爆発的に増加すると、パフォーマンスがどんどん落ちます。それをどうするか。ガッキーの圧倒的人気から、データベースをどう守るか。

アンチパターン

テーブルや列をコピーする

Comments テーブルの行数が問題になるなら、例えばその日付でテーブルを分割すれば、検索自体は速くなりそうです。

Persons

person_id name
1 ガッキー
2 星野源
3 かばお

Comments_2015

person_id comment date
1 かわいい 2015-01-04
1 天使 2015-03-21
3 ゴミクズ 2015-08-11

Comments_2016

person_id comment date
2 かっこいい 2016-09-02
1 神様 2016-11-30
1 癒やし 2016-03-02

Comments_2017

person_id comment date
1 人間国宝 2017-04-20
3 愚か 2017-07-07
1 生まれてきてくれてありがとう 2017-12-09

間違って2016年のコメントが2017年のテーブルに混入しないように、など、データの整合性を管理する必要があります。

  • insertdate のカラムに対して check制約をつける
  • updatedate の年が間違っていた場合、 delete して insertする

「すべてのコメントを取得したい」という場合に、Comments_20XX たちを union することになりますが、テーブルが増えるたびにそのSQLを修正する必要があります。

解決策

パーティショニングと正規化を行う

水平パーティショニング

行を分割しますね。シャーディングですね。MySQLなら partition by hash ( year (date)) すればいいですね。 PostgreSQLならなんやかんやがんばりましょう。

垂直パーティショニング

列を分割しますね。BLOB列など大きなデータが入っていて select * がクソ重くなるみたいなときは、別テーブルに切り出しましょう。

従属テーブルの導入

Multicolumn Attributes の解決策と同じですね。そうですね。ですねですね。

まとめ

個々をみると、ですよね〜ってかんじですよね。全体としてみると、メタデータ(属性)とデータ(値)の区別を見誤らず、相互に混入しないように注意することかな、と思いました。論理設計というところでいうと、対象のデータをリレーショナルモデルでどう表現するのか、というのがおもしろいところですね。でもどちらかというと逃げ恥のほうがおもしろいです。ガッキー。

本でもこのブログでも最後まで読んでくれた方は「う~ん、それっていわゆる、"EAV"だよね(^-^;)?」とかいってドヤ顔してどんどん嫌われていきましょう。

あと、Ⅱ部、Ⅲ部、Ⅳ部があるので、がんばって読んでなにか書きます。たぶん。