データベース論理設計のアンチパターン
おはこんばんちは。
がんばってブログを書きたいので、ちょうどいま読んでいるSQLアンチパターンという本を、噛み砕いて離乳食くらいの柔らかさにして晒してみます。すでに読んだ人はいますぐそっ閉じ、まだ読んでない人は、こちらも同様にそっ閉じしてお風呂に入ってすぐ寝ましょう。
- 作者: Bill Karwin,和田卓人,和田省二,児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (45件) を見る
かなり圧倒的に当たり前のことしか書いてないんですけど、そういう本こそが良書だっておじいちゃんの息子の息子が言ってた気がします。
アンチパターンが全部で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階層までしか取得できません。階層が増えると、SQLの join
がどんどん増えていきます。どんどんドーナツです。また、ノードの削除もしんどいです。あるノードが削除されると、その子たちも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_id
が Gakkies.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年のテーブルに混入しないように、など、データの整合性を管理する必要があります。
insert
:date
のカラムに対してcheck
制約をつけるupdate
:date
の年が間違っていた場合、delete
してinsert
する
「すべてのコメントを取得したい」という場合に、Comments_20XX たちを union
することになりますが、テーブルが増えるたびにそのSQLを修正する必要があります。
解決策
パーティショニングと正規化を行う
水平パーティショニング
行を分割しますね。シャーディングですね。MySQLなら partition by hash ( year (date))
すればいいですね。 PostgreSQLならなんやかんやがんばりましょう。
垂直パーティショニング
列を分割しますね。BLOB列など大きなデータが入っていて select *
がクソ重くなるみたいなときは、別テーブルに切り出しましょう。
従属テーブルの導入
Multicolumn Attributes の解決策と同じですね。そうですね。ですねですね。
まとめ
個々をみると、ですよね〜ってかんじですよね。全体としてみると、メタデータ(属性)とデータ(値)の区別を見誤らず、相互に混入しないように注意することかな、と思いました。論理設計というところでいうと、対象のデータをリレーショナルモデルでどう表現するのか、というのがおもしろいところですね。でもどちらかというと逃げ恥のほうがおもしろいです。ガッキー。
本でもこのブログでも最後まで読んでくれた方は「う~ん、それっていわゆる、"EAV"だよね(^-^;)?」とかいってドヤ顔してどんどん嫌われていきましょう。
あと、Ⅱ部、Ⅲ部、Ⅳ部があるので、がんばって読んでなにか書きます。たぶん。