ポケモンを題材に「SQLアンチパターン」を実践してみる

@tkanayama_です。「SQL アンチパターン *1」という本を読みました。「ポケモンを題材に因果推論を実践してみる」のように、仮想的なストーリ上で実際に使ってみた感を出すことにより、自分の記憶に定着させることを狙います。

前提として、何をアンチパターンとするかは状況（ベンダーフリーである必要があるかどうか、どの程度の頻度で更新されるか・・・など）によって大きく異なるので、下記で紹介するアンチパターンは実は状況によっては問題にならないケースもあるかと思います。この投稿はあくまで「SQL アンチパターン」に忠実に従うことが目的です。

www.oreilly.co.jp

追記
登場人物
ストーリー
最後に
謝辞

追記

このブログを公開後、「外部キー制約はレコードロック周りのトラブルが起きやすい」との知見共有を有識者からいただきました。実務で使う場合にはご注意ください。

登場人物

オーキド博士：ポケモンを研究している博士。
サトシくん：かつてはポケモントレーナーを目指していたが、紆余曲折あって今はオーキド博士のもとでデータベースエンジニアをしている。

ストーリー

オーキド博士は、研究のために「どのポケモントレーナーがいつどんなポケモンを捕まえたか」というデータをデータベースを使って管理しており、サトシくんが運用しています。以下、各テーブルのスキーマ・データ例を示します。（SERIALなど、一部MySQL特有の文法が入っているので適宜読み替えてください。）

CREATE TABLE PokemonZukan (
    zukan_id      SERIAL PRIMARY KEY,
    name          VARCHAR(50),
    pokemon_type  VARCHAR(50),
    height        DECIMAL(5, 2),
    weight        DECIMAL(5, 2)
);

zukan_id	name	pokemon_type	height	weight
1	フシギダネ	くさ	0.7	6.9
2	フシギソウ	くさ	1.0	13.0

CREATE TABLE Pokemons (
    pokemon_id    SERIAL PRIMARY KEY,
    zukan_id      BIGINT UNSIGNED NOT NULL,
    pokemon_level TINYINT UNSIGNED NOT NULL,
    personality   VARCHAR(50),
    FOREIGN KEY (zukan_id) REFERENCES PokemonZukan(zukan_id)
);

pokemon_id	zukan_id	pokemon_level	personality
1	19	2	ひかえめ
2	16	8	おっとり
3	16	2	ゆうかん

CREATE TABLE Trainers  (
    trainer_id    SERIAL PRIMARY KEY,
    name          VARCHAR(50),
    hometown      VARCHAR(50)
);

trainer_id	name	hometown
1	レッド	マサラタウン
2	シゲル	マサラタウン

CREATE TABLE Captures  (
    id            SERIAL PRIMARY KEY,
    pokemon_id    BIGINT UNSIGNED NOT NULL,
    trainer_id    BIGINT UNSIGNED NOT NULL,
    created_at    TIMESTAMP NOT NULL,
    ball_type     VARCHAR(50),
    success       BOOLEAN,
    FOREIGN KEY (pokemon_id) REFERENCES Pokemons(pokemon_id)
);

id	pokemon_id	trainer_id	created_at	ball_type	success
1	10	100	2020-07-25 12:34:56	スーパーボール	False
2	10	100	2020-07-25T12:35:10	スーパーボール	True
3	11	55	2020-07-25T12:36:18	モンスターボール	True

PokemonZukanテーブルはポケモンに関する既知の情報（名前やタイプ・体長・体重など）を格納するためのテーブルです。ポケモントレーナーになるためのオンライン申請がされると、Trainersテーブルに情報（名前や出身地など）が格納されます。また、各ポケモントレーナーがポケモンを捕獲しようとすると、捕獲対象のポケモンに関する情報（ポケモンのレベル・性格など）が Pokemonsテーブルに格納され、捕獲の仕方に関する情報（使ったボール・捕獲に成功したかどうかなど）が Capturesテーブルに格納されるようになっています *2 。

フシギダネへの対応

長らく、各ポケモンはタイプを1種類しか持たないと思われていましたが、フシギダネというポケモンは「くさ・どく」の2タイプ持つことが明らかになりました。さらに、リザードンは「ほのお・ひこう」、バタフリーは「むし・ひこう」など、1つのポケモンが複数のタイプ持つ事例が多数確認されました。この報告を受けたオーキド博士は、 PokemonZukan テーブルを複数タイプ対応にするようにサトシくんに依頼しました。

そこでサトシくんは、 PokemonZukan テーブルの pokemon_typeカラムに、下記のような変更を加えました。

zukan_id	name	pokemon_type	height	weight
1	フシギダネ	くさ,どく	0.7	6.9
2	フシギソウ	くさ,どく	1.0	13.0

このように、複数の値を持つ属性をカンマ区切りなどで無理やり文字列として格納するのは、「信号無視（SQL アンチパターンの第1章）」と呼ばれるアンチパターンです。例えばどくタイプのポケモンだけ抽出したい場合、下記のようなクエリを書くことになります。

