ある年度の引越しの数のデータをまとめるテーブルを考えてみましょう。
このようなアンチパターンの設計のテーブルを作ったとします。
2019_move_count
| prefecture | move_count | created_at |
|---|---|---|
| 東京都 | 100 | 2019-01-01 00:00:00 |
| 大阪府 | 80 | 2019-01-01 00:00:00 |
| 愛知県 | 50 | 2019-01-01 00:00:00 |
デメリット
データ制約
このテーブル設計では、2020年になったら新たにテーブルを追加することになります。
そのテーブルには201９年以前のデータが入らないようにするために
created_atにはデータのCHECK制約を設ける必要があります。
間違ったデータの処理の手間
2020年のテーブルに入っているべきデータが
2019年のテーブルに入っていた場合、
2019年のテーブルから該当業を削除し、
2020年のテーブルに新たに挿入するという操作をしなければなりません。
一意性
2020年のテーブルにあるデータと、2019年のテーブルにあるデータで、（2018,2017...と全てのテーブルに対しても）ある行のデータの一意性が保たれていることを保証するためには
別テーブル(MoveIds)を用意し、各年毎のテーブルの行のidを利用して一意であることを保っておく必要があります。
テーブル結合
全ての年度のデータをまとめるとなった際には、
年毎にテーブル結合のクエリが更新されていく必要があります。
新たな列の追加
新しい列を追加するとなった際には、全てのテーブルに対して
列の追加操作をする必要があります。
外部キーによる参照整合性がない
テーブルは複数に分かれているので
外部キーを元に引越し回数を取得するといったことは
基底テーブルや交差テーブルを作成する必要があります。
解決策
パーティショニング
水平パーティショニング
MySQLには PARTITION BY HASH を使って
テーブル内を分けることができます。
垂直パーティショニング
こちらは列を元にテーブルを分割する方法です。
BLOB型、TEXT型といったデータを扱う列がある場合は
それらを別のテーブルとして扱ってデータを保存します。
多くのDBソフトがこれを自動で行なっているらしいです。