データベース設計(変更履歴について)

Question

現在、社内で利用しているシステムがあります。
そのシステムのデータベースは、MySQL 5.6系を利用しています。

マスターデータを、更新される度に履歴を残していきたいと考えているのですが、
更新頻度が非常に多く、更新履歴をMongoDBへ格納しようと検討しております。

更新履歴として保存するデータは、更新前のデータ保存していく予定で考えております。
更新前データをMongoDBへ格納し、更新後のデータは、MySQLで管理し、
差分を調べたいときは、プログラム上で、判定を行いたいと考えております。

MongoDBの選定理由については、Json形式でデータを保持し、操作も容易にできそうなため選定しました。
MySQLでも、JSONの格納はできることは理解していますが、今回、NoSQLを試したいと思い試作として導入してみようと考えております。

ご教示頂きたいこととしまして、変更履歴について、皆様はどのようにデータを持たれているのか、
教えて頂きたく質問させて頂きました。

要件により履歴の内容も違うと思いますが、「どの箇所が変更されたかをあとから明確にすること」を
目的として、履歴を残していきたいと思っています。

NoSQLに、変更前、変更後を保持するのか、変更前を保持するのか、
色々な意見を頂けると幸いです。

ざっくりとした質問で申し訳ございませんが、よろしくお願いします。

Answer 1

私の経験では、マスタに「有効期間」(FROM,TO)列を設け、履歴レコードを持つ方法と、更新前データをトリガーで履歴テーブルに書き込んでいく方法の2通りの履歴管理をしていくシステムを扱いました。履歴を持つDBを分けるのであれば、後者をお勧めします。「更新日時」列を持てば、「バージョン番号」の代わりにもなりますし、１週間前の何時の時点ではどんなデータだったかなどはプログラムで検索できます。「更新者」列を持てば誰が更新したかも記録できます。もちろん、ユーザーが一人ひとり区別できるようにする必要がありますが。

Answer 2

皆様、ご回答ありがとうございます。

最初は、NoSQLの利用を想定しておりましたが、最終的には、nikoken様よりアドバイス頂きました、トリガーを利用する方法で実施したいと考えております。

色々とご教示ありがとうございました。

Answer 3

・重要なシステムである
・容量を食ってでも保存したい
・複数のカラムが同時に変更されることがある
・変更対象のカラムが多い
・更新日時カラムが既存テーブルにある
などを条件として

・既存のテーブルにバージョン番号カラムを持たせておく
・履歴テーブルを既存テーブルと同じカラム構成で作成する
・履歴テーブルの主キーは、既存テーブルの主キーにバージョン番号を加える
・既存テーブルでは更新のたびにバージョン番号を1増やす(初期は0か1で開始する)
・既存テーブルに追加・更新の際に、同じ内容で履歴テーブルに追加する
という対応をしています。

毎回全量の情報を保存しておきます。どこが変わったかは、前回分のレコードと機械的に比較すればいいだけの話です。やや大きめのシステムなので、1つの履歴テーブルだけで毎日1000万程度のレコードが増えます。当然大きな容量が必要になります。

更新頻度が高くかつ十分なストレージを用意できない場合には、監視対象のカラムを減らしたり、変更のあったカラムだけを保存するなどした方がいいと思います。

Answer 4

証跡ログとして取得する場合には、
対象データの更新に対する記録が保証されている必要性があると思います。

その場合には、トランザクション内でコミット/ロールバックを行うことが必要となると思うのですが、
同じMySQL同士でトランザクションを実行したほうが確実性は高まると思います。

証跡ログを残すにあたり必要な情報は、
・タイムスタンプ
・操作ユーザー
・対象データベース
・対象テーブル
・対象カラム
・更新後のデータ
(更新前のデータは、直近のレコードより判断->これにより更新前のデータを取得するコストが削減できます)
としてよく設計をしておりました。

更新処理が2つのテーブルにまたがることがパフォーマンスに大きな影響を与えるような頻度でない場合には
お勧めの方式です。

対象ユーザーを特定する必要がない場合には、
クエリーログを活用するのもいいかもしれません。

Comments

[退会済みユーザー]

ご回答ありがとうございます。

更新後のデータを書き込みをするということは、初回データ登録時からの運用を想定しているということでしょうか？

[KazumasaTanaka]

サービスイン時のデータの投入を、マスターデータ更新用の機能を経由して行う方法を選択するのであれば、アプリケーションの機能だけで対処できると思います。

一方、システム移行などで既存データがある場合には、
新規のシステム構成に合わせて、必要なコンバート処理や補正などが発生することもあるかと思いますので
その一環で、初期データとなるマスターデータを更新データ側のテーブルに入れるように対処するといいと思います。

Answer 5

Oracleでの開発でしたが、テーブルの変更履歴用のテーブルで、連番、変更日、変更担当、変更内容(フリーフォーマット) を使っていたお客様での作業がいちばん便利でした。
ディクショナリ・ビューと合わせて調べると、きのう変更したhoge テーブルの hoge 列だけど、同じ列名のテーブルがこっちにもあって、桁数やデータ型が違っているけど、このままだとやばくない？ みたいなことも簡単にできました。

Answer 6

NoSQLではありませんが、今業務で携わっているシステムでは、変更履歴には更新後のデータを格納しています。さらに変更履歴にはマスターデータの各更新可能カラムの「更新対象フラグ」を付ける設計になっています。

例えば

--マスターデータ格納テーブル
CREATE TABLE MASTER_TABLE (
    id int,
    name varchar(64),
    item_type varchar(2)
)

というテーブルがあったとしたら、

--変更ログテーブル
CREATE TABLE MASTER_TABLE (
    id int,
    renew_date datetime,
    name varchar(64),
    name_update_flag varchar(1) --nameが更新された場合1,されてなければ0
    item_type varchar(2),
    item_type_update_flag varchar(1) --item_typeが更新された場合1,されてなければ0
)

みたいな感じです。これがベストな設計かと聞かれたら自身を持って答えられませんが、「どの箇所が変更されたかをあとから明確にすること」 という事に関しては良い方法だと思っています。

Comments

[退会済みユーザー]

カラムの数に応じて、更新フラグをもうけて管理するという方法ですね。

変更ログテーブルについてですが、マスターデータを全て記録し、その中で変更されたもののみにフラグを立てる感じでしょうか？

[hitsujimeeee]

そうです。