SELECT * 
FROM PokemonZukan
WHERE pokemon_type LIKE '%どく%';

しかし、この方法には以下のような懸念点があります。

今後「どく」タイプと「もうどく」タイプのように、名前に重なりがあるタイプが定義された時に対応できない。
タイプを3つ4つ持つポケモンが発見された場合、最初に定義したVARCHAR(50)に収まりきらなくなり、スキーマを更新する必要が出てくる可能性がある。
「全ポケモンが持つ平均タイプ数を知りたい」など複雑な集計処理を行う場合、pokemon_typeに含まれるカンマの数を数えるなどのトリッキーなクエリを書く必要が出てくる。

このアンチパターンを避ける方法の一つは、PokemonZukanテーブルのpokemon_typeカラムを使う代わりに、新たに下記のような交差テーブルを作成することです。

CREATE TABLE PokemonType  (
    zukan_id      BIGINT UNSIGNED NOT NULL,
    pokemon_type  VARCHAR(50),
    PRIMARY KEY (zukan_id, pokemon_type),
    FOREIGN KEY (zukan_id) REFERENCES PokemonZukan(zukan_id)
);

zukan_id	pokemon_type
1	くさ
1	どく
2	くさ
2	どく

これにより、さきほど例として挙げた「どくタイプのポケモンだけ抽出する」場合のクエリは下記のように書くことができ、文字列操作を回避できます。

SELECT z.*
FROM PokemonZukan AS z
INNER JOIN PokemonType AS t
ON z.zukan_id = t.zukan_id
WHERE t.pokemon_type = 'どく';

また、PokemonTypeにidの列が存在しないことにも注目です。全てのtableに無闇にid列を設けてしまうのは「とりあえずID（SQL アンチパターン第3章）」というアンチパターンです。今回の場合は zukan_idとpokemon_typeの組み合わせが一意になるはずなので、これに加えてさらにid列を定義すると冗長になってしまいます。そこでこの例では、id列を設けるのではなく複合キー PRIMARY KEY (zukan_id, pokemon_type) を宣言することで回避しています。

ヤミカラスへの対応

サトシくんは、前章で作成した PokemonTypeテーブルを下記のように修正し、pokemon_typeに無効なタイプが入らないように工夫しました（MySQL以外の場合は、Check制約として読み替えをしてください）。

CREATE TABLE PokemonType  (
    zukan_id      BIGINT UNSIGNED NOT NULL,
    pokemon_type  ENUM('ノーマル', 'ほのお', 'みず', 'でんき', 'くさ', 'こおり', 'かくとう', 'どく', 'じめん', 'ひこう', 'エスパー', 'むし', 'いわ', 'ゴースト', 'ドラゴン'),
    PRIMARY KEY (zukan_id, pokemon_type),
    FOREIGN KEY (zukan_id) REFERENCES PokemonZukan(zukan_id)
);

しかし残念なことに、ジョウト地方にてヤミカラスやハガネールというポケモンが新たに発見され、新たなタイプとして「あく」「はがね」の2つのタイプが追加されることになりました。これらのタイプを追加できるようにデータベースを変更するよう指示されたサトシくんは、下記のようなクエリを発行して対応しました。

ALTER TABLE PokemonType MODIFY COLUMN pokemon_type  ENUM('ノーマル', 'ほのお', 'みず', 'でんき', 'くさ', 'こおり', 'かくとう', 'どく', 'じめん', 'ひこう', 'エスパー', 'むし', 'いわ', 'ゴースト', 'ドラゴン', 'あく', 'はがね');

しかし、このようにスキーマ定義に今後更新されうる制約を直書きし、ALTER TABLEで変更を加えるのは「サーティーワンフレーバー（SQL アンチパターン第10章）」と呼ばれるアンチパターンです。理由としては、

更新の際に、今までの制約を知らなければならない。
例えば今後「くさ」タイプが廃止された場合など、制約の要素を1つ削除する場合の手順が複雑である。
列定義を後から変更できないデータベースもある。

などが挙げられます。このアンチパターンを回避するために、下記のような参照テーブルを作成して外部キー制約を宣言するという方法があります。

CREATE TABLE Types (
    pokemon_type  VARCHAR(50) PRIMARY KEY
);

INSERT INTO Types (pokemon_type) VALUES ('ノーマル'), ('ほのお'), ('みず'), ('でんき'), ('くさ'), ('こおり'), ('かくとう'), ('どく'), ('じめん'), ('ひこう'), ('エスパー'), ('むし'), ('いわ'), ('ゴースト'), ('ドラゴン');

CREATE TABLE PokemonType  (
    zukan_id      BIGINT UNSIGNED NOT NULL,
    pokemon_type  VARCHAR(50),
    PRIMARY KEY (zukan_id, pokemon_type),
    FOREIGN KEY (zukan_id) REFERENCES PokemonZukan(zukan_id),
    FOREIGN KEY (pokemon_type) REFERENCES Types(pokemon_type) ON UPDATE CASCADE
);

