SQLアンチパターン-8章 メタデータトリブル-まとめ

ある年度の引越しの数のデータをまとめるテーブルを考えてみましょう。

このようなアンチパターンの設計のテーブルを作ったとします。

2019_move_count

デメリット

データ制約

このテーブル設計では、2020年になったら新たにテーブルを追加することになります。
そのテーブルには201９年以前のデータが入らないようにするために
created_atにはデータのCHECK制約を設ける必要があります。

間違ったデータの処理の手間

2020年のテーブルに入っているべきデータが
2019年のテーブルに入っていた場合、
2019年のテーブルから該当業を削除し、
2020年のテーブルに新たに挿入するという操作をしなければなりません。

一意性

2020年のテーブルにあるデータと、2019年のテーブルにあるデータで、（2018,2017...と全てのテーブルに対しても）ある行のデータの一意性が保たれていることを保証するためには
別テーブル(MoveIds)を用意し、各年毎のテーブルの行のidを利用して一意であることを保っておく必要があります。

テーブル結合

全ての年度のデータをまとめるとなった際には、
年毎にテーブル結合のクエリが更新されていく必要があります。

新たな列の追加

新しい列を追加するとなった際には、全てのテーブルに対して
列の追加操作をする必要があります。

外部キーによる参照整合性がない

テーブルは複数に分かれているので
外部キーを元に引越し回数を取得するといったことは
基底テーブルや交差テーブルを作成する必要があります。

解決策

パーティショニング

水平パーティショニング

MySQLには PARTITION BY HASH を使って
テーブル内を分けることができます。

垂直パーティショニング

こちらは列を元にテーブルを分割する方法です。
BLOB型、TEXT型といったデータを扱う列がある場合は
それらを別のテーブルとして扱ってデータを保存します。
多くのDBソフトがこれを自動で行なっているらしいです。