DBの「登録日」「更新日」の指定方法に関するメモ

業務システムを作っていると、結構データベースの各レコードに「登録日」「更新日」などを設定するようになっている設計を見かけます。ここのデータを誰がいつ書き込んだのかを記録することで、後々いろんな調査などに使うというものですね。

これ、過去に見たやり方がいくつかあるので、自分用の整理もかねて列挙しておきます（いや、ちょっと仕事で同じような話が出て自分でしゃべった内容を忘れないように書いておくだけです）。

44のアンチパターンに学ぶDBシステム (DB Magazine SELECTION)

SQLの中でその時点の日時を入れる

InsertやUpdate文の中で、データベースサーバーの日時を取得して登録/更新日に設定するというものです。一番一般的じゃないでしょうか。たとえばSQL Serverの場合「GetDate()」という関数を呼べばその時点の日時を得られるので、それを使って日時を記録します。
この方法だと、たとえば100件のデータを登録した際に、1件目と100件目では日時の「秒」あたりが変わる可能性があります。日時の取得は1行ごとに実施されるので、刻一刻と進んでいるサーバー時刻もずれていくためです。1回のデータ登録で日時がずれるというのは気持ち悪いと思う人もいるかもしれませんが、過去に1回だけこれが有効に働いたことがありました。
特に処理速度測定のためのログなどが織り込まれていないシステムで、データ登録や書き換えのパフォーマンスが問題になった際にこの登録日、更新日がデータベース処理のパフォーマンスを示す貴重なログとなったのです。というわけで、業務上「本日付でないといけない」といった要件がないのであればデータベースサーバーの日時を都度都度入れるのが案外最良の選択だったりします。

グラス片手にデータベース設計（生産管理システム編） （DB　magazine　selection） [ 梅田弘之 ]

SQL発行の前にいったんサーバー日時を取る

InsertやUpdateを行う前に、一度「日時を取るSQL」を実行してその時点のサーバー日時を取得。その情報を以後のSQLに埋め込んで登録日や更新日を設定するというもの。メリットとしては1回のデータ処理に関する日時がそろうことで、綺麗なデータができる、という点。
あとあと登録日をピンポイントで検索したいといった場合、これにしておけば「12月1日の13:10:23に登録した全データを取得」なんてこともできます。ただ、このメリットを享受したいのならば、個人的にはこういう「データ登録処理のログとしての登録日、更新日」と、「業務上の検索処理のためのデータ登録日時」は別の列として用意した方がよいように思います。
データにしても機能にしても、本来想定していた用途以外に使い始めると意外にいろんな問題が出てくるものです。たとえば、会社のPC起動や終了を出退勤の管理に使う、といった場合、「単にPCを再起動しただけで退社扱いになる」といったケースを想定しないといけないなど、本来、それ専用の仕組みを用意すれば考える必要が無い手間が出てきたりします。
やはり、データベースのデータ1つにしても「何のためにその列を作ったか」をきちんと考えて設計し、その考えを反映した作りを心がけるべきだと思います。
話が飛びましたが、SQL発行前にサーバー日時を取るというのは個人的にはこの「登録日/更新日」に関してはあまりおすすめしないと考えます。

ITシステム開発徹底攻略【電子書籍】[ WEB+DB PRESS編集部 ]

トリガー機能を用いて登録する

過去に1回やったことがあるのがこれです。データベース側のトリガーという機能を用いて、「データ登録、更新時にこの処理を行う」という設定を行います。この「処理」の中で「対象となるレコードの登録日、更新日に年月日を登録する」という処理を入れておくことで、各業務処理のSQLには登録日、更新日を入れる必要がなくなるというものです。
大量のデータを扱う処理などの場合、使うか使わないか分からない日付情報取得のためのSQLを毎回削れるメリットがある上、トリガー処理内で「接続ユーザーや端末情報も取得して記録する」なんて処理をいれることで厳密なデータ更新ログをとれるというメリットもありました。
ただ、初期データの大量登録や、登録日、更新日まで指定した日付にしてテストデータを作りたいといった場合はトリガー機能のON/OFFを切り替えるなど、少々手間は出ますが、システム運用が始まってしまえば結構メリットがある方法です。

というわけで、何気なく追加しがちな「登録日」「更新日」もその実装方法はいろいろありますし、それぞれ意味やメリット、デメリットがあるので、適当に設計して適当に実装しないようにしたいものです。

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