これにより、PokemonZukanのpokemon_typeを、Typesテーブルのpokemon_typeに格納されている値のみに制約することができます。また、新たにタイプを追加する際は

INSERT INTO Types (pokemon_type) VALUES ('あく'), ('はがね');

のように、スキーマ変更ではなくデータの追加という形で実現できます。さらに、タイプを削除する際にも、Typesテーブルからタイプを削除すれば整合性を損なうことなく削除することができます。これで、近い将来サトシくんの身に起こるであろう「フェアリータイプも追加してほしい」という要望にも迅速に対応できます。

ディグダへの対応

時代は少し遡り、まだカントー地方の図鑑を整備中だった頃、ディグダの体長をどう定義するかが激しく議論されていました。地面から上の高さを体長と定義する派閥と、地面を掘り起こして地面に埋まっている部分も体長と見なす派閥に分かれ、結論を出すのは半年後に開かれる学会まで待つこととなりました。

そこでサトシくんは、定義が明確になるまでPokemonZukanテーブルのディグダの体長・体重を下記のように入力しました。

zukan_id	name	pokemon_type	height	weight
49	・・・	・・・	・・・	・・・
50	ディグダ	じめん	-1	-1
51	・・・	・・・	・・・	・・・

体長や体重は負の数になることはありえないので、これを見た他の分析者が勘違いしてしまうことはないだろうとサトシくんは考えました。

しかし、これは「恐怖のunknown（SQL アンチパターン第13章）」と呼ばれるアンチパターンです。なぜなら、「全ポケモンの平均体長を知りたい」と思った他の分析者が誤って

SELECT AVG(height)
FROM PokemonZukan

のようなクエリを書いてしまった場合、誤った値を返してしまう（かつ、誤りに気づきにくい）というデメリットがあるためです。このアンチパターンは、不明な値を-1ではなくNULLで表現することで回避できます。

誤登録でポケモントレーナーになってしまったユーザーの削除

サトシくんは、ユーザーから下記のような連絡を受けました。

間違ってポケモントレーナーとして登録してしまった。個人情報なども入力してしまったので、データベースから完全に削除してほしい。

そこでサトシくんは、該当ユーザーの trainer_idを調査した上で、Trainersテーブルから該当ユーザーを削除するSQL文を発行することにしました。

この作業自体は難なく終わりましたが、後日「Trainers テーブルに存在しない trainer_idが Captures テーブルに格納されている」という問題が起きてしまいました。実は、誤登録した件のユーザーは、誤登録ついでに一匹ポケモンを捕まえてしまっていたようです。

このような事態になってしまったのは、「外部キー嫌い（SQL アンチパターン第4章）」というアンチパターンを踏んでしまったためです。Capturesテーブルの trainer_idは、外部キーとして Trainersの trainer_idが宣言されていませんでした。もし外部キーが宣言されていれば、「レコード削除の時点でエラーになる」 or 「自動でCapturesテーブルの該当行も削除される」という挙動になったはずです。

せめて、下記のようなクエリ発行を行ってCapturesテーブルに該当ユーザーが含まれないことを確認してからdeleteすべきでしたが、クエリ発行から削除までの間にCapturesが更新されてしまう可能性0ではないので、やはり外部キー宣言をしておくのが最善だったと考えられます。

SELECT * FROM Captures WHERE trainer_id = xxx

また、外部キー宣言をして無事削除ができた後にも、アンチパターンが潜んでいます。削除後に欠番になった trainer_idを無理やり埋めようとするのは、「擬似キー潔癖症（SQL アンチパターン第21章）」というアンチパターンです。欠番はそのままにしておきましょう。

最後に

今回は、SQL アンチパターンの内容をポケモンにこじつけつつ紹介しました。もちろん、用途によってはアンチパターンが実際は有用である場合もある（これは、書籍中では「アンチパターンを用いても良い場合」として説明されています）ので一概には言えませんが、今後DBの設計に携わる機会があればアンチパターンに気をつけながら設計していきたいと思いました。

謝辞

「SQL アンチパターン」は、「目的→アンチパターン→アンチパターンの見つけ方→アンチパターンを用いても良い場合→解決策」の流れが徹底されており、大変読みやすかったです。日本語訳も特に違和感がなく、淀みなく読むことができました。

*1:SQL アンチパターン -- Bill Karwin (著), 和田卓人 (監修), 和田省二 (監修), 児島修 (翻訳)

*2:体長・体重が PokemonではなくPokemonZukanテーブルに格納されていることに違和感を持った方もいるかもしれません。たしかに、ポケモンを生き物と考えるならば個体によって大きさは様々なはずです。実際、Pokemon GOでは個体ごとに体長・体重が定義されています。しかし、少なくとも初期のポケモンでは体長・体重を固定値として扱っていること、及び説明の簡潔さのために、体長・体重を各ポケモンに固有の値として扱っています。

kanayamaのブログ

twitter: @tkanayama_