mike-neckのブログ

Java or Groovy or Swift or Golang

SQLのUPDATE文と履歴系テーブル

大したことは書いてありません。

気付いたことのメモ程度です。

『理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL (WEB+DB PRESS plus) 』を少し本屋で立ち読みしました。(ノットワーキングプアにあえいでいるので立ち読みしかできない(´・ω・｀))

この本の中で履歴系テーブルの扱い方というのに丸々一章が割り当てられていました。

だいたいこんなことが書かれてたと思う。

たしかに、RDBMSでは履歴データというのは相性が悪い。

僕は陸上競技を見るのが好きなので、これのデータモデルをたまに考えたりしています。で、選手の氏名が変わることあるよなーと考えると、これを取得するときのSQLは…SQLで一発で取れないじゃんと悩んだりします。

というわけで、悩んでいる例をそのまま書いてみたいと思う。

例えば選手というテーブルを作るとしたら、次のようなテーブルになると思われる。

選手

カラム 型
選手番号 数値
選手姓名 文字列
選手名前 文字列

だが、実際は、選手の姓名は変わることがある。

例を挙げればキリがないのですが、次のような選手を挙げられます。

日本で名前の変更は裁判を行ってはじめて可能になるらしいので、こちらはかなりのレアケースなので、ほぼ無視して構わないとします。

井村さん、福本さんも二瓶さんも今は競技会に出場しないので、今現役なのが千葉選手ですが、400mの日本記録を見ると次のように書かれている。

種目 記録 氏名 所属 記録年月日
400m 51.75 丹野麻美 ナチュリル 2008/05/03

出展:日本陸上競技連盟

一方、現在は千葉選手は千葉麻美で大会に出場している。

名前 都道府県 所属 資格記録 自己ベスト
千葉麻美 福島 東邦銀行 53.76 51.75

出展:第98回日本陸上競技選手権大会

つまり「姓名」に関しては日付の情報から表記が変わることがわかる。したがって、この「選手」というテーブルは「姓名」カラムを別のテーブルに分離して管理するべき履歴系テーブルである。

したがって、上記の「選手」テーブルは次のように改めなければならない。

選手

カラム 型
選手番号 数値
選手名前 文字列

選手姓名

カラム 型
選手姓名番号 数値
選手番号 数値
選手姓名 文字列
適用年月日 日付

なお、僕は主キーを(選手番号, 選手姓名)にするのが気持ち悪いと考えるので、サロゲートキーを設けていますが、まあ、そのへんは読者の好きにやってください

では、ある時点での選手の氏名を取得するSQLを書きましょう。

SELECT A.選手番号, B.選手姓名, A.選手名前
FROM 選手 A JOIN 選手姓名 B ON A.選手番号 = B.選手番号
WHERE B.選手姓名 = 'hoge'
  AND A.選手名前 = 'foo'
  AND B.適用年月日 <= 'date'

さて、これがdateが今日の日付だったら、千葉麻美選手のレコードは二件になってしまいますね。

一発で一件取りたいのに、それが取れないのが履歴系テーブル問題ですね。まあ、みなさん経験済みですね。

じゃあといって、SQLで頑張ってみます

SELECT A.選手番号, B.選手姓名, A.選手名前
FROM 選手 A JOIN 選手姓名 B ON A.選手番号 = B.選手番号
WHERE B.選手姓名 = 'hoge'
  AND A.選手名前 = 'foo'
  AND B.適用年月日 = (SELECT MAX(適用年月日)
      FROM 選手姓名 C JOIN 選手 D ON C.選手番号 = D.選手番号
      WHERE C.選手姓名 = 'hoge'
        AND D.選手名前 = 'foo'
      GROUP BY C.選手番号)

ここ最近SQL書いていないので、間違っているかもしれません。

ただ、まあ、気持ち悪いSQLだということはわかるかと思います。

いやいや、その前に、僕のテーブルの設計がアレだろというのはありそうです。

それは「選手姓名」テーブルに終了日を設けて、比較演算子で抽出できるだろという話です。

選手姓名

カラム 型
選手姓名番号 数値
選手番号 数値
選手姓名 文字列
適用年月日 日付
終了年月日 日付

これは、これでSQL一発でできそうですけど、以下の二点が気に食わないので、僕は絶対にこのような設計にしません。

  1. 「適用年月日」と「終了年月日」が異なるレコード間で依存関係が発生してしまう
  2. 終了していない場合「終了年月日」がNULLになる

まあ、両者の問題を解決する画期的な方法として日付に9999/12/31を入れるというのがあるだろうし、やっているところはやっていそうです。でも、これは単なるNULLと変わらないし、新しいレコードを追加するだけなのに、INSERTだけでなく、UPDATEも実行しないといけない…

上記の問題を解決する画期的な方法として、「終了年月日」をまた別のテーブルに分離するという技があります。

選手姓名

カラム 型
選手姓名番号 数値
選手番号 数値
選手姓名 文字列
適用年月日 日付

選手姓名終了

カラム 型
選手姓名番号 数値
終了年月日 日付

では、これで最新の日付を取ろうか…はい、副問い合わせがまた発生しますね。集計関数を使わない分まだましですが、それでもnot inを使いますね…

このような履歴系テーブル問題への対処案を上記の書籍では提示しています(僕のようにとろい頭の持ち主は一読しただけでは理解できませんでした(´・ω・｀))。

さて、こういった問題が発生した時のデキルSE=ｻﾝの対応を見てみましょう。

調整法1

  • SE=ｻﾝ
    • 実装が大変なので、履歴を持たない方向で調整させてもらえませんか

つまり、これまで僕が考えてた「選手姓名」とか「選手姓名終了」とかをなしにして、「選手」に対して姓名が変わったらUPDATEをかけてしまいましょうということです。

一時的にはいいのかもしれませんが、本当に履歴が必要な業務だった場合、このSE=ｻﾝどうするんでしょうね。

調整法2

  • SE=ｻﾝ
    • PG=ｻﾝ、姓名の部分はテキスト型にして、JSONで持つようにしてください。

これはせっかく正規化したテーブルを0次正規化するということですね。こんなこと考えるSE=ｻﾝは人生をやり直すかイット業界から足を洗ってください(正規化原理主義者による声明(ちなみに僕は第三正規化くらいまでしかわかりませんが…))。

また、上記の調整をした場合、新しい姓名が追加された場合は、INSERTではなく、UPDATEを実行することになります。

以上、僕の足りない頭で考えた履歴系テーブルの問題点と問題だらけの対応策です。

履歴系テーブルがすっごい厄介で、かつ臭いものだということがわかっていただけたと思います。

で、先の書籍を読んでいて、データベースにおけるUPDATE文というのは、くっさい履歴データに対して、臭いものには蓋的な命令なのではないだろうかと思いました。

結論

いや、中途半端なところで終わりにするんですけど、履歴系テーブル問題は難しいですね…

あと、中途半端かつ理論武装なしでこんなエントリーを書いて、世の中の怖い人から強力なマサカリを投げつけられて「選手姓名終了」もとい「選手生命終了」しそうなので、これで終わりにします。